Referencing a Cell for a Worksheet

[rkrastel]

Hello!

I am currently trying to understand whether or not this is possible and am looking for feedback/support from the community if so. 

Simply put, I have a formula that contains a reference to specific worksheets in the document. What I would like is to instead have those lines reference a specific cell that I can input the name of the desired tab to reference.

Here is an example of the formula I am currently utilizing:

=COUNTIFS('Run_#58'!$P$2:$P$1000,$A$56,'Run_#58'!$N$2:$N$1000,"PASS",'Run_#58'!$M$2:$M$1000,"21")

In the above formula, it is directly referencing a worksheet ("Run_#58"), but what I would like is for it to reference a Cell (ie. A1) that I could manually input whatever sheet I want to reference. 

For example, if I were to input "Run_#70" (the name of another sheet) in Cell A1, the formula would then update accordingly and would now reference 'Run_#70' instead of 'Run_#58'. 

Any tips or help would be greatly appreciated!

[#2 · Accepted solution]

@rkrastel 

You can use the INDIRECT function for this. With the worksheet name in A1:

=COUNTIFS(INDIRECT("'"&A1&"'!$P$2:$P$1000"), $A$56, INDIRECT("'"&A1&"'!$N$2:$N$1000"), "PASS", INDIRECT("'"&A1&"'!$M$2:$M$1000"), "21")

[#3]

@Hans Vogelaar thank you for the response!

I have just tried to utilize this formula as you presented it, however it does not seem to be returning the expected data result unfortunately.

Is there a specific format that the worksheet that would be defined in A1 would have to follow? 

For additional context:

This formula is representing the following where if it meets the criteria, it is defined as "1":

- Cells P2:P1000 (from referenced sheet) contain referenced A56

- Cells N2:N1000 (from referenced sheet) contain "PASS"

- Cells M2:M1000 (from referenced sheet) contain "21"

In this scenario, I can confirm that the original formula correctly outputs "1", but when I use the provided formula the output is "0"

[#4]

@rkrastel 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.