Formula results in #NAME? when referring to a sheet scoped name

Aspose.Cells Product Family
1

Hi,


Using Version 7.4.3.0 for .NET.

When opened in GridDesktop,
a workbook containing a formula like =‘sheet1’!RangeName returns #NAME?

=GlobalRangeName works.

I have attached a workbook to show example.

JM
2
Hi,

Thanks for the sample file.

After an initial test, using your template file I can notice the issue as you have mentioned. The global named ranges are working fine when the template file is loaded into GridDesktop. The local/worksheet scoped named ranges are not working.

I have logged a ticket with an id "CELLSNET-41679" for your issue. We will look into your issue.
Once we have any update on it, we will let you know here.

Thank you.
3

Hi,

Thanks for your posting and using Aspose.Cells for GridDesktop.

We have fixed this issue logged as CELLSNET-41679.

Please download and try this fix: Aspose.Cells for GridDesktop v2.7.18.2001 and let us know your feedback.

4

Hi,


Thanks for posting this fix.

However, the problem is still there when the name is contained in a formula such as
=CountIF(RangeName,“Criteria”)

I did not try many others but =SUM and =COUNT have the same issue.

Also noticed in reviewing the fix:

- the CountIF function is case sensitive while the one in Excel is not.
- the “General” format hides numbers (returned from formulas or even directly entered in a cell)

I am posting an updated workbook as exhibit…

JM
5

Hi,


Thanks for the updated file.

After an initial test with latest fix Aspose.Cells.GridDesktop v2.7.18.2001, I can notice the issue with your newly attached file. I have logged your feedback and comments there against your existing issue into our database. We will look into it to figure it soon.

Thank you.
6

Hi,


I have other issues occuring with formulas that have references to named cells within the same worksheet:

Let’s imagine a sheet named Sheet1 with a cell named “MyCell”

=MyCell returns #NAME?
=Sheet1!MyCell works

Thanks,

JM
7

Hi,


As we have already logged that (issue id “CELLSNET-41679”) the global named ranges are working fine when referencing. The local/worksheet scoped named ranges are not working properly as you have pointed out. Anyways, we have logged it as the worksheet scoped named ranges should also work fine when referring to it in the formula by simply using its name (rather than calling it with respect to its sheet name) in the same worksheet. We will also look into it as well.

Thank you.
8

Hi,


Please try the attached latest fix/version: Aspose.Cells.GridDesktop v2.7.18.2002 (attached), your issue should be fixed in it.

Thank you.
9

Works… Thanks!


There is still the issue with the formula referring to a named cell within the same worksheet (without specifying the sheet name) but that can easily be worked around.
Others might want this to be fixed though… But for our current requirements, the fix will provide just what we need.

Thank you.

JM
10

Hi,

Thanks for your posting and using Aspose.Cells for GridDesktop.

We are pleased to know that your issue is fixed with the latest version. If you encounter any other issue, please feel free to post on our forums, we will be glad to help you further.