Sum Crossreference
 

Let's see if I can set this up right...

I have a workbook with three sheets. Sheet2 column A has a list of
geographical abbreviations. Column B has a list of garage addresses. Columns D
through BY (that's 76 columns) has a list of sub-geographical areas in each row
that the garage in Column B is responsible for. Each garage is responsible for
unique sub-geographical areas and there are several garages in each parent
geographical area.
Sheet3 Column B has a list of those same sub-geographical areas. Column C
has the amount of work performed in each sub-geographical area and Column D has
the month in which that work was done.
On Sheet1 I would like to have that same list of garages that appears on
Sheet2 but in columns D through I, I would like to show the amount of work done
by each garage for each month represented on Sheet3. So, if the garage at 123
Main did 12 items in areas ABC, 10 in ABD, and 20 in ABE in the month of August,
I would like column D, which represents August's results, to show the sum of
ABC, ABD, and ABE for the month of August. Column E would contain the results
for September, etc.
I've considered trying to use a SUMIF and AND but can't think of anything
that would work. I've also considered a macro but can't think of anything there
either. Any suggests or help would be greatly appreciated. I'm really stuck.
Thanks!

Sum Crossreference
 

Hi Bill
you may consider using a pivot table on your data in sheet 3. You may
have to add the garage name for each sub geographic region.
Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
to get startet with them




--
Regards
Frank Kabel
Frankfurt, Germany

Bill Oertell wrote:
Let's see if I can set this up right...

I have a workbook with three sheets. Sheet2 column A has a list
of geographical abbreviations. Column B has a list of garage
addresses. Columns D through BY (that's 76 columns) has a list of
sub-geographical areas in each row that the garage in Column B is
responsible for. Each garage is responsible for unique
sub-geographical areas and there are several garages in each parent
geographical area. Sheet3 Column B has a list of those same
sub-geographical areas. Column C
has the amount of work performed in each sub-geographical area and
Column D has the month in which that work was done.
On Sheet1 I would like to have that same list of garages that
appears on Sheet2 but in columns D through I, I would like to show
the amount of work done by each garage for each month represented on
Sheet3. So, if the garage at 123 Main did 12 items in areas ABC, 10
in ABD, and 20 in ABE in the month of August, I would like column D,
which represents August's results, to show the sum of ABC, ABD, and
ABE for the month of August. Column E would contain the results for
September, etc. I've considered trying to use a SUMIF and AND but
can't think of anything
that would work. I've also considered a macro but can't think of
anything there either. Any suggests or help would be greatly
appreciated. I'm really stuck. Thanks!