How to use Data validation List for multiple Lists

**bdouglas1011** · 09-11-2014, 04:42 PM

I have a worksheet that we have a huge list which we use data validation on
I would like to be able to have a cell at the top of the worksheet that has a drop down with five choices in it.

If the user chooses say Directional I would like a list to be visible in range b3:b46 with the choices for that Directional dept..
If the user chooses say MWD I would like a list to be visible in the same range b3:b46 with the choices for that MWD dept..
Other Choices would be Gyro, Wire line and some others.

Can you get the data validation to populate based upon the choice they make in say cell a1 (which has the list (Directional, MWD, GYRO,.....))

Is this possible

Right now the list I use has about 350 rows in it but they are not all relative to the job at hand that is why I would like to be able and narrow down the choices by having a separate choice that would dictate what list gets shown.

Any Help would be great

**PeteABC123** · 09-11-2014, 05:05 PM

Contextures instructs you here http://www.contextures.com/xlDataVal13.html how to create Dependent Dropdown Lists. It should work for your project.

Pete

**newdoverman** · 09-11-2014, 05:36 PM

This is how I interpreted your requirements.
There is a Data Validation list in A1 from which you make a choice. The choice in A1 will draw values from the Named range on Sheet2 and fill in column B with the values.

**bdouglas1011** · 09-11-2014, 05:51 PM

I see what you have started and let me help clarify a little better.

In A:1 you will have your choice of service (Directional, MWD, Gyro)
Depending on your choice of service your data validation List would filter those results depending on your choice of service. It wont populate the cells automatically but you would have your drop down list filtered to your choice of service.

I am using the data validation list right now which is in cells z:1 thru z:300

You could divided them into 100 per each group so right now you would click on your data validation list which would show all 300 items and you have scroll through all 300 items to get one item.... then you move to next line and do it all over again

I would like if you choose the service MWD then it would only show the range 100-200 of the list to help narrow down your choices.

On my sheet I have my drop down list in cells b3:b46 (which includes the entire unfiltered set right now.)

Maybe this will help explain it better

**newdoverman** · 09-11-2014, 06:14 PM

Maybe this will be better for you.

**bdouglas1011** · 09-11-2014, 06:37 PM

Looks more like I was wanting I will try to set it up later tonight or in the morn....Will it be hard to duplicate for me?

**newdoverman** · 09-11-2014, 06:52 PM

It isn't difficult to duplicate. If your data is set up in the same columns, you can swap in your values in the Z column then in the small table, enter the names that you want to use. Do this in a copy of the file that I uploaded and not the only copy that you have so that if you make a mistake, you can at least start again fresh.

It is easy if you use single word titles otherwise a vlookup would have to be used to convert the word titles to "legal" names. In the Name Manager, to change the definitions of the names, select a name and click edit. There you can change the cell ranges for the names.

Make sure that the names that you enter in the small table are the EXACT names that you use for the Named Ranges. This is what make everything work.

When it comes to make the series of drop down lists in column B, select all the cells that you want to have a Data Validation list and then create the Data Validation (all at one time). If you have to adjust the range of cells that have the Data Validation, click in the last cell and use the Fill Handle to drag further down the column just like you would do to copy a formula.

Good Luck.

How to use Data validation List for multiple Lists

LinkBack

Thread Tools

Rate This Thread

Display

How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Re: How to use Data validation List for multiple Lists

Thread Information

Users Browsing this Thread

Similar Threads

Data Validation lists - entering value not in list

Multiple Data Validation Lists based on a master list, but same info cannot be selected

Data validation with multiple lists

How to use data validation for Mutiple lists dependant on one list

Data Validation Lists - Creating lists based on data from another list

Bookmarks

Bookmarks

Posting Permissions