help with problem....

[Jeff King]

Hi,
Please bear with me as this post may be long.
Any help with this problem is greatly appreciated. Using Excel97.

I have a workbook where each sheet is a monthly summary of $ values
for jobs done and who the job is for. (along with alot of other info).

What i am trying to do is:

1) Search through column E for each individual company name and
create a running total of billed amounts in another worksheet named
Summary.

2) Store the name of each company and the total of all money billed
to them in another worksheet named Summary.

3) Use the Summary sheet to keep track of company billing for month
after month.

format for Summary sheet is:
Company name Month 1 Month 2 Month 3 etc
Company 1 $xxxx $yyyy $zzzz
Company 2 $aaaa $bbbb $cccc


$xxxx - this is a total of all the amounts in October's sheet where
company 1 is found

etc.

Thanks for any help.

Jeff King

 

[Bill Kuunders]

{=SUM(IF(company_Jan=A2,jobs_Jan,0))}

As you have already setup your sheets per month
The above formula will give you a total for the company listed in A2 on your
summary sheet.
You will need to create names for the list of companies and the list of jobs
done in each sheet. I.e company_Jan is the name for the area with the list
of companies on your January sheet.
That way if you add to these areas you won't need to change the formula's in
the summary sheet. (that is as long as you add above the last row!!!)

Next to the above you would have
{=SUM(IF(company_Feb=A2,jobs_Feb,0))}
Enter each formula as an array formula without the {} these will appear when
you push <enter> while holding <cntrl><shift>

Enter the top row for each month and extend down for all your companies,
using the right hand bottom corner of the cell, the mouse pointer changes
into "+"
click and drag down.
You will have to spell the names exactly the same on the summary as well as
on your monthly lists.
It may pay to have dropdown lists on your monthly sheets referring to the
one list on your summary sheet. (data / validation /
allow.............list )

Good Luck
Bill K

 

[Paul Falla]

I personally would use pivot tables. This will eliminate
the need to create lookup tables etc.