Adding data validation in Excel Addin

RBiems 

In your code, you're trying to use the formula =INDIRECT(C2) directly as the validation formula. However, when setting data validation programmatically in Excel, you can't directly reference cell values in the same way as you do in the Excel formula bar.

Instead, you need to construct the formula dynamically using the appropriate syntax for referring to cell values. Here's how you can modify your code to achieve this:

Code is untested please backup your file.

using Excel = Microsoft.Office.Interop.Excel;

// Assuming wsN is a reference to your worksheet
Excel.Range range = wsN.Range["D2"];

// Construct the formula dynamically
string formula2 = "=INDIRECT(\"'\" & C2 & \"'\"";

// Add data validation to the range
range.Validation.Add(
    Excel.XlDVType.xlValidateList, 
    Excel.XlDVAlertStyle.xlValidAlertInformation, 
    Excel.XlFormatConditionOperator.xlBetween, 
    formula2,
    Type.Missing
);

In this modified code:

• We construct the formula as a string, concatenating the parts together using &.
• We use double quotes (\") to properly enclose the sheet name in the INDIRECT function. Note the additional single quotes (') around the sheet name.
• We pass the formula as an argument to the Validation.Add method.

With these adjustments, the formula should work properly when set programmatically in your Excel add-in. Make sure to adjust the code according to your specific requirements and environment. The text, steps and formulas was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.