Dynamic Named Ranges

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[mrice]

Try

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

where aaa is the range name.

 

[Guest]

Thank you for your assistance.

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