Add Data Validation List to Excel in C#

Question

I'm trying to add Data Validation to Excel using Microsoft.Office.Interop.Excel in a C# Program.

I have a sheet named MapAssist in which column A contains some value. Now want to add a validation list of that column A in MapAssist to another sheet named Maps. What I've done so far is:

Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)ws.Cells[row, column];

rng.Validation.Delete();
rng.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertWarning, Formula1: "=MapAssist!$A:$A");

rng.Validation.ErrorTitle = "Value Error!!";
rng.Validation.ErrorMessage = "Please select a value from dropdown list.";

rng.Validation.ShowError = true;
rng.Validation.InCellDropdown = true;
rng.Validation.IgnoreBlank = false;

This simply doesn't add any validation to the cell I wanted. When I remove the "=" from "=MapAssist!$A:$A", like "MapAssist!$A:$A", it worked but don't serve my purpose. In the dropdown I just get "MapAssist!$A:$A". But I want the values in the list.

Is there any way to do it? Any help would be appreciated.

Thank you.

Answer 1

If I got the question right you want to copy the values from the Column A in some sheets and put those in a new sheet to make a validation of some sort easier, if this is it I can try to help, first thing I think you don't have to specify the name of the sheet in the formula, since I'm pretty sure you can't do that, and in the formula you have to specify the actual cells, right now it seems like you are making a comparison between A and A, you have to specify the column as well, let me know if this can help