Excel: How to create dynamic data validation list based on data table

Question

Imagine I'm writing a menu-planner in Excel for my kids (easier to describe than my actual problem) ...

I have a list of available foods: apples, bananas, carrots, dates, eggs, fish, etc.

I have a list of kids: John, Karen, Lional, Mike, etc.

Then, I have a simple table that lists the food each kid likes: Under John's column there will be an 'x' against apples, bananas and fish, but blanks against the others.

     J  K  L  M
---------------
a    x  x  x
b    x     x
c       x  x  x
d       x
e          x  x
f    x

Now, in the main part of my menu-planner, I want to have some cells with data validation that allow me to select food for each kid, using the above 'likes' table:

Name  Food
A2    B2

Cell A2 will contain data validation that gives an in-cell drop-down with all kids names (J, K, L, M) (that's easy - I can do that bit!)

Cell B2 needs to contain a dynamically generated list of foods that are liked by the selected kid. So, if I select John in A2, then B2 list would be {a, b, f}. If I select Lionel, B2 list would be {a, b, c, e}. Clearly, as my kid's tastes change, I can just update my 'likes' table by adding/removing 'x', and the drop-downs in B2 will auto-update.

How do I create the drop-down validation list for cell B2? (I'd prefer to do this without resorting to VBA please)

Answer 1

I assumed that your data table is in range A1:E7.

Step 1. Create a list of choices for each kid

For each kid create a list with all their preferences listed (at the end of the list I added "-" as placeholders). Enter this formula in G2 and drag to range G2:J7:

=IF(G1="-";"-";IF(ISNA(OFFSET($A$1;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+
MATCH("x";OFFSET(B$2;IFERROR(MATCH(G1;$A$2:$A$7;0);0);0;7;1);0);0;1;1));
"-";OFFSET($A$1;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+MATCH("x";OFFSET(B$2;
IFERROR(MATCH(G1;$A$2:$A$7;0);0);0;7;1);0);0;1;1)))

Also put kids names above data columns (G1:J1).

Step 2. Create conditional data validation

Given that your first data validation list (name) is in cell L2 and you've followed step 1, use this formula for data validation for food:

=OFFSET(F$2;0;MATCH(L2;$G$1:$J$1;0);6-COUNTIF(OFFSET(F$2:F$7;0;
MATCH(L2;$G$1:$J$1;0));"-"))

This formula both excludes all empty choices ("-") in the list and gives the right list based on kid's name.

---

UPDATE. Alternative solution with INDEX/MATCH

OFFSET is a volatile formula (i.e. Excel recalculates it whenever there is any change in your workbook) so you might want to do this with INDEX instead. Here is the formula for my step 1 above:

=IF(G1="-";"-";IFERROR(INDEX($A$2:$A$7;IFERROR(MATCH(G1;$A$2:$A$7;0);0)+
MATCH("x";INDEX(B$2:B$7;IFERROR(MATCH(G1;$A$2:$A$7;0)+1;1);1):B$7;0);1);"-"))

As for the step two, it seems that formula for data validation gets recalculated only when you select the cell so OFFSET doesn't have volatility in data validation lists. As INDEX cannot return a range and Excel doesn't allow INDEX(..):INDEX(..) ranges for data validation, OFFSET is better for data validation lists.

Answer 2

First post alert!!

Here's my approach http://www.mediafire.com/download/sqm41koonqjdz99/DynamicLists.xlsx

It is based on three steps.

Step 1:It uses a series of 5 tables to bash the tables of x's of food/kid likes into something more useful which lists out for each kid, their 1st, 2nd, 3rd, etc likes followed by #N/A's for any "unused" like positions. The fifth table contains the data validation information - its just a question of picking the right column and the right number of rows from this table.

Step 2: Cells D2 and E2 identify the column and number of rows respectively.

Step 3: A named range called ValidFood is dynamically defined and picks out the right column and rows from the fifth table using an =OFFSET() function. This is the clever bit - you can use functions in the name manager and that's what has been done here. The data validation rule for cell B2 simply makes reference to this named range. Alternatively, the =OFFSET() formula could have been used directly in the data validation rule using the Custom option

I've reviewed the solution in light of Lina's and found them not dissimilar. In both solutions the Step 1's achieve the same thing (except Lina's uses a '-' character in place of my #N/A for an unused choice). Both then pick out the relevant bit of the outcome of Step 1 and apply this to the data validation rule. The difference between them lies in approach: lengthy and nested formula in one (so compact but needing effort to understand) vs more extensive use of the worksheet for recording the detailed steps along the way in the other (less compact but requiring less effort to understand).

Answer 3

You can definitely do this without VBA. If you haven't added the developer tab to Excel, go ahead and do that now. http://msdn.microsoft.com/en-us/library/vstudio/bb608625.aspx

For each list, you'll want to create a named range. So make one for "name" and then one each for everyone's favorite foods. Ex: AJ_Likes, RJ_Likes.

Next, you'll want to insert a Combo Box (Active X Control). Right click on the box and select Properties. Under "ListFillRange," type the "name" range. This will fill your first box with the names of your family members. Then you want to link the list to a cell, so each selection will populate the cell with that value. You can place the dropdown directly over the linked cell so it won't appear (for aesthetics).

"LinkedCell" is also in the properties box for the combo box. Then, you want to create a second combo box. This box will have a fillrange that = a new dynamic range you will create. The dynamic range will change depending on the selection of the first box.

In the dynamic range, use a conditional formula such as this:
=IF($F$3="aj", INDEX(aj_likes,1), IF($F$3="rj", INDEX(rj_likes, 1), ""))

It basically says that if F3 = AJ, select first row from the AJ_Likes range, else, if it's = to RJ, select first row from the RJ_Likes range.

I can't seem to make my images appear, so here are some links to the screenshots I created. Hope this helps - glad to offer additional assistance.

http://hostmypicture.com/images/1ici.jpg

http://hostmypicture.com/images/2aga.jpg

Sorry - noticing you specifically asked for Data Validation. Same steps as above, however instead of adding the second Combo Box, just add data validation, LIST as the option, and select the dynamic list as your range.

http://hostmypicture.com/images/3olo.jpg

Answer 4

If you're prepared to rearrange your initial table then you can create a conditional in-cell dropdown validation list more easily, as follows:

You need to set up two tables, one for the data and one for the lookup.

Enter the kids' names in cells A1:D1.

Under each of these, enter the foods they like, one per cell, in B1:D6. You can leave blanks, as per the example above, or push them all up so some kids have shorter lists.

Then create a named range for each kid's choices - eg JohnsChoices, including only their choices, not their name as well.

These are the individual dropdown lists. Make them all the same size to allow for additions later.

Then create a lookup table which is 4 rows tall and 2 columns wide: John|JohnsChoices Karl|KarlsChoices Lina|LinasChoices Mary|MarysChoices

Then name this 4x2 table ChoiceLookup.

Then, name the cell where you want to select the name, eg Name.

In that same cell, create a dropdown validation list just for the names, ie using the left column of the table just created.

Finally, next to the Name cell, create a dropdown validation list and in the range put: =INDIRECT(VLOOKUP(Name,ChoiceLookup,2,FALSE))

This will identify the Name that has been selected first, and will then look up which choice list to use for the data validation dropdown list. The INDIRECT interprets the text string into a range, as per usual.

You can nest up to about 20 of these - if you have the patience, but in essence they are cascading choices that update subsequent options before you even get to them. I use this frequently and it is very useful, and avoids messing with MATCH, INDEX, OFFSET, etc

BTW: Almost any dialog box requiring a range can be filled with a formula so long as it resolves into a range, hence the INDIRECT.