Directly asking about Indirect func.

[Hari]

Hi,

I have come across the indirect function one or twice in NG's. I read one
time about somebody saying that this function has been given a very normal
description in the help file and its actual potentialities are much beyond
that

If possible please give me a link/lead to some website(s) in which the uses
of this Indirect function would be discussed thoroughly (with some neat
examples if possible), so that I could learn and put to use wherever
relevant.

Regards,
Hari
India

 

[BrianB]

The principle of the INDIRECT() function is quite simple. It converts
text string to a formula.
Take a reference formula such as :-
Code
-------------------
=[Book1.xls]Sheet1!$A$1
-------------------

This could also be written as :-

Code
-------------------
=INDIRECT("[Book1.xls]Sheet1!$A$1")
-------------------


Not a lot of difference so far. However, the real power of thi
function comes from our ability to make up the string argument any wa
we wish.

Say we have a list of Workbook names in column A starting at A1 eac
with a sheet called "Sheet1" and we want to collect the contents o
cell A1 from each.

In cell A1 is "Book1". First we need to make up a text string tha
looks like the top one above. To check this, In B2 put
Code
-------------------
="["&A1&".xls]Sheet1!$A$1
-------------------
or
Code
-------------------
=CONCATENATE("[",A3,".xls]Sheet1!$A$1"
-------------------


To finish this off we now use INDIRECT() to convert the string to
reference.

Code
-------------------
=INDIRECT(CONCATENATE("[",A1,".xls]Sheet1!$A$1")
-------------------


This can be copied down to pick up the workbook name from cells A2,A
.. etc.

Hopefully you can now see how the string can be broken down stil
further to cope with differences in worksheet name and cell references

 

[Hari]

Hi Brian,

Thanx a lot.

Im much better placed now as compared to before.

Regards,
Hari

India