Dynamic sheet name in formula

Question

sheet1 and sheet2 are structured the same way.

In sheet3 I'm using the sumproduct() this way:

=SOMMEPROD((**sheet1**!$C$2:$C$2000=$A3)*(**sheet1**!$B$2:$B$2000=D$2)*(**sheet1**!$D$2:$D$2000=$B3)*(**sheet1**!$E$2:$E$2000=$C3)*(**sheet1**!$I$2:$I$2000="Ongoing"))

Is there a way to replace the hardcoded sheet1 in my formula above by referring to a cell which correspond to the name of the sheet?

Answer 1

You can insert the cell values but will need to use =INDIRECT to make them behave the way you expect.

I built a simple example here.

=INDIRECT(A1&"!A3")&(B1&"!A3")

This formula gets the page name from cells A1 and B1 on the page the formula is on and uses them to references the cell A3 on the two other pages named in those cells.

For your particular formula . . .

If you want to make sheet1 dynamic, you need to pull the value from somewhere. Let's assuming it's in cell A1 on the sheet where the formula resides. Set the value of cell A1 to be the name of the sheet you wish to use.

I didn't check the last portion but I think I escaped the quotes correctly.

=SOMMEPROD((INDIRECT(A1&"!$C$2:$C$2000=$A3"))((INDIRECT(A1&"!$B$2:$B$2000=D$2"))((INDIRECT(A1&"!$D$2:$D$2000=$B3"))((INDIRECT(A1&"!$E$2:$E$2000=$C3"))((INDIRECT(A1&"!$I$2:$I$2000=\"Ongoing\"")))