use of ADDRESS function within CELL function


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
 
Ad

Advertisements

T

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
 
D

drummo2a

Tom,

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

drummo2a
 
T

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).
 
Ad

Advertisements

S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top