title: Excel data validation reference spreadsheet location versus static list
body: <p>I'm having trouble with some Excel data validation. On one sheet, I have a list of values that contain the list that populates the validation drop-down, like this:</p>
  
  <pre>
  A1 |1.)   New Item
  A2 |2.)  Miscellaneous
  A3 |3.)  Change Order
  A4 |4.)  Choose the item being broken out:
  A5 |          Caulk NP-1
  A6 |          Safety signs
  A7 |          Warning lines
  A8 |          Snow fence
  A9 |          Drain Lead 4 lb 30"x30" 
  A10 |          Cant Strip
  A11 |          Screws 1 5/8" 
  A12 |          SS Sheet Metal 24 gauge
  A13 |          Sheet Metal Aluminum 040 mill finish 
  A14 |          Sheet Metal Aluminum .050 mill finish
  A15 |          Termination Bar
  A16 |          Lead Boots 2"
  A17 |          Lead Boots 3" 
  A18 |          Modified Bitumen Smooth APP
  A19 |          TREMCO ELS Mastic
  A20 |          TREMCO Polyroof SF
  A21 |          TREMCO Base sheet fasteners
  A22 |          TREMCO BURmastic Composite
  A23 |          TREMCO PowerPly Standard
  A24 |          TREMCO BURmastic SF
  A25 |          TREMCO PowerPly HD Base Sheet
  A26 |          TREMCO PowerPly Standard FR
  A27 |          TREMCO Burmesh 6"
  A28 |          TREMCO Site visits
  A29 |          TREMCO Reglet Sealant
  A30 |          TREMCO WB Primer
  A31 |          TREMCO Ice Coating
  A32 |          TREMCO Tremflash tape
  A33 |          TREMCO Warranty
  A34 |          1/4" x 1 1/2" drive pins
  A35 |          SS Roof Nails 1 1/4"
  A36 |          Freight
  A37 |          Auto Fuel
  A38 |          PA Direct Labor Supervisor
  A39 |          PA Direct Labor Sheet Metal
  A40 |          PA Direct Labor Roof Coating
  A41 |          Equipment Crane 45 ton
  A42 |          Equipment Crane 70 ton
  A43 |          Platform Hoist R&G 400 28'
  A44 |          Sqeegies 24" Notched
  A45 |          Dumpsters
  A46 |          Porta John
  A47 |          Permit
  A48 |          Subcontractor RK Hydro Vac 
  A49 |          Subcontractor Roofing ICG
  A50 |          Subcontractor Lightning Protection
  A51 |          Misc
  A52 |          Subtotal
  </pre>
  
  <p>When I have another sheet reference this list, the validation works properly. However, I have a VBA macro that at some point copies this other sheet into a workbook by itself to be emailed. For this and other reasons, I need the validation drop-down list on this column to not be dependent on the other sheet, so I have the macro loop through all of the cells and create a validation string that looks like this:</p>
  
  <pre>
    1.)   New Item,2.)  Miscellaneous,3.)  Change Order,4.)  Choose the item being broken<br> out:,          Caulk NP-1 ,          Safety signs,         Warning lines
  </pre>
  
  <p>As you can see, it's the exact same list, but it's a text string, with each selection separated by commas. It works almost like I need it to, but there is a problem - The 10 leading spaces are not working like they should in the second scenario. I have them there so that those selections are indented in the drop-down box to make it more intuitive. The 10 leading spaces are in the actual "validation list formula", but don't show up when I click the drop-down or make a selection!</p>
  
  <p>Any ideas? </p>
  
  <p><strong>EDIT:</strong>
  As requested, here's the actual code:</p>
  
  <pre><code>Range("A1").Value = "1.)   New Item"
  Range("A2").Value = "2.)  Miscellaneous"
  Range("A3").Value = "3.)  Change Order"
  Range("A4").Value = "4.)  Choose the item being broken out:"
  Range("A5:A350").Formula = "=""          ""&amp;INDIRECT(""'Buy Out'!B""&amp;MATCH(""Description"",'Buy Out'!$B:$B,0)+ROW()-3)"
  Application.Calculate
  
  ' build ValidationList string for later use (this will also have to happen when misc section isn't built, so this isn't the best place for it)
  Range("A1").Select
  ValidationList = ActiveCell.Value
  ActiveCell.Offset(1, 0).Select
  Do Until ActiveCell.Row = 350
      If ActiveCell.Value = "          Subtotal" Or ActiveCell.Offset(3, 0).Value = "          Subtotals" Then 'end of the loop
          Exit Do
      End If
  
      If ActiveCell.Value &lt;&gt; "" Then
          ValidationList = ValidationList &amp; "," &amp; vbTab &amp; ActiveCell.Value
      End If
      ActiveCell.Offset(1, 0).Select
      If ActiveCell.Row = 349 Then 'this shouldn't ever happen
          ValidationListMessedUp = True
          'MsgBox ("There appears to be a problem creating the drop-down list for the ""Addon Category/Item to Break Out"".")
      End If
  Loop
  
  
  'back to buy out and populate validation as dynamic formula
  Sheets("Buy Out").Select
  Cells.Find(What:="Addon Category/Item to Break Out", after:=ActiveCell, LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Activate '* EH and message? "Is this job on the most recent MBO template? It does not appear to have the words "Release #" anywhere on the Buy Out tab
  
  With Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(TotalRow - ActiveCell.Row - 2, 0)).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= _
          "=INDIRECT(""reference!$A$1:$A$""&amp;IFERROR(MATCH(""          Subtotal"",reference!$A:$A,0)-1,IFERROR(MATCH(""          Grand Total"",reference!$A:$A,0)-1,MATCH(""          Subtotals"",reference!$A:$A,0)-3)))"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p>So the last section of this code that fills the validation with the indirect formula is the one that is working properly. Later on in the code, I populate the "static" validation list using the string. Here is that other piece:</p>
  
  <pre><code>    With Range(ActiveCell.Offset(MiscStartRow - ActiveCell.Row + 1, 0), ActiveCell.Offset(TotalRow - ActiveCell.Row + 5, 0)).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ValidationList
      .IgnoreBlank = False
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p>Thanks so much for the help. This is getting really frustrating. </p>