We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

IDataValidation with DataRange on different sheets

Thread ID:





117858 Dec 29,2014 01:21 PM UTC Jan 2,2015 10:53 AM UTC ASP.NET MVC 1
Tags: XlsIO
Michele Montanari
Asked On December 29, 2014 01:21 PM UTC

I have used a IDataValidation interface to make sure a cell value is in a range of possible values.
I Have used the ListOfValues member, but it seems to throw an exception when the total characters of the String Array is more than 256.
In the Excel Interface for that  operation, the input of characters is stopped at that length.
So I am trying to use the DataRange Property to add the List of Values.
I have created a second sheet, Hidden to put the values.
Whan I try to set the DataRange Property to that range (that is on a different sheet) There is an exception that says that the cells must be on the same sheet.
Excel permits to do that so there is a method to do it with XlsIO?

For this particular operation I am still using an older version of Dlls ( Maybe the newer version don't have this problem.


   Andrea Perazzolo

Ishwarya Narayanan [Syncfusion]
Replied On January 2, 2015 10:53 AM UTC

Hi Michele,


Thank you for using Syncfusion products.


Regarding “String array is more than 256”


In XlsIO, limitation of  listOfvalues property  is 255 characters including separators, when it exceeds the limits xlsIO throws Exception. If you want to add more characters, you could use the following code snippet.

Code Snippet:

//Add data to cells

sheet.Range["A1"].Value = "Iam 20 letter string";





sheet.Range["A15"].Value = "Iam 20 letter string";


//Sets the allow type of validation to list

validation.AllowType = ExcelDataType.User;


//Source for the above list

validation.DataRange = sheet.Range["A1:A15"];




“Cell must be on the same sheet”


Kindly, use the following code snippets to  refer  the ranges from hidden sheets. We have shared the code snippet and  sample for your reference. Please let us know if it resolves the issues.


Code Snippet:


workbook.Allow3DRangesInDataValidation = true;



Sample Link:




Please let us know if you need any clarification.



Ishwarya N


This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon