title: Excel drop down list not working in other cells
body: <p>What I'm trying to do is make a drop down menu using a name range and data validation.
  Simple enough however the problem arises when I have multiple cells running off the same name range.</p>
  
  <p>In my spreadsheet I have two rows using two different name ranges.  The problem is occuring for both.  One line is employee names and one line is project manager names.</p>
  
  <p>The following link is an image of the cells in question.  I do not have 10 rep and as such cannot post an image sorry.
  <a href="http://imgur.com/wfTaOZj" rel="nofollow">http://imgur.com/wfTaOZj</a></p>
  
  <p>The first cell is normal and the drop down works fine however, the adjacent cell which is a copy of the first cell doesn't drop down anything.</p>
  
  <p>the code I'm using to define the range for my drop down is:</p>
  
  <pre><code>=MANAGEMENT_list!$D$2:INDEX(MANAGEMENT_list!D:D,LOOKUP(2,1/(MANAGEMENT_list!D:D&lt;&gt;""),ROW(MANAGEMENT_list!D:D)))
  </code></pre>
  
  <p>The range goes from D2 to the last nonempty cell in column D.</p>
  
  <p>When I do data validation for the other cells I  get "the source Currently evaluates to an error".</p>
  
  <p>Hopefully I've included enough information if not feel free to ask and I will clarify.</p>