title: Populate a ComboBox based on a chain of selection criteria
body: <p>I have two worksheets, let's call them worksheetA and worksheetZ:</p>
  
  <p><strong>WorksheetA</strong> has two columns that we're interested in - "Order"(Col C) and "Customer" (Col D).
  The "Customer" column holds a combo box/drop down list populated by a load of customers' names.
  The "Order" column holds another combo box/drop down list with details about the order from this customer, such as order number, customer code, and order description.
  (I understand this is a bad layout by the way, it's not my design). Example:</p>
  
  <pre><code>value in Customer Column (column D): "Google"  
  value in Order Column (column C): "1234 - GOO - An order from google."
  </code></pre>
  
  <p><strong>WorksheetZ</strong> contains two columns that interest us, it holds the values that are used to populate the ComboBoxes in WorksheetA:</p>
  
  <p>again, an "Order" column (Column A), which contains the same data, but the data is split into rows, instead of being a drop down list, and the "Order" drop down list in worksheetA is currently populated by all of the rows in this column.</p>
  
  <p>And a "Customer" column again, which holds customer names - "Google" - "IBM" etc.</p>
  
  <p>Currently, the "Order" column in WorksheetA is populated using this data validation <code>=WorksheetZ!$A:$A</code> which will select ALL orders (over 300 at the moment!)</p>
  
  <p>I need to be able to display ONLY orders for customers that are already selected in WorksheetA.Customer in the "Order" dropdown on worksheet A.</p>
  
  <p>So, (and please forgive my terrible SQL-like-pseudo-code)</p>
  
  <p><strong>WorksheetA.Order = (Select * from WorksheetZ.Order where worksheetA.Customer = WorksheetZ.Customer)</strong></p>
  
  <p><a href="http://i.stack.imgur.com/HOyqV.png" rel="nofollow">Here's the drop-down for WorksheetA.Customer (I've removed company-sensitive info)</a></p>
  
  <p><a href="http://i.stack.imgur.com/p8wCm.png" rel="nofollow">Here is WorksheetZ (again censored), where "Column1" in "A" is "Orders", and "Client" is "Customers"</a>  </p>