Total amounts spent at each vendor..?

[FYRGUY47]

Hello all,

Using Excel 2002, I have a sheet that contains employee names on rows. Next to each employee's name is a column named "Draw#1", then sucessive draws to the right. In the columns for each employee there is a drop down menu with the vendor names. After inserting the vendor's name from the list, they will enter an amount $XX.XX.

I need to get the total amount spent across all employees for each vendor. I am putting this information on sheet#2 labeled "Vendor Totals"

I hope I have drawn a good picture of what I am trying to do. Thank you in advance for your help.

This is somewhat how it looks:

Employee vendor X vendor Y vendor Y
$00.00 $00.00 $00.00

Employee vendor Y vendor Y vendor X
$00.00 $00.00 $00.00

 

[SkipVought]

Hi,

I need to get the total amount spent across all employees for each vendor.

I'm afraid that I have some bad new for you. The way that you have organized your data, does not work at all, for reporting as you describe.

Your data need to be orgainzed in tabular format...
[tt]
Employee Name | VendorID | Amount
[/tt]
I would also add a column for a date, as I imagine that this data is accumulated periodically.

With data organized like this, a PivotTable report can easily be generated in a VERY short period of time; mere seconds, if you know what you're doing.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Hi Skip,

Thanks for your quick reply.

I actually do have the date in a cell just above the vendor cell.

I will look at redesigning the form.

Thanks anyway for your help and knowledge!!

Chris

 

[SkipVought]

I actually do have the date in a cell just above the vendor cell.

Actually the date must be on every row of data in order to actually be useful in reporting.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

It is on a row above every "vendor" cell. Another problem I may have is that for the employee's name, I merged some cells.

So actually, for each employee there are three rows. Just like my earilier sample. Employee's name A2,A3,A4(merged)B2 = date, B3 = vendor, B4 = amount. Columns B through M are the same as above for that employee's draws. I thought that layout would work well considering what I was doing.

(This is for- Each employee gets X-Amount of money for clothing allowence, they can take "draws" of money and it is to be used for only certian vendors - FYI )

 

[SkipVought]

Merging cells also may look nice, but it makes analyzing and summarizing your data extremely difficult.

Again, the proper structure for your source data is a table, not the structure you posted with merges cells and dates above anything.

Check out faq68-5184

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Thank you Skip!!