Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Combo box filter


(!)

Preston1's Avatar
Preston1 Preston1 is offline
Junior Member with 25 posts.
THREAD STARTER
 
Join Date: Dec 2006
07-Aug-2008, 07:57 AM #1
Excel Combo box filter
Hello!

I hope somebody is able to help me...


I have an excel spreadsheet that is using combo boxes on the page.

What i would like to do is have one combo box selection change another combo boxes choices.
I assume this is possible, I have tried several times and have been unable to get the second combo box contents to change.


I have attached an example spreadsheet of what i need to do (apologies as I dont have the actual unit here on this pc).

The scenario is if a user selects Test 5 from the first combo box, the second combo box content (list options) should change to different content such as colours instead of days of the week and so on.

I have tried to do this with a formula, which i have been unable to do...I have also tried VBA coding, but I'm afraid my VBA isnt that great..

So if anyone could help I would apreciate it.


Thanks in advance.
Attached Files
File Type: xls Test.xls (14.0 KB, 863 views)
Aj_old's Avatar
Aj_old Aj_old is offline
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
07-Aug-2008, 08:26 AM #2
Is this fine for you:
Attached Files
File Type: xls Test.xls (19.0 KB, 2226 views)
Preston1's Avatar
Preston1 Preston1 is offline
Junior Member with 25 posts.
THREAD STARTER
 
Join Date: Dec 2006
07-Aug-2008, 02:00 PM #3
AJ

Yes thanks that is perfect.

Just a couple of questions...

What exactly have you had to do to make this work?
I have looked at the formula but i am un certain still how it works...could you break it done for me how it works, as what i sent was an example and i need to add it to different worksheet


Also when you change the first combo box, it changes the second ones content, but if the second one has a pre selection in it i.e. Blue, and i choose a different option in combo one the blue entry is still present until i select the combo box again..is there any way of clearing it...or selecting the first choice of the new entries.


Besides that, its all good thank you!!
Thank you very much!
Aj_old's Avatar
Aj_old Aj_old is offline
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
08-Aug-2008, 12:27 AM #4
I named ranges on your worksheet, so the could be identified by names! After that next to each value in the range that goes to combobox 1, I just typed the name of the range that should be on the second combobox2, when this value is selected in first combo.


On the second combo, via Data\Validation I put this formula:
Code:
=INDIRECT(VLOOKUP($F$13,$B$7:$C$15,2,FALSE))
Vlookup part will look for the cell on the right of the range of the first combo, and the Indirect part will return the range that has the name specified in the cell returned by the lookup part!

As for the second part, it can be done, but I think for this is needed a macro! If it's alright for you it ti be done by a macro then I can do it for you ( it's a very simple one)!

Last edited by Aj_old; 08-Aug-2008 at 12:36 AM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2