What purpose is the INDIRECT function?
 

Thanks

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

What purpose is the INDIRECT function?
 

Hi,

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

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

Mike

"Simon" wrote:

Thanks

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

What purpose is the INDIRECT function?
 

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

On Aug 1, 11:16*am, Simon wrote:
Thanks

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

What purpose is the INDIRECT function?
 

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

"Simon" wrote in message
...
Thanks

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

What purpose is the INDIRECT function?
 

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

On Aug 1, 11:41*am, "Pete_UK" wrote:
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

"Simon" wrote in message

...



Thanks

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

- Show quoted text -