Define Named Ranges

Define Named Ranges

Post by Joe Giede » Sun, 22 Feb 2004 04:41:24


I created several Named Ranges in a spreadsheet which I
use for all my estimating, somtimes my spreadsheet can
have 100 rows and sometimes it can have 5000 or more, I
continually add rows so I don't know from the start how
many there will end up being. The question I have is, is
there a way by using named ranges Excel can detemine the
last row of data so I don't have to enter "=Suppliers!
$BG$3:$BG$5000" for a spreadsheet that contains only a
few hundred rows or have to keep track and redefine the
range if it goes over 5000? If I did define them upto
10,000 to cover all possiblities will Excel always look
through 10,000 and therefore making my calculations take
so long I can go on break every time I calculate things.

Thank you for your help and answers.
Joe
 
 
 

Define Named Ranges

Post by Frank Kabe » Sun, 22 Feb 2004 05:01:06

Hi
if there are no blanks in between you can use the following formula for
your name definition
=OFFSET('Suppliers'!$BG$3:$BG$3,0,0,COUNTA($BG$3:$BG$10000))