Formula points to text in a cell to identify sheet

[Nick70]

Hi,

I have a formula in cell C10 of Sheet1 equal to =COUNTIFS('CW 01'!I:I,B10,'CW 01'!K:K,X10) that points to sheet CW 01.

Is there a way to put text "CW 01" in Cell A1 of Sheet1 so that formula would look in sheet name equal to text in A1 (in this case CW 01)?
This way one could easily repoint formula simply by changing text in cell A1.
For example if we wanted formula to point to sheet CW 02 we could simply put text CW 02 in cell A1.

I tried formula =COUNTIFS('A1'!I:I,B10,'CW 01'!K:K,X10) but it does not work.

Any ideas how formula should look?

Thanks,
N.

 

[Fluff]

You could use

=COUNTIFS(INDIRECT("'"&A1&"'!I:I"),B10,INDIRECT("'"&A1&"'!K:K"),X10)

but it's volatile so will re-calc whenever anything on any open workbook changes

 

[Nick70]

Thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback.