INDIRECT function in excel- For consolidating data

March 18th, 2011 by Tips Master | Posted under Excel.

This is very useful excel function but not commonly used by excel users. INDIRECT as the name denotes returns the reference specified by a text string. There are lots of uses for this function and in this post i want to mention about a handy use of
this function which is “Get the values from a different sheet”


Lets consider this scenario , you have got a worksheet with more than 26 sheets containing data and you need to show the summary of the data in a single sheet. We have sales from 2005 to 2011 in each tab. See the sample data in the below screen shot.

INDIRECT Function in excel

In the summary sheet you want to get the sales for all the years as you see in the table below.

Indirect Function help

Manually linking to each cell is going to be time consuming  process .

Instead of that we are going to use the INDIRECT function in excel to get the values. To make the work simple, in the left most column i will have the name of the tabs which i will use in the INDIRECT function to refer to.

The syntax of the Indirect function is INDIRECT(ref_text,[a1]) , where

Indirect function syntax

1. ref_text is the is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
2. A1 is a logical value that specifies what type of reference is contained in the cell reference. If a1 is TRUE or ommitted it is interpreted as a A1-style reference. if a1 is FALSE then interpreted as a R1C1 style reference.
Learning the syntax we will work out our formula.
This will be the formula to be entered in to cell B2 in the Summary tab

B2 =INDIRECT(“‘”&$A2&”‘!”&ADDRESS(5,COLUMN()))
Drag the formula to the whole cells in the range B2 to H27. Hurray, you have values in all the cells linked to the respective tabs. Pretty simple right?
We will see now the formula , the first part “‘” is the single quote inside  double quotes and you will be concatenating the same with the reference to the sheet names you have in the “A” column . I have fixed the reference “$A” with a dollor sign so that we can drag the same to all the cells and the reference to column “A” will remain same.
I have added to it single quote and “!” symbol inside double quotes (“‘!”) so the end result of the same in the formula in the cell B2 will be (‘A’!). To this we will be adding the column and row reference  using the Address function .For the row reference i have  used the number 5 because it is remaining constant through out the range.
The final result in cell B2 will be ‘A’!$B$5 which will return the value from the cell B5 in sheet A.
The Summary sheet will look like the one below
Help on INDIRECT function
Now we will try to simplify our initial formula a little bit. We will use address function to get the sheet reference also.
Lets make the changes in the cell B2. The new formula is B2 =INDIRECT(ADDRESS(5,COLUMN(),1,1,$A2)) . This now uses the full help of the Address function. The sheet reference is now mentioned inside the Address function .
Copy the cell B2 to the entire range. The values will be same but you will have a more refined formula. The benefit of using the address formula will be that we can use match function inside it to get the column and row reference
which will make it more user freindly. More uses of ADDRESS function and INDIRECT function will be covered in another post.

Download the Excel 2007 version - Download the Excel file

Download Excel 2003 version -Indirect function to consolidate data

Waiting for your suggestion /Feed back.

Comments

16 Responses to “INDIRECT function in excel- For consolidating data”
  1. Mitesh says:

    Very nice and helpful. thanks for sharing this.

  2. suresh says:

    how do i open the files in excel after download

    • Tips Master says:

      Please tell me what is the Excel version you are using… I will send you the file once you let me know the excel version…

  3. Sudip says:

    Really good examples. Very helpful. Thanks a lot

  4. Sai Han says:

    Excellent explanation and tip.
    It helps me a lot.

  5. BinduAjeeth says:

    Hi,

    Its really useful one. Keep going!!!

  6. manikant says:

    i am very happy your dose is very right i read INDIRECT function is very well your side is helpful thanks

  7. Thomas says:

    Great example, The files will not open when I down load them

  8. Zafar Iqbal says:

    Files downloading from this web site become zip files. Just rename its extension from zip to xlsx. It will work well.

  9. Vikas Padale says:

    Very Usefull & innovative. Thanks & hope will get more from you.
    All The Best & Good Luck

  10. Sathya says:

    Very good example.Thanks a lot.

  11. Balaji says:

    Very nicely explained.. This has helped in consolidating huge data with ease..Thank you for sharing such a useful formula..

  12. rishikesh dubey says:

    i want more function and formula in exl in detail

Do you have any comments on INDIRECT function in excel- For consolidating data ?