Dynamically populated data validation lists based on other criteria

Hello,

I'm trying to create some input validation stuff so I can have users enter data in an easy and correct way, without having to worry about errors. I've been searching but can't really find a way to do this, as most forum topics are about using predefined lists and just selecting the correct list dynamically. I want to take it a step further.

For this, I have the following table:

CODE Product Family Support
A000 ProdA FamilyA SupportX
B000 ProdB FamilyB SupportX
B001 ProdB FamilyD SupportY
C000 ProdC FamilyC SupportZ
C001 ProdC FamilyA SupportX

The users have to select the Product in the first dropdown, and then the second dropdown should be populated with the values of column Family, but only those that are valid for the selected product. Same goes then for the Support column. The last column will then contain a manually entered number to specify the amount they want of it.

this should be repeatable over many rows, so each user can enter his/her selection.

Anyone any idea?

Take a look here

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

You will need to add formulae to create your dynamic lists so it will help if you post a sample file which mirrors what you require.



Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

It's hard to create a mockup without doing a load of work.

Basically, the tab Master List is the tab I want to update with all my data.

The tab "Requirement list" is where users should select in column A the product, but column B should then populate with the valid values of the Metric column in the master list for that particular product. Same for the support column.

this list should be completely dynamic.

the attached has a possible "model" approach BUT is incomplete.

Named range "Metric_List"

in I2

=IFERROR(INDEX($E$2:$E$28,SMALL(IF($B$2:$B$28='Requirement list'!A2,ROW($E$2:$E$28)-ROW($E$2)+1,""),ROWS($E$2:E2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down.


Issue here is the need to change the A2 reference to A3,A4 etc : you will need VBA to do this (Worksheet event macro).

Further, you have multiple instances of a Product in you list with same Metric values so the list needs extract unique metric value.

Dat Validation for "Metric" is now

List

=Metric_List

Another alternative is to a Metric list for each Product (Product1_Metric, Product2_Metric....)

the DV for Metric will be

=INDIRECT(A2 &"_Metric")

Copied down

I'm starting to doubt excel is the right tool for this.

=INDEX('Master List'!$D$2:$D$100,MATCH('Requirement List'!$A20,'Master List'!$B$2:$B$100,0)):INDEX('Master List'!$D$2:$D$100,MATCH('Requirement List'!$A20,'Master List'!$B$2:$B$100,1))

This named range comes up correctly, but with doubles, which is also not what I want.

I know ... As I said in my post there are several issues to be addressed ...

Further, you have multiple instances of a Product in you list with same Metric values so the list needs extract unique metric value