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

How to set a DataRange from a different worksheet ?

Thread ID:





70389 Dec 10,2007 03:45 PM UTC Jan 27,2013 11:58 AM UTC ASP.NET Web Forms (Classic) 6
Tags: XlsIO
Asked On December 10, 2007 03:45 PM UTC


Is it possible to set a DataRange of some cells (or Range) from a diffrent worksheet ?

It doesn't work for me, and I get this error :"Data range should be from same worksheet"

Is there a solution to do it ?

The code :

IDataValidation validation = sheetOne.Columns[2].DataValidation;

validation.DataRange = sheetTwo.Columns[0];

Thank you very much

Replied On December 10, 2007 03:51 PM UTC

.. I mean,
is it possible for example (an other solution which is normally possible in an excel file), to give a name to a range of cells in a second sheet, and then in the data validation from the first sheet, set the "Data Range" option to this range of cells, giving the name attribute which define the range of cells in the second sheet..


Replied On December 11, 2007 09:41 AM UTC


Today, we also tried with the ListOfValues property to pass the problem, but then, we have an error as the string[] we give to the ListOfValues DataValidation property seems to have too many elements (we have a string[222]).

It is then a problem for us, as we have no way to have a list of values or a DataRange in an other sheet, which could let us have a dropdownlist in excel cells, for our 222 choice elements..

Do you have a idea for us?

Thank you very much

Replied On December 11, 2007 03:01 PM UTC

MS Excel doesn't allow to use ranges from another worksheet in data validations, but it support such thing (parsing and work with it).
You can set
Allow3DRangesInDataValidation = true
from IWorkbook interface and use range from another worksheet.

Or you can create named range (as you suggested) and use FirstFormula property of the data validation object to set that named range

Replied On December 11, 2007 04:59 PM UTC

Hello ami,

Thank you very much,

Your Allow3D.. Solution worked very well..

Thank you again

Xavier Bocken
Replied On August 3, 2010 08:16 AM UTC

I had the same problem and I used Allow3DRangesInDataValidation = true to refer a validation range in another sheet and it work well under Excel 2003 - 2007, but with Excel 2010 when you try to save the generated file you get this message :

"One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved"

So now I use a NameRange (Workbook scope) to define my data range for the validation and then use the FirstFormula to refer the namerange and it works fine on Excel 2003 - 2007 - 2010.

string dataRangeName = "myNameRange";
validation.DataRange = IWorkbook.Names[dataRangeName].RefersToRange;
validation.FirstFormula = dataRangeName;

Must be in this order ! The second line purpose is only to define a list validation type (i did not find a better way), then it is overriden by the FirstFormula property.

Mustafa Arikci
Replied On January 27, 2013 11:58 AM UTC

l have issue too .
ArgumentOutOfRangeException when the string is higher than 255 characters issue
Please help .How do l solve it?


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