Forum Discussion
Solved
Referencing a Cell for a Worksheet
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!
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")
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")HansVogelaar 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"
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.
