Summing indirect based on column and row headers

**SKAh** · 03-22-2011, 01:36 PM

I have a spreadsheet that contains a tab for each of 10 projects (tab name = project name). Row 1 of each of those tabs contains dates, one column for each month (e.g. Dec-10, Jan-11, Feb-11, etc.). Rows 2 through n contain numbers.

The spreadsheet also contains a Summary sheet. Dates matching those found in Row 1 of the project sheets are listed in Column A (starting in Row 2). Row 1 (starting at column B) contains the project names, which also match the tabs in the spreadsheet.

What I'm trying to accomplish is to use INDIRECT based upon the project name in Row 1 and the date in Column 1 to sum the numbers found in the associated project tab in the associated column. I'm just not getting how to do it. Maybe indirect is not how it should be done.

I've attached a spreadsheet containing what I've accomplished so far. I sure could use some advice on this. I'm lost.

**NBVC** · 03-22-2011, 01:42 PM

Try something like:

=SUM(INDEX(INDIRECT("'"&B$1&"'!A:R"),0,MATCH($A2,INDIRECT("'"&B$1&"'!$A$1:$R$1"),0)))

where A:R covers all columns from each sheet that you need to look in... expand/contract as needed.

**Roger Govier** · 03-22-2011, 01:55 PM

Hi

One way
Group all your Data sheets, other than the summary Sheet. Do this by clicking on the first of the sheet tabs, then hold down the Shift key whilst you click on the last of the tabs to be included.

Insert a new row at the top of the first of the Grouped sheets, and in cell A1 enter the formula
=SUM(A3:A300)
Drag the formula across the sheet for as many columns as you are going to have data.
Now, click on your Summary sheet (which will un-group all of the others)

In cell B2 enter
=INDEX(INDIRECT(B$1&"!A1:Z300"),1,ROW(A1))
Copy across and down as far as required

**Roger Govier** · 03-22-2011, 02:08 PM

Hi

Won't that add the date into the OP's result?
The example sheet shows Serial 40603 formatted as mmm-yy as the column header on the data sheets

Perhaps a modification to
=SUM(INDEX(INDIRECT("'"&C$1&"'!A2:R300"),0,MATCH($A2,INDIRECT("'"&C$1&"'!$A$1:$R$1"),0)))

would help