How to reference a sheet using a cell

[portugeezers]

So I have a master sheet and multiple more detailed sheets that feed into the master. All the smaller sheets follow the same template so I am able to use indirect addressing to extract the data I need for the master sheet. Now on the master sheet, I am trying to check if a value/cell in the master sheet is contained in an array (row of 10 cells) for each of the smaller sheets. How should I do that? I am using the current formula:

=IF(COUNTIF('City A'!$D$10:$D$20,$E$2),"Yes")

Now instead of typing City A for the sheet, I want to try and reference the cell I created for the sheet and all other sheets. Is this possible?

Any help would be great!

 

[Legacy 143009]

Yes, INDIRECT function is what you are looking for. Assuming your sheetnames are in column A:

=IF(COUNTIF(INDIRECT("'"&A2&"'!$D$10:$D$20"),$E$2),"Yes")

Using INDIRECT is not recommended because it is a volatile function which means it will recalculate each time you open the workbook that may lead to increased load times.