ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

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

2 Q & A posts tagged with dropdown

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
Fri
Apr 2
2010

Can you insert a drop-down or subcategory into an Excel text filter list?

I have a vocab spreadsheet that filters terms by their assigned Category, but there are a great many categories and I'd prefer to make many of them subcategories within the Category filter (essentially, a drop-down within a drop-down, but within a text filter list), rather than create a separate column from which to filter the subcategories, but so far it does not seem possible!


Answer

Using AutoFilter, the only approach I see is what you already did, which is adding another column with the subcategories

However, you can still use some trick to make it in one column by having certain mask in the categories name
So if you have Cat1, Cat2, Cat3
And Cat10 is sub of Cat1, Cat20 is sub of Cat2, etc
Then do this in the category column
Cat1 - Cat2
Meaning combining the two categories into one cell, the do sort and you can now easily visually see the category/subcategory in one column

Does that make sense?

let me know if not

VBAXLMan

Friday 4/2/2010 2:59:51 PM

Tags

History