Software

Let Excel's Indirect function keep data at your fingertips

Displaying key data on a sheet at the beginning of a workbook makes it easy to get a quick snapshot of essential information. See how simple it is to put this handy trick to work.
Rather than have your users search through dozens of worksheets for specific data, put the data they need most in its own worksheet in the front of the workbook. For example, say you have the worksheet shown in Figure A, which keeps track of replacement costs in your Inventory workbook.

Figure A

sample worksheet

Follow these steps:

  1. Click on E8.
  2. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  3. Enter Total_cost in the Name text box and then click OK.
  4. Click on E9.
  5. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  6. Click on E10.
  7. Enter Most_Expensive in the Name text box and then click OK.
  8. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  9. Enter Least_Expensive in the Name text box (Figure B) and click OK.

    Figure B

    worksheet setup

    1. Create a new blank sheet, move it to the front of the workbook, and enter the data shown in Figure C.

    Figure C

    worksheet data

    Now, when the user opens the workbook, Excel displays the data shown in Figure D for replacement costs on the first sheet of the workbook.

    Figure D

    indirect data


    Miss an Excel tip?

    Check out the Microsoft Excel archive and catch up on other Excel tips.

    Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

    26 comments
    philrunninger
    philrunninger

    The example doesn't show off the power of the function, nor does it compel one to use it. Here is one way I've used it in the past. I have multiple sheets, identical in structure, but with different data. They could be projects, yearly budgets, etc. I also have a dashboard worksheet as a front-end, on which I have a cell with a data validation list. The list contains the other sheets' names. Then all my dashboard values can come from the sheet I selected in from the list. For example, if man-hours is in H3 on all sheets, I can report any project's value on the dashboard like so: A1: Project B1: Foobar (This is my validated list) A2: Man-Hours B2: =Indirect(B1&"!H3") I'm sure there are other uses for Indirect, and even other ways of doing what I just described. Maybe a 3D range - who knows... However, Indirect function calls are hard to debug, and when you start doing this sort of thing, you might want to consider using a database instead.

    techrepublic
    techrepublic

    The example fails to identify the advantage (if any) over simply referring to the 3 cells directly, as in Inventory!B18, Inventory!B19, and Inventory!B20. The example illustrates the use of INDIRECT(), but why is it better in this case?

    SSirish
    SSirish

    Is there any way to create a link between a cell and a worksheet.

    jonathank
    jonathank

    This example contains typos and is not descriptive of how INDIRECT works. Follow along with this example if you like. If you want to refer to the contents of cell J10, which has a value of 50, you can enter =J10. If you define a name for J10 of "Shirley", any reference you create at that point is to =Shirley, and displays the value of J10, which is 50. These are direct references. The INDIRECT Function returns the reference specified by a text string. Text string references to cells are in the format $A$1 for cell A1. This allows you to specify the row and column as separate strings of text. Thus if I put a value of 10 in cell F8, and in a new cell put the formula =INDIRECT("J"&$F$8), Excel evaluates this expression as J10, and displays a value of 50. If I enter into cell E9 the letter J, I can use the formula =INDIRECT($E$9&$F$8), this also evaluates to J10 and displays the value of 50. More tricky than this, if in cell D12 we enter a value of E9, the formula =INDIRECT($D$12)returns a value of J; this is because E9 is the named range of cell E9. In cell F8 we can enter a value of 10. Now the formula =INDIRECT($D$12)&F8 returns a value of J10. Why not 50? Because the INDIRECT function only makes one "jump" from the directly referenced value to the indirectly referenced value. To get the value of J10 to display this way, we have to nest: =INDIRECT(INDIRECT($D$12)&F8) now returns a value of 50.

    gio
    gio

    Like the idea of a summary front sheet but I would rather use Sdavidson@ or Ian.Burns@ technique. And thanks to David.Hanshumaker@ for his tip on the indirect() function

    solson
    solson

    Why not just use a filter tab at the top of each column.

    sdavidson
    sdavidson

    Why not instead simply enter these values on the first sheet in your workbook?: B2: =Inventory!$A$18 C2: =Inventory!$E$18 B3: =Inventory!$A$20 C3: =Inventory!$E$20 B4: =Inventory!$A$19 C4: =Inventory!$E$19

    haiau54
    haiau54

    It should be E18, E19, E20?

    pgurney
    pgurney

    What is the advantage of using the Indirect function versus just creating a cell reference to the original data cell? Seems easier to me to just reference the cell where the data is located. For those of you having trouble with the indirect function, using this information and the help information in Excel for Indirect, I was finally able to figure out what the formulas are doing. You are basically defining a location (either through a cell address or a named location) for the data. (I believe the earlier posts re: the typos are correct.)

    ian.burns2
    ian.burns2

    What's the benefit of Indirect instead of using =named range?. If you delete a sheet and copy a new one in could this be used to fix the links?

    r-knight2
    r-knight2

    I'm sorry, but this example is meaningless to me. I have tried to reproduce it and the explanation is so poor its impossible to follow.

    david.hanshumaker
    david.hanshumaker

    Values in this example could just as easily be displayed with the min, max, and sum functions. A real need for the indirect function comes when you have a block of data of varying size. Then a count function can be nested inside the indirect function to create an address. For example, INDIRECT("E"&count(E2:E5000)+1) will show the last value in column E assuming one header row, and less than 5000 records.

    corysak
    corysak

    In Fig A it is saying to type E9, E10 etc. shouldn't it be E18, E19 etc.?

    pstarr
    pstarr

    Thanks for this tip. I think some of the cell refernces may be incorrect. In the sample, E9 should be E19, E8 should read E18 and E10 should read E20.

    gerard.de.graan
    gerard.de.graan

    I use indirect in a sheet for my bills, to refer to the task names in the sheet of the project (every project has its own sheet): =IF(INDIRECT(L3)="";"";INDIRECT(L3)) etc In cel L3...L10 I have =CONCATENATE($I$2;$L$1;TEXT($J2;0)) to point to the cell containing the description, e.g. '107316'.M7 for the task in cell M7 of the sheet 107316. I2 contains =CONCATENATE("'";TEXT(I1;0);"'.") I1 contains 107316. Use ! in Excel, dot in OpenOffice. L1 contains M, J2 contains 7.

    RRB
    RRB

    The example has typos and the example is not descriptive of the INDIRECT function. Furthermore, the names are defined for the workbook, which makes unnecessary to include the name of the sheet in the INDIRECT function. An easier way to obtain the desired result would be to name E6:E16 as Cost, and use =Sum(Cost), =Max(Cost) and =Min(Cost)in the new sheet.

    vjanecky
    vjanecky

    Why not just use ="named range"? Same result without the hassle of writting the formula.

    tleblanc
    tleblanc

    I believe E8 should be E18, E9 & E10 are correct, but steps 6 & 7 are transposed.

    EddieS
    EddieS

    It appears that: E8 in the instructions should be E18 E9 should be E19 and E10 should be E20.

    ppg
    ppg

    I agree with the majority of posters that the example as currently set up does not require use of the INDIRECT() function. However a small change will make it useful. If you have a series of worksheets, each named for the room, e.g. "Huron Room", "Superior Room" etc. and a summary sheet. If you put the name of the room in $A$1 and INDIRECT(A1&"D18") in cell $C$2, INDIRECT(A1&"D19") in cell $C$3 and, INDIRECT(A1&"D20") in cell $C$4 you get the summary for the room and only have to change the room name in $A$1 fo get the summary for a different room. Note that it is not a good idea to put the totals at the bottom of a list in a cell such as $D$18 if you are planning to combine them in a summary sheet since they are likey to end up in different places for different rooms.

    dlovep
    dlovep

    Can you tell me the really function for this INDIRECT function apply to, since I have read thru those posts, but still I cant imagine where to apply this function, if things are that indirect, why would you want to refer something partially....

    CharlieSpencer
    CharlieSpencer

    why bother with named ranges? Instead just reference the cells directly: =WorksheetName!E18 =WorksheetName!E19 =WorksheetName!E20

    seanferd
    seanferd

    That's exactly how I do it. Good to know I'm not doing something non-standard. ;)

    madwhitehatter
    madwhitehatter

    I used this function and IF to roll up attack data in the desert and make a dashboards for the front of my spreadsheets. There is no reason to go the long way.

    Editor's Picks