Indirect and lookup across several worksheet tabs

[flier109]

I've confused myself tonight, can someone help?

I have a spreadsheet with tabs for each month with data such as the following on each tab.

Name Rate 1 Rate 2 Rate 3 Rate 4
Adam 5 2 4 0
Jane 0 3 4 2
Bob 1 1 2 3

I want to be able to have a summary tab which calculates a total for each rate for each person for all months.
The summary tab would be in alphabetical order, as each tab may have different names in different orders.
How would I do this please?

[XOR LX]

Hi,

Very difficult to know for sure without seeing your actual workbook, though try to adapt this to suit your requirements:

First go to Name Manager (Formulas tab) and define:

Name: SheetNames
Refers to: ={"Jan";"Feb";"Mar"}

(Or whatever happen to be the sheet names in question.)

Assuming that:

1) You have e.g. "Adam" in cell A2 of your summary sheet, with headers in row 1 (Rate1 in B1, Rate2 in C1, etc.)
2) Each of the individual sheets has names in column A with Rate1 in column B, Rate2 in column C, etc.

then this formula in B2 of your summary sheet:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!A:A"),$A2,INDIRECT("'"&SheetNames&"'!"&CELL("address",B:B))))

Copy to the right to give equivalent sums for Rate2, Rate3, etc.

Also copy down to give equivalent sums for the entries in A3, A4, etc. (e.g. "Bob", "Jane", etc.).

Regards

[flier109]

Spot on, thanks so much for your assistance XOR LX

[XOR LX]

No worries.

Reply here if you come across any issues.

Cheers