How to reference a dynamic tab in excel (maybe indirect, but not sure)

Hi Everyone,

I have a question regarding making a template sheet in excel with formulas, with this template sheet being able to move to different workbooks and still working as in the first workbook. So, it is referencing a dynamic tab instead of a fixed tab. For example, I have a workbook titled Region 1. In this workbook I have the 'data' sheet from which the template sheet, which i have named 'data cleaned' has to extract the data. I have used different formulas to do this, some are IF formulas, such as:

=IF(Data!AL2="";"NA";Data!AL2)

and

=IF(Data!AM2="No";"0";IF(Data!AM2="";"NA";Data!AN2))

or formulas using index (in another sheet titled 'HF2', referencing this cleaned data), such as:

=INDEX('Data cleaned'!$U$4:$RN$4;1+11*(ROWS(C$15:C15)-1))&""

However, these formulas specifically refer to the 'data' sheet in the workbook Region 1. If I copy my 'data cleaned' sheet into for example workbook Region 2 that also contains a 'data' sheet, the template sheet will still refer to the data in the 'data' sheet from workbook Region 1 and not to the sheet in Region 2, which I want it to do. I have been looking into the INDIRECT function, but can't figure out how to combine INDIRECT with the above formulas (if that is even possible).

Does anyone know how to reference a dynamic tab in the above formulas?

Thank you

Hi Richard,

That does work, thanks! The only thing is that excel crashes when trying to update the fields, but oh well.

Hi Richard, I think it was just my excel being overworked. But I got it to work now. Thanks again!