Conditional Data Validation Dropdowns

[McMasher]

Hey friends,

I am in search of a new way to do conditional data validation dropdown lists within an excel table.

Here's what I understand:

1. Creating tables and defining that table as a named range will create an automatically updating dropdown
2. I can type that named range as a string value within a cell and reference that cell with an indirect formula in a data validation list to create a conditional dropdown.

What I want:
Instead of having to create an entire new table for each conditional dropdown, I would prefer to have one table that would look like the below table.

1. [TABLE="width: 500"]
   <tbody>[TR]
   [TD]Group[/TD]
   [TD]Sub-Group[/TD]
   [/TR]
   [TR]
   [TD]Group 1[/TD]
   [TD]Sub g[/TD]
   [/TR]
   [TR]
   [TD]Group 1[/TD]
   [TD]Sub f[/TD]
   [/TR]
   [TR]
   [TD]Group 1[/TD]
   [TD]Sub x[/TD]
   [/TR]
   [TR]
   [TD]Group 2[/TD]
   [TD]Sub r[/TD]
   [/TR]
   [TR]
   [TD]Group 2[/TD]
   [TD]Sub m[/TD]
   [/TR]
   </tbody>[/TABLE]

I would prefer this method because I am then managing one table for all groups instead of over 100 tables for 100 different groups.

Is there any way I can make this work with VBA or otherwise?

Thanks in advance for any help you can offer

 

[Jeffrey Mahoney]

I know how to do it with macros better. You would have to create a cell Change Event on your first pulldown to trigger a macro to find the subset and load it into a dynamic named range table.

 

[Eric W]

Take a look at this thread:

http://www.mrexcel.com/forum/excel-questions/972151-truly-dynamic-dropdown-lists.html

I answered a very similar question. Posts #2 and #8 are probably most pertinent to your situation. Let me know if you have any questions about it.

 

[McMasher]

Eric W. Exactly what I needed. Thanks so much for your help.

 

[Eric W]

You're very welcome.

 

[carhill32]

Hello Friends,

I would like to create drop-down list in (sheet #1 ) that would reference variables from 1 of 6 different tables from another worksheet (sheet #2 ) in the same workbook.

- The drop-down created in sheet #1 , would reference 1 of the 6 tables in sheet #2 and retrieve those variables (percentages) from sheet #2 to calculate formulas into the cells in sheet #1 that is designated with the drop-down.

Question:

- Is there a better or more efficient way to perform this task rather than using a drop-down list?

 

[Jeffrey Mahoney]

My last post in this link may answer your need:
https://www.mrexcel.com/forum/excel-questions/1053995-data-validation-list-based-cell-input.html

I used a pulldown to allow the user to select values from the table. The table contains names of Named Ranges found by the vlookup in the secondary pulldown. It can retrieve values or text from many tables. The only caveat is that the many tables need to be hard coded named ranges, not dynamic named ranges. I'm guessing that the INDIRECT portion of the data validation formula doesn't like dynamic named ranges.

Jeff