I’m working with Google Sheets and need help with dynamic sheet references. I have two worksheets - one contains employee work schedules organized by weekdays in different columns, and another creates payroll calculations.
Currently I’m using direct references like:
='Employee Schedule - Week 10.13'!C4
What I want to do is put the sheet name (‘Employee Schedule - Week 10.13’) in a specific cell and then reference that cell in my formulas instead of hardcoding the sheet name.
The reason I need this is because I manage 10 different locations and create new schedule and payroll sheets every week. Right now I have to manually update around 70 different formulas each time to point to the new sheet names. This takes forever and is really tedious.
If I could just change one cell with the sheet name and have all my formulas automatically reference the correct worksheet, it would save me hours of work each week. Is there a way to make this work in Google Sheets?
been there! use =INDIRECT(“'”&A1&“'!C4”) where A1 has your sheet name. the extra quotes handle spaces in sheet names. saved me tons of time with a similar multi-location setup.
The INDIRECT function can be a game-changer for your scenario. I’ve faced similar frustrations with managing multiple sheets before. You can place your sheet name in a designated cell, let’s say A1, and then reference it in your formulas like this: =INDIRECT(A1&“!C4”). So if A1 contains ‘Employee Schedule - Week 10.13’, it will dynamically pull data from C4 of that sheet. This way, when you need to update for a new week, simply change the content of A1 and all your formulas will adjust accordingly. Just make sure that the sheet name in A1 is correct to avoid any REF errors, especially if there are typos or special characters in the names. This method has significantly streamlined my workflow for over two years.
Had this exact issue with quarterly reports across departments. INDIRECT with concatenation works great - put your base sheet name in B1 (like ‘Employee Schedule - Week 10.13’) then use =INDIRECT(B1&“!C4”). Just make sure you wrap the cell reference and range in quotes properly. Heads up though - INDIRECT doesn’t auto-update when you rename sheets, so your reference cell has to match the actual sheet name exactly. I keep backups of my original formulas too. This trick dropped my weekly updates from 3 hours to 10 minutes.