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

Named range does not work (vlookup)

Thread ID:

Created:

Updated:

Platform:

Replies:

76781 Sep 23,2008 09:44 PM UTC Aug 30,2012 06:07 AM UTC WinForms 5
loading
Tags: XlsIO
Thomas Goettsche
Asked On September 23, 2008 09:44 PM UTC

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 [Syncfusion]
Replied On 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


Thomas Goettsche
Replied On 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





Geetha M [Syncfusion]
Replied On 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


Thomas Goettsche
Replied On 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




Sridhar [Syncfusion]
Replied On 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

CONFIRMATION

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.

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

;