I am working on a project where I need to have data validation dropdowns that drill down from a top category. Example below.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD]Apples[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Apples[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Berries[/TD]
[TD]Raspberry[/TD]
[/TR]
[TR]
[TD]Fruits
[/TD]
[TD]Berries[/TD]
[TD]Blackberries
[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Berries[/TD]
[TD]Strawberries[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Beans[/TD]
[TD]Green Beans[/TD]
[/TR]
[TR]
[TD]Vegges[/TD]
[TD]Beans[/TD]
[TD]Baked Beans[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Beans[/TD]
[TD]Red Beans[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Corn[/TD]
[TD]Whole Kernal[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Corn[/TD]
[TD]Creamed[/TD]
[/TR]
</tbody>[/TABLE]
This example is extremely simplified This would be several thousand rows long with 7 columns instead of 3, however you get the idea. These columns will change regularly, so I don't think it would be convenient to use this with a column for each selection using indirect.
I was hoping to use this method:
http://www.contextures.com/xlDataVal13.html
I can use this with a helper key column, and selections 1 and 3 in the example would come out well, however the dropdown in number 2 would have duplicates. For example for fruits it would show: "Apples, Apples, Berries, Berries, Berries" With how many rows there are in real dataset this is an issue.
I can't make make a space on another sheet where I remove duplicates and use this new file from the range. Because the final resultant dropdowns will be copy'd down ~ 100 rows. So for example, the final dropdowns from the example would be in a1,b1,c1 (for the 3 categories, and then would copy these to use down below) So I'd like a formula in the drop down that if a1 selects fruits, b1 has the fruits options. And then in a2 I put veggies, b2 has the veggies options.
I figure I have two options for approaching this problem using a created function, but I'm not sure which (or either) are possible.
1) Output a list from a VBA Function:
My thought is to create a function that I would enter the offset range into this function and it would output a list of individual outputs in that range (Let's say for Fruits). It seems data validation won't take an array, (which would have been easy). I'm not sure if its able to output a list of strings, in a way that data validation could accept however. Ex: "Apples","Berries"
2) Output a selective range from VBA function:
The thought here would be if I input the offset range and it would pick the cell locations out of that range that are unique. For example, the offset formula for fruits would send "B1:B5" into the function. And the function would return "b1","b3" to give a range to data validation.
Hopefully I've expressed problem clearly (I tried my best, still fairly new at this). If not please let me know any questions.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD]Apples[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Apples[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Berries[/TD]
[TD]Raspberry[/TD]
[/TR]
[TR]
[TD]Fruits
[/TD]
[TD]Berries[/TD]
[TD]Blackberries
[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Berries[/TD]
[TD]Strawberries[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Beans[/TD]
[TD]Green Beans[/TD]
[/TR]
[TR]
[TD]Vegges[/TD]
[TD]Beans[/TD]
[TD]Baked Beans[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Beans[/TD]
[TD]Red Beans[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Corn[/TD]
[TD]Whole Kernal[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Corn[/TD]
[TD]Creamed[/TD]
[/TR]
</tbody>[/TABLE]
This example is extremely simplified This would be several thousand rows long with 7 columns instead of 3, however you get the idea. These columns will change regularly, so I don't think it would be convenient to use this with a column for each selection using indirect.
I was hoping to use this method:
http://www.contextures.com/xlDataVal13.html
I can use this with a helper key column, and selections 1 and 3 in the example would come out well, however the dropdown in number 2 would have duplicates. For example for fruits it would show: "Apples, Apples, Berries, Berries, Berries" With how many rows there are in real dataset this is an issue.
I can't make make a space on another sheet where I remove duplicates and use this new file from the range. Because the final resultant dropdowns will be copy'd down ~ 100 rows. So for example, the final dropdowns from the example would be in a1,b1,c1 (for the 3 categories, and then would copy these to use down below) So I'd like a formula in the drop down that if a1 selects fruits, b1 has the fruits options. And then in a2 I put veggies, b2 has the veggies options.
I figure I have two options for approaching this problem using a created function, but I'm not sure which (or either) are possible.
1) Output a list from a VBA Function:
My thought is to create a function that I would enter the offset range into this function and it would output a list of individual outputs in that range (Let's say for Fruits). It seems data validation won't take an array, (which would have been easy). I'm not sure if its able to output a list of strings, in a way that data validation could accept however. Ex: "Apples","Berries"
2) Output a selective range from VBA function:
The thought here would be if I input the offset range and it would pick the cell locations out of that range that are unique. For example, the offset formula for fruits would send "B1:B5" into the function. And the function would return "b1","b3" to give a range to data validation.
Hopefully I've expressed problem clearly (I tried my best, still fairly new at this). If not please let me know any questions.
