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