title: Excel: How to create dynamic data validation list based on data table
body: <p>Imagine I'm writing a menu-planner in Excel for my kids (easier to describe than my actual problem) ...</p>
  
  <p>I have a list of available foods: apples, bananas, carrots, dates, eggs, fish, etc.</p>
  
  <p>I have a list of kids: John, Karen, Lional, Mike, etc.</p>
  
  <p>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.</p>
  
  <pre><code>     J  K  L  M
  ---------------
  a    x  x  x
  b    x     x
  c       x  x  x
  d       x
  e          x  x
  f    x
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Name  Food
  A2    B2
  </code></pre>
  
  <p>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!)</p>
  
  <p>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.</p>
  
  <p>How do I create the drop-down validation list for cell B2? (I'd prefer to do this without resorting to VBA please)</p>