ExcelInsider Team
In Excel, the INDIRECT function helps you build references using text strings instead of hardcoded cell or range references. This is especially useful when you want to dynamically pull data from multiple sheets, such as monthly reports or regional sales tabs, without changing your formula structure. The INDIRECT function transforms a cell or string containing a sheet name or address into a working reference.
In this article, you’ll learn all the practical ways to use INDIRECT to reference other sheets, from basic cell lookups to structured table references, VLOOKUP integration, and error-proof dynamic referencing.
Steps to use INDIRECT function to reference different sheets in Excel:
➤ Firstly, create a new sheet and give it column headers like Sheet Name, Output and Note for clear understanding.
➤ In the A2 cell of the Dashboard sheet, type the name of the sheet you want to reference (e.g., Sales_Jan).
➤ In C2 cell, enter the formula:
=INDIRECT(“‘” & A2 & “‘!B2”)
➤ Press Enter to display values.
Reference a Fixed Cell on Another Sheet Dynamically
A basic use of INDIRECT function is referencing a fixed cell from another sheet using a cell that contains the sheet name. We will derive the Price of product such as Pen by referencing the sheet name from another cell. This is helpful when you want to create a central dashboard that can fetch data from any sheet based on user input.
To follow the examples in this article, we will use a sample sheet called Sales_Jan which contains data columns with Product, Price, Quantity, Region, and Score.
Steps:
➤ Firstly, create a new sheet and give it column headers like Sheet Name, Output and Note for clear understanding.
➤ In the A2 cell of the Dashboard sheet, type the name of the sheet you want to reference (e.g., Sales_Jan).
➤ In C2 cell, enter the formula:
=INDIRECT(“‘” & A2 & “‘!B2”)
➤ Press Enter.
This returns the value from cell B2 (Price of Pen) based on the sheet named in A2.
➤ You can also use this alternative formula in a blank cell like B5:
=INDIRECT(“Sales_Jan!C3”)
This fetches the Quantity of Eraser from the Sales_Jan sheet directly without relying on another cell.
Return the Value of a Named Range from Another Sheet
If your sheet such as Sales_Jan has named ranges like Data covering a range like B2:B6 (Prices), you can return their contents dynamically using the INDIRECT function. This method is useful when named ranges are standardized across sheets.
Steps:
➤ Create a named range called Data on Sales_Jan sheet covering Prices (B2:B6).
➤ In the A2 cell of another sheet, type the sheet name (e.g., Sales_Jan).
➤ In B2 cell, type the named range (e.g., Data).
➤ In C2 cell, enter formula:
=SUM(INDIRECT(“‘” & A2 & “‘!” & B2))
➤ Press Enter.
This will sum the range B2:B6 (Data) from the sheet Sales_Jan.
Sum a Range on Another Sheet Based on Sheet Name
When the cell range to be summed is consistent (e.g., B2:B6 for prices), you can use the INDIRECT function to construct the range based on a sheet name. This makes it easy to swap sheets without editing the formula.
Steps:
➤ In the A2 cell of a blank sheet, type the sheet name (e.g., Sales_Jan).
➤ In B2 cell, enter this formula:
=SUM(INDIRECT(“‘” & A2 & “‘!B2:B6”))
➤ Press Enter.
This returns the sum of prices (column B) for the sheet specified in A2 cell.
Dynamically Reference Row Numbers Using INDIRECT with ROW Function
Use INDIRECT with ROW function and drag formulas down for dynamically referring to corresponding rows in another sheet. This works great for pulling lists, like the Price column from the dataset.
Steps:
➤ Type Sheet Name in A2 cell such as Sales_Jan.
➤ In B2 cell, use this formula:
=INDIRECT(“‘” & A$2 & “‘!B” & ROW())
➤ Drag the formula down.
This fetches prices from rows B2, B3, etc., of the Sales_Jan sheet.
Reference the Same Cell Address on Another Sheet Using INDIRECT and CELL Function
This method uses the CELL function to capture the address of a specific cell (like A2) and the INDIRECT function to pull the corresponding value from another sheet such as Sales_Jan. It’s helpful when you want the same relative cell on a different sheet based on input. Using this formula, we will find the Price of a product such as Marker based on helper cells.
Steps:
➤ In A2 cell, type the sheet name you want to reference, for example, Sales_Jan.
➤ In B2 cell, type any cell whose address you want to use as a reference, for example, B5 cell.
➤ In C2 cell, enter this formula:
=INDIRECT(“‘” & A2 & “‘!” & CELL(“address”, B2))
➤ Press Enter.
➤ Alternatively, use this formula to handle errors or blanks neatly:
=IF(OR(A2=””, B2=””), “”, IFERROR(INDIRECT(“‘” & A2 & “‘!” & CELL(“address”, B2)), “”))
Now your output shows the price of Marker based on the reference sheet in A2 and cell address in B2.
Use INDIRECT with VLOOKUP Function to Retrieve Values from Multiple Sheets
If your sheet (e.g., Sales_Jan) contains a structured table named Sales_Jan_Tbl, you can use INDIRECT with VLOOKUP function to dynamically pull values based on the table name. This approach is useful when you’re switching between months using dropdowns or typed text on the Dashboard.
Steps:
➤ Turn your data into a table pressing Ctrl + T or use the Insert tab >> Table option and check your headers.
➤ Name your table Sales_Jan_Tbl from the Table Design tab.
➤ In the A2 cell of another sheet, type the product name you want to search for (e.g., Pencil).
➤ In B2 cell, type the sheet/month prefix (e.g., Sales_Jan matching your table name Sales_Jan_Tbl).
➤ In C2 cell, enter the following formula:
=VLOOKUP(A2, INDIRECT(B$2 & “_Tbl”), 2, FALSE)
➤ Press Enter.
This formula searches for the product name in the first column of the named table Sales_Jan_Tbl, and returns the value from the second column (Price). For example, if A2 contains Pencil, it returns 0.8.
Frequently Asked Questions
What does the INDIRECT function do in Excel?
INDIRECT function converts text into a usable reference. It lets you construct dynamic links to cells, ranges, or sheets without hardcoding them, which makes dashboards and templates more flexible.
Can I pull values from other sheets dynamically?
Yes. By storing the sheet name in a cell and combining it with the INDIRECT function, you can pull values from different sheets without changing your core formula each time.
Does INDIRECT work with named ranges and tables?
Yes. You can store the name of a range or a table in a cell and use the INDIRECT function to turn that into a working reference, which makes formulas much more adaptable.
Can INDIRECT return values from closed workbooks?
No. INDIRECT function only works with sheets and ranges that are open in the same workbook. If the workbook is closed, the formula will return a #REF! error. To avoid this, wrap it up with IFERROR function so that it returns blank instead.
Wrapping Up
In this tutorial, you learned how to use the INDIRECT function in Excel to dynamically reference data from another sheet. Whether you’re working with single cells, named ranges, or structured tables, INDIRECT function adds flexibility to your spreadsheet logic. By combining it with functions like VLOOKUP, CELL, and ROW and wrapping it with IFERROR or IF you can build smarter dashboards and reporting tools. Feel free to download the practice file and share your feedback.
