=INDIRECT(B1)

will not work unless B1 contains a cell reference. Since B1 contains a
formula, you have to use the cell address (a string):

=INDIRECT("B1")

--

Vasant

"Rob Hick" wrote in message
oups.com...
ok. tried putting the SUM(ROW()) in but still didn't fix the problem -
using INDIRECT(...) still returns #REF!

i've tried to boil the problem down to see what's happening and i have
a feeling i might be misunderstanding the INDIRECT function. As i
understand it the following should work:

A B
1 cat INDEX(A1:A2,2,0)
2 dog =INDIRECT(B1)

so that in cell B2, the value 'dog' is returned but it seems to return
#REF!