What purpose is the INDIRECT function?

[Simon]

Thanks

Surely if I want cell value of A3 to appear in cell B4 I could enter
"=A3" in cell B4?

 

[Mike H]

Hi,

This explains it in much more detail than could be gone into here.

http://www.contextures.com/xlFunctions05.html

Mike

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[John C]

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.

 

[Simon]

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 -

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.

 

[Simon]

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 -

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.

 

[Pete_UK]

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