Indirect function result in #REF

[Julie L]

Hi,

I tried to use the indirect function (i.e. indirect(A3)) where A3 contains a
number (say 3), but the result came back as #REF. Any idea what caused the
problem.

Thanks,
Jules

 

[Max]

Try it as: =INDIRECT("A3")

 

[Dave Peterson]

=Indirect() expects something that looks like a range address/range name in that
cell.

So if A3 contained X99, then
=indirect(a3)
would return the value contained in X99.

 

[Julie L]

Thank you Dave, I now understand what created the problem. Reference in
indirect should refer to an address, therefore, if a3 contains 3, the result
would be too volatile since excel is trying to pick up all information from
row 3.

Thanks for the info!

Jules

 

[Dave Peterson]

And "3" isn't a valid range address, either.

But if you had '3:3 (make sure it's text and not a time!) in A1, then you could
use:

=sum(indirect(a1))
to add up all the values in row 3.

 

[Julie L]

Thanks for the insightful explanation!

Jules

And "3" isn't a valid range address, either.

But if you had '3:3 (make sure it's text and not a time!) in A1, then you could
use:

=sum(indirect(a1))
to add up all the values in row 3.

 

[Julie L]

Thanks for the information Max!

Try it as: =INDIRECT("A3")

 

[Max]

welcome, Julie. Thanks for the liner