DataValidation from range on other sheet

[Matt Andreko]

I'm trying to add Data Validation to a cell in a worksheet. However, that Data Validation is a formula that references another sheet in the workbook.

In Excel, the Data Validation looks like this:

https://www.evernote.com/shard/s34/sh/5f2ae187-fc01-42f3-ac39-bc4a4d443f41/3d38ea991dd2fbdee5a06f00c51e3ee3

Essentially it's:

Allow: List

Source: =Other_Worksheet!$A$22:$A$37

I can't seem to make this work in XlsIO.  Is it not supported?  I don't want to hard-code the list of values in my application if I can avoid it.

The code I'm doing so far:

var assessmentPhaseValidation = vulnsTab.Range["A1:A" + (row - 1)].DataValidation;

assessmentPhaseValidation.AllowType = ExcelDataType.User;

assessmentPhaseValidation.PromptBoxText = "Data Validation List";

assessmentPhaseValidation.IsPromptBoxVisible = true;

assessmentPhaseValidation.ShowPromptBox = true;

However, when I do that, it strips off the other sheet name, making the validation in the current sheet (which doesn't work).

[Matt Andreko]

Sorry, I missed a line.  Pretend that this line was also in the above source:

assessmentPhaseValidation.FirstFormula = "=Criticality_Overview!$A$22:$A$37";

[Matt Andreko]

For anyone else that has issues, I made it work like this:

var assessmentPhaseValidation = vulnsTab.Columns[0].DataValidation;

 assessmentPhaseValidation.DataRange = vulnsTab.Workbook.Worksheets["Criticality_Overview"].Range["A22:A37"];

In addition to that, I also had to have this enabled on the workbook:

workbook.Allow3DRangesInDataValidation = true;

[Sridhar]

Hi Matt,

Thank you for using Syncfusion products.

We are glad to know the above reported issue is resolved at your end.

If you still face the issue, please get back to us with issue reproducing sample with excel template to proceed further.

Please let me know if you require any further clarifications.

Regards,

Sridhar.S