 SOLVED

New Contributor

# 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!

3 Replies
best response confirmed by rkrastel (New Contributor)
Solution

# Re: Referencing a Cell for a Worksheet

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")``

# Re: Referencing a Cell for a Worksheet

@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?

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"

# Re: Referencing a Cell for a Worksheet

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.