data validation drop down

April 7, 2020 at 20:58:54
Specs: Windows 10
I want a cell to contain a data validation drop down list for vendor items dependent on which vendor is selected from another cell.

See More: data validation drop down

Reply ↓  Report •

#1
April 8, 2020 at 09:43:46
Look here for a very good tutorial on Dependent Drop Down List:

https://contextures.com/xlDataVal02...

If you have any problems, let us know.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
April 8, 2020 at 09:53:39
Thank you Mike. I have watched that one several times and countless others and I have been trying to figure this out for seems like forever. I have a requisition we use for purchase request. Could I possibly send it to you so you can see what I'm wanting to do? I have searched and searched and tried to understand but I am having no luck at getting it to do what I want it to. I know how to do the dependent drop down for the vendors but I want another cell in the requisition to contain a dropdown for all item for the selected vendor. That's what I can't figure out.

message edited by JenniferT


Reply ↓  Report •

#3
April 8, 2020 at 10:45:55
Maybe we can walk you through it step-by-step. (There aren't very many steps)

If you already have a drop-down that populates a cell with the vendor names, you are done with Step 1.

The next step is to create a Named Range for each vendor:

Step 2A: Put all of a given vendor's items in a range of cells, e.g. F1:F100.

Step 2B: Select that entire range and enter the vendor's name in the Name field above the Row numbers/Column A. Now press Enter. You must press Enter after entering the Vendor's name or the Named Range will not be created. If you click outside the field before pressing Enter, the Named Range will not be created.

In addition, the Vendor's name must be entered exactly as it appears in the Vendor Name Drop Down list that you already have.

Step 2C: Repeat the creation of Named Ranges for the other vendors' items, e.g. using G1:G100, H1:H100, etc.

Let's us know if you are having trouble with those steps before we move on.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
April 8, 2020 at 10:54:47
Thank you. I will work on that. I appreciate your help! ☺

Reply ↓  Report •

#5
April 8, 2020 at 15:02:29
You will know if you got it right by following these steps:

1 - Click the Down Arrow in the Name box above Column A
2 - If you see the name of the Range (in your case a "vendor name") then you'll know that the Named Range exists.
3 - If you select one of the names, the corresponding range will be selected.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
April 8, 2020 at 21:14:45
I have completed everything you suggested above. Now that I have it done, how do I get the cell with the drop down (cell E1 thru E14) to pull in the drop down list for the selected vendor in cell C2? I will be glad to send you the spreadsheet if you want.

Reply ↓  Report •

#7
April 9, 2020 at 04:52:04
I'm confused.

You said "The cell with the drop-down" followed by "cell E1 thru E14”.

E1:E14 is a range of cells, not a cell.

Please clarify that.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#8
April 9, 2020 at 05:13:42
I apoligize, it is cells E8: E14. Cell C2 is the vendor name that has a data validation for my vendor list. Cells E8:E14 are the requisition lines for the requested items that are in named ranges on a separate tab. I want to be able to choose from the Vendor list in cell C2 and then I want the items from the named ranges to be available in cells E8:E14 as a drop down list depending on the vendor selected in C2. If I can get it to do that, then I know how to automatically fill the price, UOM and our cost codes.

Reply ↓  Report •

#9
April 9, 2020 at 06:32:14
That's what I thought, I just didn't want to move on until I was sure that we were on the same page.

OK, next steps...

First, a side lesson on the INDIRECT function.

The INDIRECT function converts a text string into a cell reference. For example...

- As you know, =A1 refers directly to cell A1
- "A1" (with the quotes) is a text string.
- Therefore, =INDIRECT("A1") also refers to A1
- =INDIRECT("A" & 1) also refers to A1 because the result of the concatenation of "A" and 1 is the text string "A1"
- A1 entered in a cell would also be a text string as far as Excel in concerned, even without the quotes. Therefore if you enter A1 in cell B1, then =INDIRECT(B1) will refer to A1 because B1 contains the text string "A1"

By using INDIRECT you can create a variable cell reference, something like =INDIRECT("B" & C1) where C1 contains a formula that resolves to a Row number.

Now back to your issue:

Since you have created Named Ranges, each of which has a text string name that exactly matches a Vendor name in the C2 list, we can use INDIRECT to refer to a specific Named Ranged based on the contents of C2.

1 - Select cells E8:E14
2 - Open the Data Validation wizard
3 - Choose List
4 - In the Source field enter =INDIRECT($C$2)
5 - Click OK

The INDIRECT function in the source field will attempt to convert the string from C2 into a cell reference. Since that string happens to refer to a Named Range, Data Validation will use that Named Range as the actual source for the Drop Downs in E8:E14.

Let me know if you have any issues with that process.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#10
April 9, 2020 at 22:00:13
I have completed everything above. The drop down list is not showing up.I have 3 columns merged. Would that cause the drop down not to work properly?

Reply ↓  Report •

#11
April 10, 2020 at 06:54:37
Without knowing what three columns you have merged, that's a hard question to answer. We would need more details.

2 things to try:

1 - Practice the dependent drop down creation in another work sheet with no merged columns. The goal here is to simply ensure that you are doing everything right. Before we start chasing solutions and wondering why it doesn't work in a "complicated" worksheet, let's make sure you can make it happen in a simple worksheet.

2 - Unmerge the columns and see what happens.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#12
April 10, 2020 at 14:03:11
Just FYI.

I merged D2:F2 and created a drop down list use the INDIRECT function in that merged cell

I created 2 Named Ranges, one referring to J4:L11 with the cells in those 3 columns merged and another referring to M4:O11, using the same merged set up.

The lists for those Named Ranges worked fine in the D2:F2 drop down, based on the Name entered in C2.

Based on that, I strongly suggest that you try a simple dependent drop down list, without any merged columns just to be sure that you have the basic technique down.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#13
April 10, 2020 at 15:10:50
I tried this formula =INDIRECT(SUBSTITUTE(D3," ","_")) and it works to some extent. Not all of the vendor list pulls through but it does for most of them. Not sure what I need to do now.

Reply ↓  Report •

#14
April 10, 2020 at 17:54:18
Again, without more details, we can't help much more.

Obviously, if it works for some vendors and not for others, the ones that it doesn't work for are of a different structure than those that it works for. We can't help with that unless you give us something to work with, like perhaps a few vendor names that work and a few that don't.

Earlier you said "The drop down list is not showing up. I have 3 columns merged."

Did that mean none were showing up, and now some are, or was "some working, some not" the original problem?

Once again we need more details. If we have to keep asking for details every time you tell us it's "not working" we'll probably eventually stop asking and move on. Example data, specific problems, formulas, etc. Help us help you and we'll eventually get this working.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#15
April 11, 2020 at 20:35:47
I apologize for being a pain. I haven't asked for help before now because I really didn't know how to put what I wanted into words. I figured out what the problem was with the vendor item list that wouldn't show up. I had to take out all the dashes, commas, and symbols from the vendor names. It works exactly how I imagined it. Thank you so much for your help and direction. I wouldn't have figured it out without your help! I am so excited to use and share this new version of our requisition at work!!! It will save everyone time! Thank you again! ;-)

message edited by JenniferT


Reply ↓  Report •

#16
April 12, 2020 at 06:20:19
I'm glad that you got it working. I kind of thought that the vendor names were the issue, but without examples, there wasn't much I could offer.

Now, just for fun, look into Dynamic Named Ranges for the item lists.

DNR's are Named Ranges that can lengthen and shorten based on the numbers of items in the range. If items are added or removed, the list in the drop-down will always contain the entire list, without blanks, etc. Less maintenance.

You can also use the DNR name to count things, average numbers, etc. since each DNR will always be the exact length of each list.

If you run into trouble, start a new thread dedicated to Dynamic Named Ranges since that a separate issue from your Dependent Drop Down question.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#17
April 12, 2020 at 06:33:01
I will check that out! Thank you!!! Happy Easter!

Reply ↓  Report •

Ask Question