title: Excel VBA drop down list from named range
body: <p>Similar problem: <a href="https://stackoverflow.com/questions/31455822/using-indirect-function-in-data-validation-through-vba">here</a></p>
  
  <p>I have this code, where I want to create a drop down list, say in Range B1:B2, where the list is dependent on some named ranges given in A1:A2. </p>
  
  <p><a href="https://i.stack.imgur.com/k5j2o.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k5j2o.png" alt="enter image description here"></a></p>
  
  <pre><code>Sub asdf()
  
  
      With Range("B1:B2").Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:="=INDIRECT(INDIRECT(""RC[-1]"",0))"
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  
  End Sub
  </code></pre>
  
  <p>Problem is this gives me the error </p>
  
  <blockquote>
    <p>Run time error 1004' (Application defined or object defined error)</p>
  </blockquote>
  
  <p>and when I click debug the following line is highlighted </p>
  
  <pre><code>   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=INDIRECT(INDIRECT(""RC[-1]"",0))"
  </code></pre>
  
  <p>The code actually has run a couple of times, I have no idea why. But then only the first cell A1 gets a dropdown list with correct values from the named range - while the cell A2 is a dropdown list with no values (all named ranges work, I've checked)</p>
  
  <p><strong>EDIT</strong> 
  I think I located part of the problem, although this does not explain my problems with running the code. (forgive the non-English version of my Excel, but you might recognize this as the "Name Manager" inside "Formula"). The code is basically exactly the same as the code I get from the Macro Recorder, so this should work, should it not? Some of my named ranges has Value {...}. Why is this? The Named Ranges refer to the correct cells, but the cell values are empty...</p>
  
  <p><a href="https://i.stack.imgur.com/lfg5z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lfg5z.png" alt="enter image description here"></a></p>
  
  <p><strong>EDIT2</strong></p>
  
  <p>It seems that you do not get the values for the cells you choose for a named range, when the cells are separated. This is so messed up... Is there a away around this?</p>