I have a list of 160+ names and four adjacent columns with combinations of values to select to categorize each of the 160+ names. The four columns have cascading (or linked) values: col 2 dependent on col 1, col3 dep on cols 1 and 2, col 4 dep on cols 1,2, 3. A master lookup table provides the four columns with duplicates in the cols. This master table has many thousands of unique records and hundreds of possible combinations.
What I'm after is to provide a cell drop-down with unique values for the user to select. And a pivot table won't provide functionality across a table of inputs.
I've created a UDF which returns what I need as an array, however I cannot get data validation to recognize the function even passing it to a defined name.
As a very simple example, with the list below can a user defined function be use to provide a unique values in a cell drop-down using data validation or other method to give the users the values to select? If a user selects Fruit for Type, the color column drop-down would show Red, Yellow, Green. If they select Red for Color then the Shape column would show just round in the drop-down, with Texure just Smooth the only value in its cell drop-down based on the previously selected values.
A B C D E
1 Name Type Color Shape Texture
2 Apple
3 Beet
4 Squash
5 Banana
Master Table of Values
G H I J
1 Type Color Shape Texture
2 Fruit Red Round Hard
3 Veg Red Round Soft
4 Fruit Yellow Long Soft
5 Veg Yellow Oblong Soft
6 Fruit Green Round Hard
7 Fruit Green Round Soft
8 Veg Green Long Hard
What I'm after is to provide a cell drop-down with unique values for the user to select. And a pivot table won't provide functionality across a table of inputs.
I've created a UDF which returns what I need as an array, however I cannot get data validation to recognize the function even passing it to a defined name.
As a very simple example, with the list below can a user defined function be use to provide a unique values in a cell drop-down using data validation or other method to give the users the values to select? If a user selects Fruit for Type, the color column drop-down would show Red, Yellow, Green. If they select Red for Color then the Shape column would show just round in the drop-down, with Texure just Smooth the only value in its cell drop-down based on the previously selected values.
A B C D E
1 Name Type Color Shape Texture
2 Apple
3 Beet
4 Squash
5 Banana
Master Table of Values
G H I J
1 Type Color Shape Texture
2 Fruit Red Round Hard
3 Veg Red Round Soft
4 Fruit Yellow Long Soft
5 Veg Yellow Oblong Soft
6 Fruit Green Round Hard
7 Fruit Green Round Soft
8 Veg Green Long Hard
