What purpose is the INDIRECT function?

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Thanks

Surely if I want cell value of A3 to appear in cell B4 I could enter
"=A3" in cell B4?
 
The INDIRECT function allows you to build up a cell or range reference
as a string and then to pass this into a function where it will be
evaluated as if you had typed the reference directly. In the simple
example you quoted you would not need to use INDIRECT, but suppose you
wanted the value from a range of different cells to appear in B4,
depending on the value (1 to 5) in B3. You could do that like this:

=INDIRECT("A"&B3)

So, if B3 contained 1, then the contents of A1 would appear in B4, but
if B3 contained 4, then the contents of A4 would appear in B4. The
range reference can also encompass sheet names, so this formula in B4:

=INDIRECT("'"&B2&"'!A"&B3)

where B2 contains a sheet name like "Sheet2" and B3 contains a number
eg 3, would return the value from the cell A3 of Sheet2.

INDIRECT does not work with closed workbooks, and it is a volatile
function.

Hope this helps.

Pete
 
The INDIRECT function allows you to build up a cell or range reference as a
string and then to pass this into a function where it will be evaluated as
if you had typed the reference directly. In the simple example you quoted
you would not need to use INDIRECT, but suppose you wanted the value from a
range of different cells to appear in B4, depending on the value (1 to 5) in
B3. You could do that like this:

=INDIRECT("A"&B3)

So, if B3 contained 1, then the contents of A1 would appear in B4, but if B3
contained 4, then the contents of A4 would appear in B4. The range reference
can also encompass sheet names, so this formula in B4:

=INDIRECT("'"&B2&"'!A"&B3)

where B2 contains a sheet name like "Sheet2" and B3 contains a number e.g.
3, would return the value from the cell A3 of Sheet2.

INDIRECT does not work with closed workbooks, and it is a volatile function.

Hope this helps.

Pete
 
Sorry about the double post - Google Groups is acting up again and
didn't seem to accept the post, so I copied it to OE and posted
through there, only to find that it had eventually gone through first
time.

Pete
 
Another thing I like about INDIRECT is that it doesn't change the cell
reference. So if you have, for example, a data tab that you are constantly
adding or deleting data from, the =INDIRECT("A3") will always refer to A3 and
not get bumped up (to the point of a REF error, or down.
 
Another thing I like about INDIRECT is that it doesn't change the cell
reference. So if you have, for example, a data tab that you are constantly
adding or deleting data from, the =INDIRECT("A3") will always refer to A3 and
not get bumped up (to the point of a REF error, or down.
--
John C







- Show quoted text -
Click to expand...

HI John yes I know what you been. It locks it in which is useful if
you have an "Add line" macro say to get the latest 10 results.
 
Sorry about the double post - Google Groups is acting up again and
didn't seem to accept the post, so I copied it to OE and posted
through there, only to find that it had eventually gone through first
time.

Pete






- Show quoted text -
Click to expand...

Hi Pete (also from UK!)

Appreciate your reply I too have summarised myself in a similar
range. Within VB I use Ranges As Range And with Paths to help define
things.

"A"&B1&":A"&C1
If B1 contains 5 and C1 contains 10, this evaluates to the string
"A5:A10". The INDIRECT function converts this string to an actual
range reference, which is passed to the SUM function.
For VBA formulas to return relative sheet names (e.g., the name
It means you can vary the rows you wish to count in cells B1 and C1.
 
Thanks for the feedback, Simon.

Presumably, then, you asked the question rhetorically to promote
debate, as you seem to know what it can be used for !! <bg>

Pete
 
Back
Top