I am using the OFFSET formula to create a dynamic named range that is several
rows long and several columns wide. How would I create a cell reference in
another spreadsheet that dynamically refers to the last cell of that range.
Thank you in advance for your assitance.

Assuming it's the same column

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

since you just want the last value there is no need to refer to the offset
formula,
replace A with the column in question

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"SJT" wrote in message
...
I am using the OFFSET formula to create a dynamic named range that is
several
rows long and several columns wide. How would I create a cell reference
in
another spreadsheet that dynamically refers to the last cell of that
range.
Thank you in advance for your assitance.

If your formula for the dynamic named range was:
=OFFSET(reference,rows,cols,height,width)

use pretty much the same formula with slight variation to get last cell:
=OFFSET(reference,rows+height-1,cols+width-1,1,1)

-Simon

"SJT" wrote:

I am using the OFFSET formula to create a dynamic named range that is several
rows long and several columns wide. How would I create a cell reference in
another spreadsheet that dynamically refers to the last cell of that range.
Thank you in advance for your assitance.

Try

=INDEX(aaa,ROWS(aaa),COLUMNS(aaa))

where aaa is the range name.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=550476

Thank you for your assistance.

"Peo Sjoblom" wrote:

Assuming it's the same column

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

since you just want the last value there is no need to refer to the offset
formula,
replace A with the column in question

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"SJT" wrote in message
...
I am using the OFFSET formula to create a dynamic named range that is
several
rows long and several columns wide. How would I create a cell reference
in
another spreadsheet that dynamically refers to the last cell of that
range.
Thank you in advance for your assitance.