How to apply the variable and functions (indirect, address) to this formula?

[vcheung]

I am using this formula "=SUMPRODUCT(('wk52'!$D$6:$AD$6=Summary!$K11)*('wk52'!$D$78:$AD$78))" in my Excel spreadsheet but I would like to set the worksheet name "wk52" as variable, please advise how to convert this formula by using the variable and the functions (indirect, address).

Thanks.

 

[maabadi]

I suppose your sheet name is in B6 then:

=SUMPRODUCT((INDIRECT(B6&!$D$6:$AD$6)=Summary!$K11)*((INDIRECT(B6&!$D$78:$AD$78))

 

[vcheung]

I suppose your sheet name is in B6 then:

=SUMPRODUCT((INDIRECT(B6&!$D$6:$AD$6)=Summary!$K11)*((INDIRECT(B6&!$D$78:$AD$78))

No, the sheet name is "wk52" currently and a new worksheet name "wk53" in following week, so I want to convert my given formula by setting the worksheet name as variable that the output can be changed by changing the variable in cover sheet. As I know it needs to use the function "indirect" and "address" to create the range with the variable but I have no idea how to make it. please help.

 

[maabadi]

Sorry previous formula not working Use this:

=SUMPRODUCT((INDIRECT("'"&B6&"'!"&"$D$6:$AD$6")=Summary!$K11)*((INDIRECT("'"&B6&"'!"&"$D$78:$AD$78"))))

Change B6 to your Cell address of Sheet name.

 

[maabadi]

AND if you want to Use active sheet name at formula.
1. You can delete sheet name at formula easily then formula is:

=SUMPRODUCT($D$6:$AD$6=Summary!$K11)*($D$78:$AD$78))

or if you what Extract sheet name Separately:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

AND combine it with your formula:

=SUMPRODUCT((INDIRECT("'"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"'!"&"$D$6:$AD$6")=Summary!$K11)*((INDIRECT("'"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"'!"&"$D$78:$AD$78"))))