Excel "Worksheet Name" Building Function for Summary Sheet

[stevefromnaki]

I am trying to create a summary sheet in excel.

My summary sheet has a table, with, for example, months down the left side,
and different products across the top.

I have a separate worksheet for each month, for each product. I only have 3
products, so for each year i would have 3 products times 12 months = 36
worksheets, plus a summary worksheet.

The names of the rows and columns in my summary table are made identical to
a part of the worksheets names. I.e. the summary table going down the left
might have 012007 for Jan 2007, 082007 for August 2007, and 112007 for
November 2007. Across the top of my summary table i have produuct X, Y, and Z.

Hence my worksheets will have the names of X012007 for product X for Jan
2007, or Y112007 for product Y for Nov 2007, etc etc - where each worksheet
name is the exact combination of the summary tables' row and column names.

I want to build a formula to get to the total dollar figure of sales for
each month from each worksheet and simply drag and drop this formula to fill
the summary table as the months go by. I.e. the total sales figure is in cell
G33 (same for all worksheets) and i want a formula similar to the following:
=sum(X012007!$G$33) but i want to build a function for the X012007 component
so this can refer to the column and row names of the summary table, and
change as the formula is dragged and filled.

I tried using concatenate but this resulted in a string (obviously!!) and
the sum function did not know to interpret this as a worksheet object.

I created a name called "Total" for the G33 cell in all the worksheets and
tried =SUM(Total) but this only returned the one figure, not separated by
month or by product. (note i will use this approach for a check at the end,
to make sure it all balances).

I could type in a formula for each product-month sales total but would find
a formula much easier .

Any help will be very much appreciated!

Kind Regards,
stevefromnaki

 

[Duke Carey]

You need to wrap the concatenated address in the INDIRECT() function. Thus,
your example of

=sum(X012007!$G$33)

would work with

=sum(INDIRECT("X012007!$G$33")

 

[Tom Hayakawa]

Hey Steve,

Try building your worksheet name as usual, then wrap it in an INDIRECT
formula. That should get the results you want. For example, if you have the
Product ID X in cell A2, the worksheet name 092007 in cell A3, and the cell
address you want on the 092007 worksheet is B24 you could do something like
this:

=INDIRECT(CONCATENATE("'",A2,A3,"'!B24") or
=INDIRECT("'"&A2&A3&"'!B24")

Good Luck!

Tom Hayakawa