MrExcel Publishing
Your One Stop for Excel Tips & Solutions

INDIRECT function: example to illustrate this function


Posted by Kristel on January 07, 2002 1:54 AM

i'd like to explain this function to my students but i can 't find a real good (and rather simple) example. Maybe some of you do have one?
Thanks for writing it down for me!


Posted by Aladin Akyurek on January 07, 2002 2:55 AM

Hi Kristel --

Example 1.

In A1 enter: E1
In E1 enter: 60
In A2 enter: =INDIRECT(A1)

Now, name cell A1 MaxValue via the Name Box on the Formula Bar.

In A3 enter: =INDIRECT(MaxValue)

Rather trivial, but it shows how it "works."

Example 2.

In E2 enter: 100

In A4 enter: =INDIRECT(ADDRESS(2,5))

provided that your students know how ADDRESS works.

Example 3. (Non-trivial)

Define three tables: CARS and Renault and BMW.

CARS is a 1-column table/list, consisting of just 2 entries:

{"Renault","BMW"}

Now create a 2-column table whose 1st column houses some Renault-models and 2nd column their prices.

Name the 1st column of this table "Renault" via the Name Box.
Name the whole table RenaultPRICES.

Now create a 2-column table whose 1st column houses some BMW-models and 2nd column their prices.

Name the 1st column of this table "BMW" via the Name Box.
Name the whole table BMWPRICES.

In A6 create a drop-down list via Data|Validation using List and CARS as Source [ =CARS ]

In B6 create another drop-down list via Data|Validation, using List and

=INDIRECT(A6)

as Source formula.

In C6 enter: =VLOOKUP(B6,INDIRECT(A6&"PRICES"),2,0)

The last example presupposes that your studs know a lot.

Hope this helps.

Aladin

Posted by KRISTEL on January 07, 2002 3:15 AM

Thanks a lot but...(i'm very demanding today...)what I really am looking for is a real-life example to show that this is a really interesting function to use in every day life. My students are of the kind that always ask why and when can WE use this and thsi time I can 't find a good example myself...

Posted by Aladin Akyurek on January 07, 2002 3:44 AM

Isn't Example 3 a real-life example? You could also adapt this example such that you can retrieve prices of products and compare them, for example, "Supermarkten" vs "Winkeliers".

Aladin

Posted by Kristel on January 07, 2002 4:41 AM

I'll try to work it out...
groetjes en tot volgende keer!
Kristel