Data Validation question

[ozhunter]

Hello

I have 7 Data validation lists, each list gets it's content from a seperate
range (list 1 gets it's data from b2:b156, list 2 get's it data from B2:B156,
list 3 gets it's data from C2:C156 so on through to list 7. All the ranges
get their data from the range ti it's left via a formula

the formula does the following.

If I select 047 from a list, the formula replaces 047 with "" in the
remaining lists, effectivley removing it from the other lists so it cannot be
used again.

Does anyone have a macro or programming sugestion that will allow me to move
the cells that return "" to the bottom of the list because it will eventually
have 274 lists each from it's own range and when you get down about 8 or 9
you have to scroll throught he list to get to the next available selection.

Hope I am clear enough.

Thanks

 

[Pete_UK]

Have a look at this page on Debra Dalgleish's site:

http://www.contextures.com/xlDataVal03.html

It seems to cover what you want to achieve.

Hope this helps.

Pete

 

[ozhunter]

Yup

That worked a treat and will save me having to have 274 seperate lists.

Thankyou

 

[Pete_UK]

Glad to be able to help.

Pete