use of ADDRESS function within CELL function
 

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

use of ADDRESS function within CELL function
 

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

use of ADDRESS function within CELL function
 

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

use of ADDRESS function within CELL function
 

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

--
Biff
Microsoft Excel MVP


"drummo2a" wrote in message
...
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

use of ADDRESS function within CELL function
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"drummo2a" wrote:

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