I need help with a Google Sheets automation problem. I work with employee scheduling and have two types of sheets. The first is a daily staff sheet where employee IDs get scanned and their names and roles appear automatically. The second is a summary sheet that should collect and organize this data. Here’s my challenge: I create a new staff sheet every day with a different name. I want my summary sheet to automatically pull data from whichever staff sheet I specify, without having to manually update all the formulas each time. I thought I could put the sheet name in cell A1 and use formulas like =A1!B2 to reference that sheet, but this approach failed. The formula doesn’t recognize the sheet name from the cell reference. Is there a way to make formulas dynamically reference different sheets based on a cell value? Or maybe there’s a better approach to solve this automation problem? Any suggestions would be helpful since manually updating formulas daily is time consuming.
Use the INDIRECT function - it’s perfect for this. Instead of A1!B2, try =INDIRECT(A1&"!B2"). This makes Google Sheets treat whatever’s in A1 as part of the cell reference. I’ve done this tons of times for reports where sheet names keep changing. Just make sure the sheet name in A1 matches exactly, including spaces or special characters. The formula handles spaces fine. Need a bigger range? Use =INDIRECT(A1&"!B2:E10") or whatever you want. One heads up - if you rename sheets, INDIRECT won’t auto-update, so you’ll need to change A1 manually. But this beats updating multiple formulas every day and will save you a lot of time.
you could also use QUERY if you want more control. try =QUERY(INDIRECT(A1&"!A:Z"),"select B,C,D") to grab specific columns. i’ve been using this for inventory tracking and it’s way more flexible than basic references. just heads up - INDIRECT will slow your sheet down if you go crazy with it.
IMPORTRANGE works if your daily sheets are in separate files, but since they’re all in the same workbook, go with INDIRECT plus error handling. Try =IFERROR(INDIRECT(A1&"!B2"),"Sheet not found") - this stops your summary from breaking when the sheet doesn’t exist yet. I learned this lesson the hard way with rotating project sheets. Also, set up data validation in A1 with a dropdown of your sheet names. Makes it foolproof and cuts down on typos that kill the INDIRECT function. INDIRECT’s your best option here - just wrap it properly for clean error handling.