Tom,

Thanks this works. I still don't understand why the CELL function doesn't
work

drummo2a

"Tom Hutchins" wrote:

Instead of CELL("contents" you could just use the Indirect function with your
Address function to return the contents of the referenced cell:

=INDIRECT(ADDRESS(B7,$B$6,2,1,"Sheet1"))

Hope this helps,

Hutch

"drummo2a" wrote:

I am trying to return a value from Sheet1 to a formula on Sheet2
I need to change the referenced cell on Sheet1 on the fly
I can build the reference using the ADDRESS function
but am not able to incorporate that information within
a CELL function



on Sheet2 I have the following

cell B6=1 column
cell B7=2 row


ADDRESS(B7,$B$6,2,1,"Sheet1") this formula returns the text Sheet1!A2, as it
should

When I combine this with the CELL function

CELL("contents", ADDRESS(B7,$B$6,2,1,"Sheet1"))

I get a message that my formula contatins an error

If I just type in the result from the ADDRESS function into the CELL
function then the formula works

Why can't I use the ADDRESS function within the CELL function?


Thanks