Is it possible to reference a sheet’s name stored in a cell for dynamic formulas? For example:
='DataLog - RegionB'!F4
automating updates for multiple reports.
I have experimented with this approach on a project where I needed to consolidate data from multiple regions. In my case, using the INDIRECT function was key to achieving the dynamic reference you are after. I stored the sheet name in a cell and then constructed the reference with INDIRECT like so: =INDIRECT(“'”&A1&“'!F4”). This method has proved reliable for me, although it can slow down calculation if you are referencing too many cells across sheets.
In my experience, dynamic referencing of sheet names stored in cells is workable but comes with trade-offs. I have found that although the INDIRECT function is almost the only direct method to achieve this dynamic behavior, it can sometimes slow down performance if your workbook has extensive data or many inter-sheet references. I opted to use a custom Google Apps Script in one project to update formulas periodically, which avoided recalculations on every change while still maintaining flexibility. Depending on your data scale, you might consider the script route to balance dynamic updating with efficiency.