• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Vlookup with Data Validation

J

Jonathan G

New Member
Hello all,

I am making a template for entering in new product data. I would like to create data validation lists that pull their values from the column header above.

So, for instance, if G2 has the value of "Watch Brands" I would like to have a data validation formula in G3 that takes that value, searches another sheet for the associated list, and provides those values as the options in a data validation drop down.

I've attached an example of what I am working with. I hope the question is clear - if not please let me know and I will clarify whatever I can.
 

Attachments

  • dummydataWITHTEMPLATE.xlsx
    15.4 KB · Views: 4
Nunes,

Thank you for your reply. My challenge is that, depending on the category of product, the column headers may change. I would like to have the data validation select its list dependent on the value of the (variable) column header.
 
You can solve that by using named ranges.

Name the clothes brands range list clotheslist, for example.
Name the watch brands range list watchlist, for example.

Create an auxiliary table somewhere in you workbook with the possible headers for your data on the first column and the named ranges you want for the data validation in the second column, like this:
Clothes Brands clotheslist
Watch Brands watchlist

Then, assuming the G2 header exemple, you'll put this in the data validation for cell G3:
=INDIRECT(VLOOKUP($G$2;AUXILIARY_TABLE;2;0))

Where AUXILIARY_TABLE is the table you created in the previous paragraph of my explanation.

Hope it helps.
 
Here's a file with an implementation of the explanation I provided.

In yellow there are data validated cells that dynamically change accordingly to the header of G2.
the auxiliary table i mention is in the sheet Aux.

Best,
Nuno
 

Attachments

  • dummydataWITHTEMPLATE_SOLVED.xlsx
    16.4 KB · Views: 0
The catch with my solution as it is right now is that if you want to add or remove elements from the Clothes List or from the Watch List, you'll be stuck with the original named ranges, but you can also solve that by dynamically naming a range.

If you want help with that drop a line here, ill try to say something later, as im out of time now...
 
if I am not mistaken, here the validation needs to be dependent on the entry in the previous column - is that correct ? For example, I believe you are looking at an option to restrict "Online" only for one product, whereas, when another product is selected, it may be available for both "Online" and "Offline" as well. Is this understand correct ? If so, I will explain the "Indirect" option in drop down validation in my next post.
 
Nunes your reply is immensely helpful, and helps solve almost all of my task for me. If you can help me with dynamic ranges, I will just about be all set.

My sincerest of thanks for your assistance.

Sudhir - Thank you for your post and assistance. In this case, the dropdowns do not need to be dependent on the selections in prior dropdowns. Thanks!
 
hey @Jonathan G !

for the dynamic named range you can use Chihiro's proposed solution.

i'm used to use a slightly different solution that ill explain

For example, in the workbook i've sent you with your problem solved, if you go to the Name Manager, the named range cbrandslist is defined as referring to
"='Master Data'!$C$2:$C$17", while in reality you'd want it to be referring to "='Master Data'!$C$2:$C$XX", where XX would be the last line with filled cells (until row 27, which in your workbook is the last available row to add items to this list).
To achieve this, in the Name Manager you should define the cbrandslist range as "=OFFSET('Master Data'!$C$2;;;COUNTA('Master Data'!$C$2:$C$27);1)"

The same reasoning would apply to your other lists.

Again, this is a fairly similar method to the one Chihiro proposes above, i'm just more used to this one with the offset than to that one with the index, but in reality it all comes down to the use of the counta formula.

I hope this helps.

Best
Nuno
 
Please mind that in my formulas, every ";" should be replaced by "," (almost for every regional setting except mine).
 
Nunes and Chihiro,

I can't thank you enough for your help. I am astonished at the graciousness you and the others at this forum display, who give so willingly of their time and expertise.

If you have the time and wouldn't mind, I'd like to understand a little better how your solution works. I've read a few articles on the INDIRECT and OFFSET functions, but don't fully understand yet. Could you possibly explain the syntax of your formulas:

=INDIRECT(VLOOKUP($G$2;AUXILIARY_TABLE;2;0))

and

=OFFSET('Master Data'!$C$2;;;COUNTA('Master Data'!$C$2:$C$27);1)

Again, only if and when you have the time. You have solved my challenge, and I only want to understand better.

Thanks!
 
You can disregard my question regarding the INDIRECT function. I fully understand it now. I'll keep working on OFFSET and COUNTA, and INDEX/MATCH as well.
 
Whoa! I'll have to learn this approach as it looks to be much easier to maintain than other approaches and as you said, dynamic.
 
Hi Jeff,

Thanks for your post! I saw (both) of your posts on the subject earlier, and very much enjoyed them. My challenge was that I couldn't figure out how to modify them to suit my particular needs. This is because while I am learning (and loving it) I am still very much an excel noob. Is there a way to modify your approach so that it can accomplish the same thing as the attached spreadsheet?

As you can see in the spreadsheet, when I change the main category it changes all (or some) of the column headers, and the column headers dictate the options available in the drop downs directly below them.

Thanks!
 

Attachments

  • template.xlsx
    17.1 KB · Views: 4
My suggestion is to NOT change the column headers, but instead use something more generic. e.g. in column k you have either the heading "Material" or "Neckline" depending on the choice in B1. Why not simplify things, and just do away with that dropdown in B1 and rename the category in K1 to "Material/Neckline".

In L1 and M1 you have 'Gender' and 'Warranty' if IHPCAT2 is selected. Just make those headings permanent, and populate the column with 'Not Applicable' if something from IHPCAT1 is in the list.

Or use two seperate sheets - one for IHPCAT1 and the other for 2.

You are trying to do too much by having columns that change, and it is a recipe for disaster.

In fact, if the purpose of the Template sheet is purely to display data, just use a PivotTable to display it.
 
Hi Jeff,

This particular document is populated by dummy data, but in the final version it will need to be able to pull data from IHPCAT1-IHPCAT650.

Each of these Categories will have a different combination of headers (attributes), and each attribute will potentially have different values, as controlled by the IHPCAT.

So for instance, the "Brand" attribute will have values 1-10 for IHPCAT1, and 11-20 for IHPCAT75.

The purpose of this template sheet is not to display data, it is to gather data from one division which will then be passed off to another division for processing. I don't feel that Excel is the best tool for this task, but it is the only one I am permitted to use. (User demands)
 
Jeff,

I could, but that would be 650 sheets, which would be unwieldy. And, the requirements I've been given specifically ask for a single template. I'd also ultimately like to set this up so that any additions, modifications, or deletions can be pulled in from the external db and reflected automagically.

Hopefully this whole thing will only be a stopgap until I can convince the Powers That Be to just use a better matched tool.
 
At a time, yes.

Essentially, a user will get in an order for new products that need to be processed for e-commerce. They will fill out the applicable information for each style, and pass the form over to a new division that will actually process each style/product.

There is a dedicated processor for each category of products (the IHP) and so each sheet will only have products related to that IHP on it.
 
Ok. I just don't see how a single template is a good idea...especially if your product attributes vary wildly across all 650 categories. So I'm going to explore my 'one sheet per category' option just a little more before we drop it.

How about you simply have a hyperlinked index in the first sheet of the workbook - or a picklist - and when they pick the relevent category they need to fill out, it takes you to that sheet?

With a small bit of VBA, you could actually keep all sheets hidden but the one you've chosen, if that makes it more palatable to the powers that be.

Trying to do it the way you've been instructed is a recipe for disaster, given what you've outlined above.
 
You must log in or register to reply here.
Back
Top