title: Error with data validation and string contruction
body: <p>I am trying to add data validation to a set of cells based on a range of cells from another worksheet. Problem is that the range of cells in the other worksheet is not static and can change.</p>
  
  <p>Overall I am looking for a set of dropdown boxes in the A10:A29 cells with the ingredients in them</p>
  
  <p>When I use =INDIRECT("Ingredients!A2:A320) just using the excel validation wizard it works but I need the end cell to be dynamic.</p>
  
  <p>I have this current vba code</p>
  
  <pre><code>Dim endrow As Integer
  
  endrow = Sheets("Ingredients").Range("A" &amp; Rows.Count).End(xlUp).Row
  
  Range("A10:A29").Select
   With Selection.Validation
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:="=INDIRECT(" &amp; Chr(34) &amp; "Ingredients!A2:A" &amp; endrow &amp; Chr(34) &amp; ")"
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  </code></pre>
  
  <p>I get a 1004 error on this code.</p>
  
  <p>To make it easier for anyone looking at this the end result I am aiming for in the formula section is this:</p>
  
  <pre><code>=INDIRECT("Ingredients!A2:A*endrow*)
  </code></pre>