title: Adding Data Validation to an excel spreadsheet using vb.net
body: <p>I am trying to add Data Validation to a spreadsheet using VB.NET with data that is on Sheet2.  I am able to open the workbook with both sheets and the data I need on Sheet2, but the problem I am having is populating the Data Validation Dropdown on Sheet1.  I am trying to create the Data Validation in Column A and the first 700 rows of Sheet1, and the data on Sheet2 will be in Column A with the first 800 rows.</p>
  
  <p>I have tried the following and it creates the Data Validation, and it only has one value inside - "Sheet2" -since I am not actually putting a formula in here.</p>
  
  <pre><code>With xlsSheet 'worksheet
      .Range(String.Concat("Sheet1!$A$2:$A$700")).Validation.Add(XlDVType.xlValidateList, &amp; _
       Formula1:="Sheet2")
  End With
  </code></pre>
  
  <p>When I try the following, it creates the excel sheet with the columns and the data on sheet2, but it doesn't create the data validation.</p>
  
  <pre><code>With xlsSheet 'worksheet
      .Range(String.Concat("Sheet1!$A$2:$A$700")).Validation.Add(XlDVType.xlValidateList, &amp; _
       Formula1:="=Sheet2!$A$2:$A$800")
  End With
  </code></pre>
  
  <p>I was looking for examples online and found <a href="http://www.get-digital-help.com/2011/10/05/add-values-to-a-data-validation-list-vba/" rel="nofollow">http://www.get-digital-help.com/2011/10/05/add-values-to-a-data-validation-list-vba/</a> but this too did not work for me.</p>
  
  <p>How can I get the data from A2:A800 in sheet2 into data validation on sheet1 from A2:A700?</p>
  
  <p>Any help would be appreciated.</p>