ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

1 Q & A posts tagged with DataValidation

Mon
Apr 12
2010

How do I remove blank (but not necessarily empty) cells from a drop down menu in excel?

I want to have a row of drop down lists. In each drop down I want all the options to exist EXCEPT any that have already been selected. This is my primary problem. The closest I can get is to remove value from the drop down range by an IF function in the source data, so that the list updates based on what has already been selected. This requires extensive functions and also leaves blanks in my drop down menus. I'm keen to have the drop downs only contain the remaining options and no blank gaps. Are there any solutions to either the primary problem, or, failing that, a solution to remove the blank (but not empty) cells from my drop down menus?

Cheers :)


Answer

You have very interesting request

Check out this file
http://www.File1.net/Links/NewList.xls

It has new list populated every time user selects an item, the new list will have the same as old list without the newly selected one, it was done for tutoring purposes

It should do what you are looking for, you might need some tweaks though
That was done using the following Excel features:
Data Validation
OFFSET function
IF function
Name

Let me know if it helped or not

VBAXLMan

Monday 4/12/2010 2:35:47 AM

Tags

History