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. Image for the cookie policy date

How to set a DataRange from a different worksheet ?

Hello,

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


6 Replies

DY dymezac 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..

Thanks



DY dymezac December 11, 2007 09:41 AM UTC

Hello,

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



AN Anonymous 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



DY dymezac December 11, 2007 04:59 PM UTC

Hello ami,

Thank you very much,

Your Allow3D.. Solution worked very well..

Thank you again




XB Xavier Bocken 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.


MA Mustafa Arikci 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?


Loader.
Live Chat Icon For mobile
Up arrow icon