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
Bookmarks