Sumproduct Sheet Reference

[matthews311]

Hello-

I am having an issue with a sumproduct formula updating. The formula is: =SUMPRODUCT((('Summary_WE_08212010 (3)'!$A$18:$A$620=$A5)*('Summary_WE_08212010 (3)'!$C$18:$C$620=$D5)),'Summary_WE_08212010 (3)'!E$18:E$620). I don't want to use the sheet name, as the lookup. Instead I want to reference the sheet number (eg. Sheet2) because this sheet is going to be deleted, updated, and name changed. If you need any other info please let me know. Thanks

[Palmetto]

You should be able to use the INDIRECT function.

Here is a possible approach:

Use helper cells to build the string for the sheet name

Cell 1: sheet number (e.g 1, 2, etc.)
Cell 2: sheet name (i.e. Summary_WE_08212010[space]
Cell 3: sheet reference using above inputs joined together

Using cells A1:C1 as the helper cells per the above
A1: 1
B2: MySheet
C1: ="'"&B1&"("&A1&")'!"

Result: 'Mysheet(1)'!

formula: SUMPRODUCT(--((INDIRECT(C1&"$A$18:$A$620")=$A5)