Updating cells based selection drop down list data validation
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).
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. Under each of these, enter the foods they like, one per cell, in B1: D6.
In the dynamic range, use a conditional formula such as this: =IF($F="aj", INDEX(aj_likes,1), IF($F="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.Step 2: Cells D2 and E2 identify the column and number of rows respectively.Step 3: A named range called Valid Food is dynamically defined and picks out the right column and rows from the fifth table using an =OFFSET() function.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.
Search for updating cells based selection drop down list data validation:
Here is the formula for my step 1 above: 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. 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.