Named range and data validation on list

[Marco Giorgi]

I need to create an excel file containing two cascading dropdowns list filled with dynamic data.

I'm wondering if with XlsIO it's possible:

1.  to assign a name to a range
   
2. Insert a data validation rule for allowing dropdown from a list
3. create a second dropdown linked to the selection of the previous list

What I'd like to do, using entirely XlsIo and the dynamic datasource I have, is described here:

https://www.extendoffice.com/documents/excel/5301-excel-cascading-drop-down.html

Please note that the data that will fill both dropdowns is not known, it's dynamic, and it comes from a database always changing, so I don't know how many records I have for the first dropdown, and how many records for the second one.

[Marco Giorgi]

found the solution as it was pretty well documented on the Syncfusion knowledge base:

https://www.syncfusion.com/kb/8367/how-to-create-a-dependent-dropdown-list-for-whole-column-in-excel-using-xlsio

//Defining the Named Range

IName lname1 = sheet.Names.Add("cities");

//Associate the name to the range

lname1.RefersToRange = sheet.Range[98,4,98,5];

Then create the validation as explained in the link above using for the second dropdown the function 

 =INDIRECT(A2)

or

 =INDIRECT(SUBSTITUTE(A2," ","")) if you have space in the text of the first named range

[Marco Giorgi]

but there still a problem with the SCOPE of the named range

I define all the data in the sheet2.

When I use XLSIO to define the named range, using the code posted before, the scope is set to sheet2

When I do directly inside Excel, the scope is set to workbook

(To view the scope of the Name Manager, under the Formulas menu)

So, how can I set the scope of a named range?

[Marco Giorgi]

found it, and it was very easy:

instead of   IName lname1 = sheet.Names.Add("cities");

let's use IName lname1 = myWorkbook.Names.Add("cities");

[Dilli Babu Nandha Gopal]

Hi Marco, 

Greetings from Syncfusion. 

Yes, the followed approach is the right way to set named range to workbook and worksheet. To know more about named range please following the below link. 

Documentation link : https://help.syncfusion.com/file-formats/xlsio/faq#how-to-use-named-ranges-with-xlsio  

Regards, 

Dilli babu.