Named range does not work (vlookup)

[Thomas Goettsche]

I a 4-sheet workbook I define a named range in the fourth sheet to be used in a vlookup on the second sheet. The fourth sheet is supposed to have all validation and lookup ranges (named), something that works flawlessly in Excel.

IName lname1 = sheet_3.Names.Add ("rcl");
lname1.RefersToRange = sheet_3.Range["A2:C22"];

Everythings fine until the next line is executed:

sheet_1.Range["H" + (nq + 1).ToString()].Formula = "vlookup(G"+(nq+1).ToString()+",rcl,2,false)";

The vlookup is essentially e.g. vlookup(G1,rcl,3,false).

Named ranges are global for the workbook in Excel, so the code is Excel compliant. It just does not execute with XLSIO.

Of course, I could skip the named range and enter an ugly cell reference range but that sort of defeats the purpose of having a sheet in a workbook with all lookup range.

If the range has to be in the same sheet where it is usedI'd say that'd be a bug.

Am I missing something?

Thanks,

[Geetha M]

Hi,

Thank you for your interest in Syncfusion products.

You can add a NamedRange to the workbook using XlsIO. The following code snippet illustrates this:

IName lname1 = workbook.Names.Add("rcl");
lname1.RefersToRange = sheet_3.Range["A2:C22"];

And the above code worked fine for me.

Could you please try running the sample in the below link and let me know if you have any difficulties?

http://websamples.syncfusion.com/samples/XlsIO.Windows/F76781/main.htm

Regards,
Geetha

[Thomas Goettsche]

Hi Geetha,

Thanks.
The problem is not with defining the named range, the problem is with adding the vlookup to the sheet. When the line of code runs that adds the vlookup to sheet_1 I get an exception. The named range does not exist.

Excel would handle the vlookup nicely, XLSIO doesn't handle the writing of the formula.

Regards,

>Hi,

Thank you for your interest in Syncfusion products.

You can add a NamedRange to the workbook using XlsIO. The following code snippet illustrates this:

IName lname1 = workbook.Names.Add("rcl");
lname1.RefersToRange = sheet_3.Range["A2:C22"];

And the above code worked fine for me.

Could you please try running the sample in the below link and let me know if you have any difficulties?

http://websamples.syncfusion.com/samples/XlsIO.Windows/F76781/main.htm

Regards,
Geetha

[Geetha M]

Hi,

Thank you for the update.

I am afraid that I am not able to reproduce the issue when the named range is added to the workbook.

The sample in my previous update has a named range referring to the range in 'Sheet 1' and is referenced inside the VLOOKUP in 'Sheet 2' and 'Sheet 3'. Could you please try reproducing the issue with this sample and send me back?

Please let me know if I had misunderstood the requirement.

Regards,
Geetha

[Thomas Goettsche]

Hi,
It's working!
Thanks to my overlooking the difference between workbook vs. sheet range ... :(

Thanks a lot and sorry for not reading your reply carefully enough,



>Hi,

Thank you for the update.

I am afraid that I am not able to reproduce the issue when the named range is added to the workbook.

The sample in my previous update has a named range referring to the range in 'Sheet 1' and is referenced inside the VLOOKUP in 'Sheet 2' and 'Sheet 3'. Could you please try reproducing the issue with this sample and send me back?

Please let me know if I had misunderstood the requirement.

Regards,
Geetha

[Sridhar]

Hi Rotwang,

 

Thank you so much for the update.

 

We glad to know the above reported query is solved at your side and let us know if you require any further assistance on this.

 

Thanks,

Sridhar.S