Excel 2003: Making a Drop Down List from a Range of Discontinuous Cells

[helpmeinexcel]

Hello,

I am trying to create a drop down list (validation or combobox; which ever is easier) made up of a range of discontinuous cells.
I wrote a function, called VisibleModels, that selects which cells I want in the drop down list:

Please Login or Register  to view this content.

This function works perfectly.

I am unsure how to go about getting this result into a drop down list. I am using excel 2003.
I think I either need to code this into a combobox (somehow..) or make the result show up, continuously, in a column (My Worksheet is "Models" and I have Column "A" empty) so that there are no formulated blanks in between each item so that data validation will work.
I spent several days and countless hours looking around other sites and I need some help...

[AlphaFrog]

You're on the right track. There are different ways this could be done depending on what you want.

If you want to use a Data Validation (DV) list, this is an example of building a comma delimited string and applying it to a DV List in cell D1.

Please Login or Register  to view this content.

[helpmeinexcel]

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

[helpmeinexcel]

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

[AlphaFrog]

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

You're welcome.

How do A10 or C7 change; by formula or by user input?

If it's by user input...

• Right-click on the Sheet tab
• Select View Code from the pop up context menu
• Paste the code below in the worksheet's code module

The function VisibleModels can go in the worksheet's code module or it can stay in a standard code mode e.g. Module1.

Please Login or Register  to view this content.

[helpmeinexcel]

A10 and C7 are both data validation lists that change what the VisibleModels function produces; they get changed by user input

This works perfectly now! Thanks so much!