Sheet Name from cell value in formula - Not Volatile (INDIRECT)

[Norgewalk]

I could not find this in another thread so please point me there if it exist.

I've been using INDIRECT when I need to use a refer to tab name based on a value in a cell, but it uses the volatile INDIRECT formula. Is there a better way to do this using INDEX or something that isn't volatile?

Example of what I use now:

D1 is a text value that is also the name of a tab, say "TabName"

This formula will search column A in the TabName sheet and return "Yes" if the value in C2 is found and "No" if it is not.

Formula:
=IF(IFNA(MATCH($C2,INDIRECT("'"&D$1&"'!"&"$A:$A"),0),0)>0,"Yes","No")

INDIRECT("'"&D$1&"'!"&"$A:$A") in this case results in 'TabName!$A:$A which is what I need in the formula.


I hope some has overcome this hurdle. So far I have not been able to.

 

[FDibbins]

Other than VBA, INDIRECT is the only way to use a text reference in a cell

What is the problem with using INDIRECT?