How to make worksheet name as variable?

[Eric]

In my workbook, some formulas are like this:

=SUMPRODUCT(($E2<'sheet01'!$G$1:$G$500)*($F2>'sheet01'!$G$1:$G$500)*($C2='sheet01'!$E$1:$E$500)*($B2='sheet01'!$F$1:$F$500),'sheet01'!$J$1:$J$500)

'sheet01' is the name of one of my worksheets. It appears several time in
the worksheet.
When I want to make them refer to another worksheet, I have to change many
instances of the name.
Can I input the worksheet name into one of the cells, and the formulas get
the name from the cell, so that I just have to change it once?

Thanks in advance!!

 

[papou]

Hello Eric
Yes you can use the INDIRECT function to build your reference.
But you will have to be careful about the (')s preceding and finishing the
sheet name.
eg
With just the sheet name in B1
=INDIRECT("'" & B1 & "'!$G$1:$G$500)

HTH
Cordially
Pascal

 

[papou]

Eric
To allow a better visibility in your final formula, you could also consider
defining a name for the indirect formula.
So the defined name could be something like : MyShName and would refer to:
=INDIRECT("'" & B1 & "'!$G$1:$G$500)

And since there are multiple ranges refereing to this sheet, you could add
other names to refer to those specific ranges eg:
MyShNameRng1 : =INDIRECT("'" & B1 & "'!$G$1:$G$500)
MyShNameRng2 : =INDIRECT("'" & B1 & "'!$E$1:$E$500)
MyShNameRng3 : =INDIRECT("'" & B1 & "'!$F$1:$F$500)
MyShNameRng4 : =INDIRECT("'" & B1 & "'!$J$1:$J$500)

But this is only a suggestion ;-)

HTH
Cordially
Pascal

 

[Eric]

You helped me a lot!!
Thank you very much.

Eric
To allow a better visibility in your final formula, you could also
consider defining a name for the indirect formula.
So the defined name could be something like : MyShName and would refer to:
=INDIRECT("'" & B1 & "'!$G$1:$G$500)

And since there are multiple ranges refereing to this sheet, you could add
other names to refer to those specific ranges eg:
MyShNameRng1 : =INDIRECT("'" & B1 & "'!$G$1:$G$500)
MyShNameRng2 : =INDIRECT("'" & B1 & "'!$E$1:$E$500)
MyShNameRng3 : =INDIRECT("'" & B1 & "'!$F$1:$F$500)
MyShNameRng4 : =INDIRECT("'" & B1 & "'!$J$1:$J$500)

But this is only a suggestion ;-)

HTH
Cordially
Pascal

 

[papou]

My Pleasure ;-)
Thanks for your feedback.

Cordially
Pascal