How to Use the INDIRECT Function to Get Values from Different Sheet in Excel (5 Practical Cases)

Get FREE Advanced Excel Exercises with Solutions!

INDIRECT function in Excel from a different sheet means extracting values from another worksheet using the INDIRECT function by mentioning the sheet name and the cell references (in text form).

In this Excel tutorial, you will learn how to use the INDIRECT function to get values from different sheets in Excel.

For example, in the following image, we have Sheet Names, Cell references, and their Total Prices. In cell B6, we entered the sheet names (e.g., Sales Report Q1) and inserted the cell references in the Cell column. Then, in the Total Prices column, we applied a formula with the INDIRECT function. The INDIRECT function takes the B6 text value as a sheet name reference and searches the F6 cell of that sheet. In cell D6, it returns the value of the F6 cell in the Sales Report Q1 worksheet.

Featured Image to Use the INDIRECT Function to Get Values from Different Sheet in Excel

Using the INDIRECT function to get values from different sheets can be done in various effective ways for their particular purposes. You can refer cells across different sheets by using two separate referencing styles; the default style (e.g., A1) and the R1C1 style (e.g., row 1, column 1). To extract a list of values as an array from different sheets, you can use named ranges and refer to them by using the INDIRECT function. Moreover, you can lock cells and get error-free values while adding or removing rows or columns in Excel. You can also look up data from different sheets and create a drop-down list to analyze data with the INDIRECT function.


Excel INDIRECT Function

The INDIRECT function returns references according to the text value. It takes the value as a string (ref_text) and returns the references with that name. These references can be cells, named ranges, or worksheets.

Syntax

=INDIRECT(ref_text, [a1])

This function is a volatile function, which means that each time you open the workbook, it recalculates the data. So, sometimes it can be slow to operate the Excel file for heavy data. One of the most useful aspects of the INDIRECT function is that when you insert new rows or columns, it won’t change its reference. Similarly, references won’t change when you remove the existing ones.
In the image below, you can get an overview of the INDIRECT function.

Overview Image of the INDIRECT Function in Excel


1. Use INDIRECT Function to Dynamically Refer Cells Across Different Sheets in Excel

In this method, we will show you how to refer cells across different sheets with the help of the INDIRECT function. We will use two sample data sets in two different sheets named Sales Report Q1 and Sales Report Q2.

In the following data sets, we have collected the sales details for the first quarter (April, May, and June) and the second quarter (July, August, and September). The image below shows the two datasets, with the products’ names varying with their purchase dates, retail prices, and quantities.

Sample Data Sets


1.1 Refer Cells with Default Referencing Style

In this section, we will refer to cells with default referencing cell style from different sheets (e.g., Sales Report Q1) and get data in a new sheet. We will use the INDIRECT function to do that.

Sheet Name and Cell References in Default Style

Here, we have taken cell references from F6 to F18 to find the total prices of different quarters.

  • In cell D6, enter the following formula:
=INDIRECT("'" & $B$6 & "'!" &C6)

Using the INDIRECT Function to Refer Cells with Default Referencing Style

AutoFill the Total Prices Column containing Formula with the INDIRECT Function

GIF to visualize the use of the INDIRECT Function to Get Values from Different Sheets


1.2 Refer Cells with R1C1 Referencing Style

This method is a bit different from the previous one with the referencing style. We will use the R1C1 referencing style, which refers to row number and column number (e.g. F6 cell means row 6, column 6). In column C and column D, we have taken all the cell references in R1C1 format.

Sheet Name and Cell References in R1C1 Style

  • In cell D6, enter the following formula to get the values of the total prices:
=INDIRECT("'" & $B$6 & "'!R" & C6 & "C" & D7,FALSE)

Using the INDIRECT Function to Refer Cells with R1C1 Referencing Style

  • Press Enter and drag the Fill handle to copy the formula.

AutoFill the Total Prices Column containing Formula with the INDIRECT Function

  • We have created a drop-down list to select the sheets. Now, change the sheet name in the drop-down list and get the values from different sheets.

GIF to visualize the use of the INDIRECT Function to Get Values from Different Sheets

Read More: Create Drop-Down List Using INDIRECT Function in Excel


2. Extract a List of Values with INDIRECT Function from Different Sheets Using Named Range

In this section, we will create named ranges on different sheets and get values on a new sheet using the INDIRECT function. The benefit of using the Named Range is that we can extract a list of whole values in an array format; we don’t need to copy or use AutoFill.

  • Firstly, we will create a Named Range (Total_Prices_1) with the Total Prices column in the sheet Sales Report Q1.

Creating a Named range in the Sales Report Q1 Sheet

  • Similarly, we will create a Named Range (Total_Prices_2) with the Total Prices column in the sheet Sales Report Q2.

Creating a Named range in the Sales Report Q2 Sheet

  • To get the total prices for the first quarter from the Sales Report Q1 sheet, enter the following formula in cell D6 and press Enter.
=INDIRECT("'" & $B$6 & "'!" & C6)

Using the INDIRECT Function with Named Ranged to Get Values from Sales Report Q1 Sheet

  • Similarly, to get the total values for the second quarter from the Sales Report Q2, insert the following formula in cell H6:
=INDIRECT("'" & $F$6 & "'!" & G6)

Using the INDIRECT Function with Named Ranged to Get Values from Sales Report Q2 Sheet

Read More: INDIRECT Function with Sheet Name in Excel


3. Lock Cell References with INDIRECT Function to Prevent Automatic Formula Changes Across Different Sheets

In this section, we will lock cell references across different sheets using the INDIRECT function. It is a very useful method as it helps to avoid errors when the cell reference changes while adding or removing rows or columns.

In our following example, first, we will calculate the total prices from the sheet Sales Report Q1 by using the SUM and INDIRECT functions. We will use the INDIRECT function to lock the cell reference.

Again, we will calculate the same total using only the SUM function. Then, we will insert a new row in the source sheet, Sales Report Q1.

We will see that a formula using the INDIRECT function gives the newly updated total, but using only the SUM function will not result in an updated total. Follow the procedures below to get it done:

  • In cell D6, enter the following formula in the Sales Report Q1 sheet to get the Total Prices column.
=INDIRECT("'" & $B$6 & "'!" & C6)

Using the Excel INDIRECT Function to Get Values from Different Sheet

  • Calculate the Total Prices with the following formula in cell G6.
=SUM(INDIRECT("D6"):D1048573)

Combining the SUM and the INDIRECT functions to Calculate the Total Prices

  • Here, we will again calculate the Total Prices of Sales Report Q1 column by using the SUM function in cell G7:
=SUM('Sales Report Q1'!F6:F1048576)

Using the SUM Function to Calculate the Total Prices

  • Now, we will insert a new row in the Sales Report Q1 sheet.
  • You can see that the G6 cell has been updated with the new total of $3,483.04, but the G7 cell has remained the same at $3,391.61.

GIF to Visualize the Locked Cell References with INDIRECT Function to Prevent Automatic Formula Changes Across Different Sheets

Read More: How to Use Excel INDIRECT Range


4. Create a Drop-Down List and Analyze Data from Different Sheets with INDIRECT Function in Excel

In this section, we will create a data validation drop-down list to get data from different sheets. It is a very useful method, as we can analyze data from different sheets and create a summary report. We will extract that information using the INDIRECT function.

  • Firstly, select cell C5, where you want to create the drop-down list with the sheet names.
  • Go to the Data tab.
  • Click on the Data Validation command from the Data Tools group.
Selecting the Data Validation Command from the Data tab

Click on the image to see the full view

  • From the Data Validation dialog box, go to the Settings tab.
  • Select the List option for the Allow criteria.
  • Type the names of the sheets Sales Report Q1, Sales Report Q2 in the Source criteria.
  • Finally, click OK.

Customize the Data Validation box

  • So, you will get your data validation drop-down list in cell C5.
  • Now, we will assign the following formulas to calculate the total sales, average sales, top sales, top quantities sold, and maximum retail prices of a selected sheet from the drop-down list.
  • Formula to calculate the total sales:
=SUM(INDIRECT("'"&$C$5&"'!"&"F6:F18"))
  • Formula to calculate the average sales:
=AVERAGE((INDIRECT("'"&$C$5&"'!"&"F6:F18")))
  • Formula to calculate the top sales:
=MAX((INDIRECT("'"&$C$5&"'!"&"F6:F18")))
  • Formula to calculate the top quantities sold:
=MAX((INDIRECT("'"&$C$5&"'!"&"E6:E18")))
  • Formula to calculate the maximum retail price:
=MAX((INDIRECT("'"&$C$5&"'!"&"D6:D18")))
  • Now, choose any of the sheet names from the drop-down list and get your analytical data from different sheets.

GIF to visualize the Changes Due to Applying Formula

Read More: How to Convert Text to Formula Using the INDIRECT Function in Excel


5. Lookup Data from Different Sheets in Excel with INDIRECT and VLOOKUP Functions

In this section, we will look up data from different sheets for certain criteria. To do this, we will combine the VLOOKUP function with the INDIRECT function.

The benefit of using the VLOOKUP function here is that we do not need to specify the cell reference in the INDIRECT function. The INDIRECT function takes a table range (e.g., B:F). So, we can get any value from the table range just by changing its column index number in the VLOOKUP function. We can find total sales, quantities, purchase dates, or any other parameters only by changing the column index number.

  • To get the sold quantities in the first quarter, enter the following formula in cell C6:
=VLOOKUP($B6,INDIRECT("'"&F$6&"'!"&"B:F"),4,FALSE)
  • Then, drag down the Fill handle to copy the formula along the Quantity (Q1) column.

Combining the VLOOKUP and the INDIRECT Functions to Look up Values for the First Quarter from Different Sheet in Excel

  • Similarly, to get the quantities sold in the second quarter, enter the following formula in the D6 cell:
=VLOOKUP($B6,INDIRECT("'"&F$7&"'!"&"B:F"),4,FALSE)
  • Then, drag down the Fill handle to copy the formula along the Quantity (Q2) column.

Combining the VLOOKUP and the INDIRECT Functions to Look up Values for the Second Quarter from Different Sheet in Excel

Read More: How to Use Indirect Address in Excel


An Alternative to INDIRECT Function: CHOOSE and VLOOKUP Functions

In this section, we will show you an alternative method to the previously discussed method. Here, we will use the CHOOSE and VLOOKUP functions.

  • Enter the following formula in cell C6 to get the value of sold quantities in the first quarter:
=CHOOSE(1,VLOOKUP(B6,'Sales Report Q1'!$B$6:$F$18,4,FALSE),VLOOKUP(B7,'Sales Report Q2'!$B$6:$F$18,4,FALSE))
  • Then, drag down the Fill handle to copy the formula along the Quantity (Q1) column.

Combining the VLOOKUP and the CHOOSE Functions to Look up Values for first Quarter from Different Sheet in Excel

  • Similarly, enter the following formula in cell D6 to get the value of sold quantities in the second quarter:
=CHOOSE(2,VLOOKUP(B6,'Sales Report Q1'!$B$6:$F$18,4,FALSE),VLOOKUP(B6,'Sales Report Q2'!$B$6:$F$18,4,FALSE))
  • Then, drag down the Fill handle to copy the formula along the Quantity (Q2) column.

Combining the VLOOKUP and the CHOOSE Functions to Look up Values for Second Quarter from Different Sheet in Excel


Download Practice Workbook


In this article, we have shown you multiple ways to use the INDIRECT function to get values from different sheets in Excel. From now on, you can dynamically refer to cells and ranges across multiple worksheets. You will also be able to lock references in different sheets to avoid formula changes. Moreover, you can look up data from different sheets and analyze it by applying formulas.

Leave us a comment for any further queries. The Exceldemy team will eagerly respond to you in no time.


<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

12 Comments
  1. Thank you for sharing.
    Why it cannot work between different workbooks if is closed?
    Is there any work around to get a data from a workbook even if it is closed??
    Thank you again..

  2. Thank You for the exact answer I spent a lot of time looking for. I found many answers on the web but nothing like the simple one you gave that works for exactly what I was looking for. Thanks again!

  3. Thank you very much for this write-up. It saved me as well!

  4. Hi, what could be wrong in my formula as this gives me #REF! error.
    =COUNTIFS(INDIRECT(“‘Attendance!”&(ADDRESS(MATCH($C2,Attendance!3:3,0),11,4,1)&”:”&ADDRESS(MATCH($C2,Attendance!3:3,0),40,4,1))),”P”)

  5. Hi, when using INDIRECT when the source cell has a hyperlink, how do I bring across the cell contents including the hyperlink. My result cell has the cell contents but the hyperlink is not present.

    • Hi GEOFFREY! Your problem is not quite clear to us. However, if you simply copy and paste the cell contents, the hyperlinks will also be pasted. If this is not what you were expecting, then we would request you to elaborate on your issue. You can also send your Excel file to us through email. Thank you!

  6. I’m sorry but I may not be in the right place or I’m not fully understanding your process.
    From what I gather, what you’re doing is accessing information via different workSHEETS. For me, that seems fairly straight forward, but my problem that I’m looking for that is not represented here is gathering said information from a different workBOOK. Again, I may be in the wrong place, but maybe you could point me in the right place to handle my problem. Thanks!

    • Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

      https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

      Moreover, you can create a simple formula:

      =’D:\SOFTEKO\[task_problems.xlsx]Sheet1′!$C$18

      Where D is the drive location, SOFTEKO is the folder name, task_problems is the desired excel file, Sheet1 is the worksheet, and C18 is the required cell value.

      Here, make changes according to your requirements.

      Good luck.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo