Using VLOOKUP in drop-down list

[concretetsunami]

Okay I guess I don't understand how VLOOKUP works. Attached is the file I am working on. I want to be able to select a equipment cat. from D2 and then, depending on what I choose in D2, it will give you the options to choose from in E2.

ex:
if I choose "VLV" in D2, I want the options in E2 to have the list that is found in Column V. If I choose "CON" in D2, then I want the options in E2 to have the list that is found in Column AB. I went to http://www.contextures.com/xlDataVal02.html and tried what they said, but it is not working for me. Any suggestions? I would really like the list that is from columns U-AB to be on page 2.

[ChemistB]

Hi Concrete,

You don't need to use the VLOOKUP function for dependent dropdowns. I modified the validation formula in Column E to be

Please Login or Register  to view this content.

You also had named column V as VLVs whereas your list under "Equipment" refers to it as VLV. (I fixed it). Take a look and let us know if this is what you want.

ChemistB

[concretetsunami]

It worked perfectly. Can you explain what it was that I needed and how to do it so I don't have to keep asking and i can start learning?

[ChemistB]

The way I understand it;

Normally when you reference a cell in a Validation list, it uses what's in that cell to set it's validation. For example, if you referenced A1 and A1 had VLV in it, then it would only allow entry of "VLV". In your case, you want to put a named range (or a list) into cell A1. The INDIRECT function, in this type of situation recognizes that and uses the named range rather than what is directly in the cell.

How you did this;
First (in D2) you need to name a range that contains all of the names of your other lists. You called this Equipment. For the validation list, you reference this list directly (=equipment). Then in E2, you use the INDIRECT function to get Excel to realize that what you want from D2 a named range. Then the Validation function goes to that named range and has a dropdown list of what is in that specific named range.

One few more points. Named ranges cannot have spaces. You can work around this by using the _ or by using the SUBSTITUTE function.

If you want to add to a list/named range, you'll need to modify the range (e.g. from K2:K5 to K2:K6) which can be done in Insert>Name>Define. If you want to add a new list, you'll need to add the defined range to your worksheet and add it to your "Equipment" range.

Did this help?

ChemistB

[concretetsunami]

Okay the other day when this was explained, I kinda understood it, but now I am lost again. I need a walk through again. ex:
A1-(drop list) "-,G,D,L"
the info in the drop list for A1 is in column AA titled "ycor"
in B1 i want the drop list to pick from column AB,AC,or AD depending on what was choosen from A1
AB-G
AC-D
AD-L

Can someone please walk me through this again
ChemistB helped me earlier but I can't recall all the steps.

[ChemistB]

A1-(drop list) "-,G,D,L"
the info in the drop list for A1 is in column AA titled "ycor"
in B1 i want the drop list to pick from column AB,AC,or AD depending on what was choosen from A1
AB-G
AC-D
AD-L

Sounds like you are almost there. Did you name the lists in AB, AC, AD, "G", "D", and "L"? If so, then all you need to do is for the validation in cell B1, select Pick from List and then

Please Login or Register  to view this content.

If this is not working, are you having a specific problem?

ChemistB

[concretetsunami]

I found my mistake. In A1 instead of putting "-,G,D,L" I put the name of the column "ycor". but since I changed that, it works. Thanks again!!!!!

[concretetsunami]

If I2=D or L I need K2 to select the options from AI which is named "YDL" and put them in a drop down list in K2. Can I write a =If statement in the validation to do that?

[ChemistB]

What do you want K2 to be if I2 doesn't equal D or L?

ChemistB

[concretetsunami]

I would like for it to be blank if it not L or D.

[ChemistB]

Okay, check out my example. You need to have a dummy cell to refer to.

In my spreadsheet, C1 would contain D or L to get the list to be available to D1. Otherwise, the list is not available to D1. D1 looks at F1 which has a formula which displays the list name if C1 is D or L, otherwise is blank.

ChemistB

[concretetsunami]

I know I am probably driving you crazy. I did what you said but I am still having problems. When I choose "D" or "L" in my cell the other cell just list the name of the column that I want listed. I am going to attach what I did. The dummy cell is AJ1. Also how do I get other cells in column "I" to do the same action that I want in I2? Thanks in advance.

[ChemistB]

Is this what you are looking for? You needed to drag the formula in column AJ all the way down and the indirect needs to be looking at AJ, not J.

ChemistB

[concretetsunami]

That worked. Thanks again!!!

[whitleyca]

ChemistB: Hoping you can help! I came across this thread on dependent data validation and thought I had it solved, but I am getting an error. I want to restrict project tasks based on the project ID selected. I have named ranges for the projects and the tasks under each. I have a list of Project IDs in column A, with their related activities in columns B-E. I have a drop down list to select the project and am using INDIRECT to get the dependent data; this is where my error occurs. Curious if you can help? Reference the Project ID and Task columns on the timesheet template tab on the attached.

[jeffreybrown]

Hi whitleyca & Welcome to the Forum,

Administrative Note:

• Somebody would be happy to help with your query, but first, before we can proceed…
• Please see Forum Rule #2...Do not post a question in the thread of another member -- start your own thread..
• If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
• Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[whitleyca]

Thanks, Jeff. My apologies. Was hoping to reach a specific member through this post. Can you tell me how to delete my original post?

[jeffreybrown]

No worries. You don't need to delete your post, but that particular member may be monitoring this thread its anyones guess.

If you create another thread its possible they may look at your new post, but of course I can't speak for them or their avalibility.