title: How to create a dropdown list on excel that filters a table?
body: <p>I have a table with the following headings on <code>Sheet 1</code>:</p>
  
  <blockquote>
    <p>List of Areas-----List of Defects</p>
  </blockquote>
  
  <p>And a table like the following on <code>Sheet 2</code>:</p>
  
  <blockquote>
    <p>Area-----Defect-----Cause-----etcetera</p>
  </blockquote>
  
  <p>I want to create a dropdown list on <code>Sheet 2</code> for defects that refers to the table on <code>Sheet 1</code> and shows only the defects for the respective area.</p>
  
  <p>The problem is that there are a lot of areas and each area has many defects, so for example Area A may appear 10 times on the first table.</p>
  
  <p>What have I already tried?</p>
  
  <ol>
  <li><p>Creating a sepearate table for every Area, however there are too many areas and it is not worth it</p></li>
  <li><p>Creating a named range, however, new defect will be constantly included on the first table</p></li>
  <li><p>I also tried the <code>=OFFSET(INDEX(Table1;MATCH(F5;Table1[List of Areas];0));;1;COUNTIF(Table1[List of Areas];F5))</code> combined with the indirect command.
  However given that the tables are in diferent sheets i cannot acces them.</p></li>
  </ol>
  
  <p>How can I fix this?</p>