Forum Discussion
Solved
A formula that changes the sheet reference based on cell value with sheet name
Hi,
I am making an executive report on one page which pulls all the findings from each individual sheet into one sheet. I have 35 different tables which each reference one sheet (which I have 35 of). Each table references the same cells but in a different sheet. I want to be able to create a formula that's dynamic with the sheet name so I can copy over my formulas from one table to another without having to manually change the sheet reference on each one. So for example - currently I have a formula that's ='Sheet1'!GL8. I want the name of Sheet 1 to change based off what the title of the table is called. So for instance, if table 2 is called Sheet 2 so it would change to ='Sheet2'!GL8 - I can easily copy over the same formulas to table 2 and it will pull from Sheet 2 using the name of the table.
Thank you in advance.
You can achieve this by using the INDIRECT function combined with the CELL function to dynamically reference the sheet name based on the table title. Here's how you can do it:
Let's say you have your table titles in cell A1 of each table sheet. In cell A1 of "Sheet1", it would be "Sheet1", in "Sheet2" it would be "Sheet2", and so on.
In the destination sheet where you want to pull all the findings together, you can use the following formula:
=INDIRECT("'" & A1 & "'!GL8")
This formula will concatenate the contents of cell A1 with the necessary syntax for referencing another sheet in Excel. So if cell A1 contains "Sheet1", the formula will resolve to ='Sheet1'!GL8. If cell A1 contains "Sheet2", the formula will resolve to ='Sheet2'!GL8, and so on.
You can then copy this formula across your tables, and it will automatically adjust to pull data from the corresponding sheets based on the table titles. Just make sure the titles in cell A1 of each table sheet match the actual sheet names. The text was created with the help of AI.
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
NikolinoDE Thank you! The one concern I have with this method is that I have 35 tables (and counting more may be added) and there are about 15 times I will reference indirect in one table. And I heard that having too many indirects in a file can bog down the load time. Do you have experience with this/know at what point the file may have too many indirects?
You are correct that excessive use of the INDIRECT function, especially in large quantities across multiple formulas, can potentially slow down the performance of your Excel workbook. The INDIRECT function is known for its volatility, which means it recalculates every time any change is made in the workbook, even if the change does not affect the referenced cells directly. This can lead to increased calculation times and slower workbook performance, particularly in large and complex workbooks.
In your case, if you have 35 tables and each table contains multiple INDIRECT formulas referencing different cells on different sheets, it could potentially impact the performance, especially if your workbook is already large or contains a lot of other formulas and data.
To mitigate this, you may consider alternative approaches such as using named ranges or structured references instead of relying heavily on INDIRECT. Named ranges can provide a more efficient and structured way to reference cells and ranges across multiple sheets without the volatility of INDIRECT. Additionally, restructuring your workbook to minimize the number of cross-sheet references and optimizing your formulas for efficiency can help improve performance.
If you find that your workbook's performance is significantly affected by the use of INDIRECT or other volatile functions, you may need to consider redesigning your formulas or workbook structure to minimize their usage and optimize performance.
