blank entries in data validation list
 

Hello. I have a data validation list based on a range on another worksheet
named "parts". The range is from B2:B2000. I only currently have about 150
parts in trhe list, but I want to leave the range larger so that when I add
new parts in, they will appear in the data validation I have in the cells of
the other sheet for part selection. How can I keep the data validation list
from showing all of the blank cells in the range?

Thank you!

Mike

blank entries in data validation list
 

Hi

Use a dynamic named range, which adjusts automatically whenever list entries
are added/removed. As example (it assumes there never will be gaps in list,
the list is placed in column A of sheet MyList, and MyList!A1 contains a
header):
=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)

Arvi Laanemets



"WiFiMike2006" wrote in message
...
Hello. I have a data validation list based on a range on another worksheet
named "parts". The range is from B2:B2000. I only currently have about 150
parts in trhe list, but I want to leave the range larger so that when I
add
new parts in, they will appear in the data validation I have in the cells
of
the other sheet for part selection. How can I keep the data validation
list
from showing all of the blank cells in the range?

Thank you!

Mike

blank entries in data validation list
 

Use a dynamic named range:

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

Biff

"WiFiMike2006" wrote in message
...
Hello. I have a data validation list based on a range on another worksheet
named "parts". The range is from B2:B2000. I only currently have about 150
parts in trhe list, but I want to leave the range larger so that when I
add
new parts in, they will appear in the data validation I have in the cells
of
the other sheet for part selection. How can I keep the data validation
list
from showing all of the blank cells in the range?

Thank you!

Mike