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.

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

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

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

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.

Very nice and helpful. thanks for sharing this.

how do i open the files in excel after download

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…

Really good examples. Very helpful. Thanks a lot

Excellent explanation and tip.

It helps me a lot.

Hi,

Its really useful one. Keep going!!!

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

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

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

Very Usefull & innovative. Thanks & hope will get more from you.

All The Best & Good Luck

Very good example.Thanks a lot.

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

i want more function and formula in exl in detail