use of ADDRESS function within CELL function

  • Thread starter Thread starter drummo2a
  • Start date Start date
D

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
 
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
 
Tom,

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

drummo2a
 
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).
 
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.
 
Back
Top