INDIRECT function: example to illustrate this function

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

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...

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

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