MS Excel: Using Sheet Names from Cells in Formulas for automation
- Fakhriddinbek
- Aug 30
- 4 min read
Updated: Sep 5
When working with multiple sheets in Excel, you may want a formula to pull values from different sheets automatically. Instead of hard-coding the sheet name inside the formula, you can place the sheet names in cells, and let Excel reference them dynamically. This makes your workbook flexible, scalable, and much easier to manage.
Excel is a powerful tool, but sometimes its limitations can hinder productivity, especially when dealing with data spread across multiple sheets. The common challenge of referencing sheet names dynamically is a perfect example. While a simple SUMIFS formula works for one sheet, it breaks down when you try to drag it across columns to pull data from other sheets. Fortunately, there's a solution using the INDIRECT function that allows you to automate this process, turning a tedious manual task into a simple drag-and-drop operation. This article will show you how to master this technique to consolidate your data efficiently.
The Problem in MS Excel Using Sheet Names from Cells in Formulas for automation
Normally, if you write a formula like:
='Sheet1'!B2
Excel locks this to Sheet1. If you drag the formula down or across, the cell references may change, but the sheet name stays fixed. For reports with many sheets, editing every formula becomes a nightmare.
The Solution: INDIRECT Function
The secret weapon for MS Excel Using Sheet Names from Cells in Formulas for automation is Excel’s INDIRECT function.
Step 1. Store sheet names in a column
Suppose you have a summary sheet where column A lists the sheet names:
Step 2. Write the formula with INDIRECT
In cell B2, type:
=INDIRECT("'" & A2 & "'!C5")
Explanation:
A2 → contains the sheet name (e.g., Sheet1).
"'" & A2 & "'!C5" → builds the reference 'Sheet1'!C5.
INDIRECT(...) → tells Excel to treat the text as a real cell reference.
Step 3. Drag the formula
When you drag down, A2 changes to A3, A4, etc.
The formula automatically updates to pull from Sheet2!C5, Sheet3!C5, and so on.
MS Excel Using Sheet Names from Cells in Formulas for automation Example:
You have a consolidated table, where column names and sheet names are the same. It is expected that sheet names and column names will continue to be named with year numbers as it now in the table.
Unfortunately, Excel's built-in formulas, including SUMIFS, can't directly use a cell value as a sheet name reference. This is a limitation that prevents a single formula from automatically adjusting the sheet name as you drag it across columns. The standard approach requires a formula for each sheet.
However, with advanced INDIRECT function to achieve the automation you're looking for. The INDIRECT function converts a text string into a valid cell reference. Here’s how you can use it to create a single, drag-and-drop formula.
How to Use the INDIRECT Function in this example
Start with the first cell: In cell C3 of your summary sheet ("Sheet1"), enter the following formula:
Excel
=SUMIFS(INDIRECT("'"&C$2&"'!$C$3:$C$12"); INDIRECT("'"&C$2&"'!$B$3:$B$12"); $B3)
Understand the formula:
C$2: This is the key to automation. It references the cell containing the year "2020". The $ before the 2 locks the row, but not the column, so when you drag the formula to the right, it will automatically change to D$2 (for "2021"), E$2 (for "2022"), and so on.
"'"&C$2&"'!$C$3:$C$12": This part builds the text string for the sum range. It combines the sheet name from cell C$2 with the '$C$3:$C$12' cell range. The single quotes ' are added around the sheet name because it contains numbers.
INDIRECT(...): This function takes the constructed text string (e.g., '2020'!$C$3:$C$12') and turns it into a real, usable reference that SUMIFS can understand.
$B3: This is your criteria, which is the name in column B. The $ locks the column A so that as you drag the formula to the right, it always looks at the names in column A.
Drag the formula:
Drag the formula in C3 down to C12. This will calculate the 2020 totals for all names.
Now, drag the entire column of formulas (from C3 to C12) to the right, to column H. The formula will automatically adjust for each year (2021, 2022, etc.) by referencing the sheet name in the header row.
Things to Keep in Mind
Sheet names with spaces → Excel requires ' around names;
Performance → INDIRECT is volatile, meaning it recalculates every time the sheet changes. With very large workbooks, this may slow things down;
No error checking → If the sheet name is misspelled in column, you’ll get a #REF! error.
Summary
When using Excel, you can't simply drag a SUMIFS formula across columns to reference different sheets, as it requires a specific sheet name. This can be time-consuming and prone to error if done manually. The advanced solution is to use the INDIRECT function, which turns a text string into a valid cell reference.
By creating a formula with INDIRECT that references the column headers (which should match your sheet names), you can make the formula dynamic. Once you've set up the formula in the first cell, you can drag it down and across to automatically pull data from all your other sheets, saving significant time and effort.
Here is the file from example:
Comments