Vlookup+Dropdown

[beseda2004]

Hello.
I need to program my project in such way, that when value of a specific sell changes other sells would change their drop down lists according to VLOOKUP.
There are different items which are under same categories and this is why drop down list is needed
I know how to create VLOOKUP function and how to make cells automatically change their drop down lists, but I am struggling combining then together.

Best regards, Vlad

[jeffreybrown]

Hi Vlad,

Not quite sure I understand the make-up of your criteria here and what would be the best solution.

Could this be somewhere in the ballpark?

http://www.contextures.com/xldataval02.html

If not, can you give a sample of what you have and what you desire?

[beseda2004]

Let me try work it out myself. Is it possible to put =VLOOKUP() formula into the Data Validation (List)?
If so, what should be different to the normal vlookup (e.g. to the one I would put into cell)
Vlad

[scottylad2]

Upload a sample please, it'll take out a lot of gueeswork on the forums part. Uoi could reference using the indirect function a drop down range and include that in a vlookup, but there's possibly better solutions if we know what you're trying tp do

[jeffreybrown]

I've never seen a vlookup used in data validation, but that doesn't mean it can't.

A vlookup normally returns one result so why the need for data validation for one result.

I'm afraid we'll need more information than what you have provided.

A sample workbook always helps with data setup reflecting your actual setup and with what you expect.

[beseda2004]

Ok, this is kind of what I want to find out.
Vlad

[jeffreybrown]

I fixed your attachment to use Indirect in the data validation for I11 and then created named ranges for B1:D4.

The named ranges are titled in B1:D1 which will match what you select in G11 and therefore I11 returns only those side dishes.

Take a look at the link I attached which explains this method.

[beseda2004]

Oh god, sorry for that, its a wrong question, i found answer for it which is overcomplicated thou, using macros. So your version will also help a lot, thank you for that.

The proper question is here

[scottylad2]

Is it imperative that the item and colour are in seperate cells?

[beseda2004]

no, colour is just for indication

[tigeravatar]

beseda2004,

Attached is a modified version of your 'question lookup+validation' workbook. I reformatted how the information was stored to make life a little easier for Excel, and then used a lot of Named Ranges to reference the information. I made Sheet2 the 'Lists' worksheet. Column A is Empty (more on that below when I explain the named ranges) and Column B is Section (the unique list of Sections extracted from the data table, namely NAVY, ARMY, and RAF).

Here's the formula for 'Lists' column B (note that this is an array formula and thus requires that it be entered with Ctrl+Shift+Enter and not just Enter):

Please Login or Register  to view this content.

The named ranges are set up dynamically so that as information is added and removed, the drop-down lists will auto-update. Here are the different named range formulas:

• Empty (this named range is so that the drop-down list can be empty if a Section has not been selected:

  Please Login or Register  to view this content.

• rngTable (the table of data for the section, clothing types, color, and price):

  Please Login or Register  to view this content.

• Section (the unique list of Sections extracted from rngTable):

  Please Login or Register  to view this content.

• Start (Used for the data validation offset formulas to dive into and extract information from rngTable):

  Please Login or Register  to view this content.

The data validation formula for the drop-down lists is the following:

Please Login or Register  to view this content.

The Price formula is a SumIfs:

Please Login or Register  to view this content.

It all looks pretty scary, I know. Its not exactly a beginner's formula, but it gets the job done quite well with no VBA.

[scottylad2]

This isn't too formula heavy. I've made a mash up from your tables, made a few named ranges and used the indirect function. If your original data needs to be seperated into columns like you had, then i suggest you copy the formula in the tables i've created below the ones you've set up

[beseda2004]

2 tigeravatar, I am afraid your drop downs don't work. I will go through the code when I will wake up, thank you for help.
2scottylad2, your formula seems to be working, thou it always gives the whole list of items on a drop down, rather than limited by cathegory

[scottylad2]

It's the only way to do it that I know that avoids having a lot of offset formulas

[scottylad2]

Heres another stab at your sheet. The table I'm referencing, you can either type your data as such, or use a formula to extract same from your data. I've moved your current setup down the page a bit, not required if you can make a table like the one i have. Again, it's a few named ranges using the offset and match, then referenced by vlookup using an indirect function. At each change in service, you will need to reselect accordingly.

hope this helps

[scottylad2]

Slight error with last sheet, named ranges are referencing a rance i've since deleted. This sheets fixed

[beseda2004]

Thanks for help guys.
How do we close that thread?

[scottylad2]

Go to your original post, click edit and select Solved. Which solution worked for you?