Dynamic Tab Reference in Formula

[gahooja]

I have a sumproduct formula below on a summary sheet in a workbook which works fine. I have multiple tabs in the workbook. Is there a way i can reference each tab based on a cell and copy this formula over based on the tab name?

=SUMPRODUCT((Burt!B4:G4='Overall Summary'!V6)*(Burt!A7:A23='Overall Summary'!A20)*(Burt!B7:G23))

So this formula references tab name Burt but i want to be able to put in a cell number instead of burt and it will reference that tab and get my data. Is this possible?

For example if i have the list of tabs in the same worksheet as the formula
A1 - Burt
B1 - Smith
C1 - George

can i get the formula to do this
=SUMPRODUCT((A1!B4:G4='Overall Summary'!V6)*(A1!A7:A23='Overall Summary'!A20)*(A1!B7:G23))

 

[njimack]

=SUMPRODUCT(--(INDIRECT("'"&A1&"'!B4:G4")='Overall Summary'!V6),--(INDIRECT("'"&A1&"'!A7:A23")='Overall Summary'!A20),(INDIRECT("'"&A1&"'!B7:G23")))

or

=SUMPRODUCT(--(INDIRECT(ADDRESS(4,2,,,A1)&":"&ADDRESS(4,7))='Overall Summary'!V6),--(INDIRECT(ADDRESS(7,1,,,A1)&":"&ADDRESS(23,1,,,A1))='Overall Summary'!A20),(INDIRECT(ADDRESS(7,2,,,A1)&":"&ADDRESS23,7)))

 

[gahooja]

Thanks for the formula but i am getting a #VALUE! error. I tried to evaluate and it seems like everything calculated right so not sure where the error occurred. Thx again for your help

 

[njimack]

Does your original formula work? SUMPRODUCT usually requires all ranges to be of the same dimensions, and either 1-row high or 1-column wide.

 

[gahooja]

Yes my original formula is working, just tried it again and it populates it with a value that i was looking for in there.

=SUMPRODUCT((Burt!B4:G4='Overall Summary'!V6)*(Burt!A7:A23='Overall Summary'!A20)*(Burt!B7:G23))