use of ADDRESS function within CELL function

[drummo2a]

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

 

[Tom Hutchins]

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]

Tom,

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

drummo2a

 

[T. Valko]

Maybe something like this:

=INDEX(Sheet1!1:100,B7,B6)

Just change the size of the indexed range to suit. In the above the range is
Sheet1 A1:IV100 (in Excel versions prior to Excel 2007).

 

[Shane Devenshire]

Hi,

Regarding your other question - why? You actually answered your own
question - the address function returns a "text" address, but the cell
function needs a refererence not a text address.