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

Named range does not work (vlookup)

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,




5 Replies

GM Geetha M Syncfusion Team September 24, 2008 06:50 AM UTC

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



TG Thomas Goettsche September 24, 2008 08:22 AM UTC

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






GM Geetha M Syncfusion Team September 24, 2008 09:30 AM UTC

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



TG Thomas Goettsche September 24, 2008 12:02 PM UTC

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





SR Sridhar Syncfusion Team August 30, 2012 06:07 AM UTC

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

Loader.
Live Chat Icon For mobile
Up arrow icon