Filtered content in the drop-down list of Combobox

Hi,

I am desperate. I need to address the following challenge and don’t grogress any further. I am asking for your kind help.

Challenge: Say, there are 5 comboboxes; each combobox should only list a selected range of values, based on the previously made combobox selection.

Example Illustration:

Lookup Table:
see attachment screenshot



User Interface / User Action:
1st combobox (related to Column1): User chooses AAA from the drop-down list
2nd combobox (related to Column2): The drop-down list shall only offer 123 and XYZ (one time displayed only!), nothing else
2nd combobox: User selects XYZ from the drop-down list
3rd combobox (related to Column3): The drop-down list shall only offer 789 and 777, nothing else
4th combobox, …..
And so on …


Any way I can do that?

Kind regards,

Sascha Graef

Dear Teylyn,

first of all, thanks a big bunch for bringing me on the right track!!
I am almost there!
Now, I have another final challenge that made me crazy the last couple of days, that's why I did not respond earlier. Can you help me on that detail? Please have a look at the attached XLS file:

CAHELLENGE 1:
In the first sheet, there is a "Customer" cell, which can be populated with ethe validation list that refers to the corresponding range of the second sheet.
QUESTION: How do I use the OFFSET function properly to make sure it will be a dynamically growing list, depending on the entries in the light-yellow comun of the second sheet? And how to eliminate the first row header in the validation list?

CHALLENGE 2:
In the second sheet, you can see those grey fields that are pre-defined with CUST001, CUST002, ..., CUST100. These should be like static placeholders or tags, to avoid that the user needs to again and again define the column header of the respective dependent lists.
Now, the user can key in the "Customer Name" in the light-yellow column.

In the first sheet, if the user chooses customer "BBB", then the next cell "Process" should only offer the range of "CUST0002", because "BBB" is related to "CUST002" (see red marked areas).

Can you help me with the sample code for this field?
Thank you very very much in advance!

Kind regards,
Sascha


-----------------------------------------------------

Originally Posted by teylyn

Hello,

you can do that with normal data validation, no need for combo boxes. It is called "dependent data validation". You will need to do some prep work to arrange your data and create the range names, but it can be done. See here for a tutorial: http://www.contextures.com/xlDataVal02.html

Hello Teylyn,

great! Thank you 100 times!
You really helped me getting the entire thing done.
Never mind the dynamic range. I simply extended the column range to 200 rows down, which is way more than enough that I ever need.

Again, a big THANK YOU!

Regards,
Sascha