I’m working with Google Sheets and need help with dynamic sheet references. I have two worksheets - one contains employee schedule data organized by weekdays in different columns, and another creates payroll summaries.
Currently I’m using direct references like:
='Weekly Schedule - 10.06'!C4
But I want to put the sheet name (‘Weekly Schedule - 10.06’) in a specific cell and reference that cell in my formulas instead of hardcoding the sheet name.
The reason is I need to create similar workbooks for multiple locations each week. Right now I have to manually update formulas in about 70 columns every time, which takes forever. If I could just change one cell with the sheet name and have all formulas automatically point to the right place, it would save me tons of time.
Is there a way to make formulas reference sheet names dynamically using cell values?
yep, just use INDIRECT! put your sheet name in A1, then go with =INDIRECT(A1&"!C4") instead. just make sure the name in A1 is exact - spaces n all - or it won’t work.
INDIRECT is exactly what you need. I use this all the time for batch processing - works like a charm. Just put your sheet name in a reference cell, then build your formula like =INDIRECT(B2&"!C4") where B2 has the sheet name. Here’s what caught me off guard: INDIRECT needs the exact sheet name format, including special characters and spaces. If your sheets have single quotes or spaces, format them as 'Weekly Schedule - 10.06' in your reference cell. This saves me tons of time with multiple location reports - I just update one cell instead of fixing dozens of formulas.
INDIRECT is precisely what you need. I employ this for quarterly reports across departments, and it works flawlessly. One aspect that hasn’t been mentioned is that you can concatenate parts of the sheet name as well. For instance, if you have a base name in A1 and a date in B1, you can utilize =INDIRECT(A1&" - "&B1&"!C4") to create the full reference. However, be mindful that INDIRECT does not automatically update when you rename sheets, so you’ll have to adjust your reference cells accordingly. While it may be slightly slower than direct references, with 70 columns, you likely won’t notice a significant difference. This technique has reduced my weekly setup from hours to merely minutes.