Dynamic Named Ranges

S

SJT

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

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
 
S

SimonCC

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
 
S

SJT

Thank you for your assistance.

Peo Sjoblom said:
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
Click to expand...
 
Top