Validating ranges

[slamanager]

Hi,

I have a data sheet that has 20 columns of data 10 of these are named ranges.

I can do a count to know how many rows of data on this data tab but the named rnages need to be dynamic so when i paste new data into the data sheet they expand to cover the data. I provisionally set the ranges at 20000 rows but the extra spaces cause formula issues.

Q1 is there a way of dynamically changing ranges to fit
Q2 showing what a range covers

 

[riclop]

In Excel 2003 highlight the data in a column and hit cntrl + l to create a list. Then name this selection. In Excel 2007 the shortcut is cntrl + t.

 

[T. Valko]

Hi,

I have a data sheet that has 20 columns of data 10 of these are named ranges.

I can do a count to know how many rows of data on this data tab but the named rnages need to be dynamic so when i paste new data into the data sheet they expand to cover the data. I provisionally set the ranges at 20000 rows but the extra spaces cause formula issues.

Q1 is there a way of dynamically changing ranges to fit
Q2 showing what a range covers

See if this helps:

http://contextures.com/xlNames01.html#Dynamic

 

[grizz]

lets say name range from $B$1:$B$100
change your name range ---- were it says refers to

=OFFSET($B$1,0,0,COUNTA($B:$B),1)