BUG: Vlookup with an Indirect returns a #REF value

Aspose.Cells Product Family
1

Hello,


We have numerous formulas in one of our excel models that use a formula like the following:
=VLOOKUP(G8,INDIRECT(G7),2,0)

This scenario returns #ref values with Aspose.cells 7.2.0 can you please advise? I have attached a test workbook as well as a screen capture of this workbook after calling Aspose and trying to get that value.

The named range Test_Value holds the formula pasted above. In G8 is a value of 3 and from my table in A1:B3 it should evaluate to a value of 2.

Thanks,
Mark
2

Hi,

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

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.1
and let us know your feedback.

I think, you have attached the wrong test file. Because I cannot see the table as you mentioned in your screenshot.

Please see the screenshot below of your attachment.

Screenshot:

3

You are correct sorry that was a test workbook from my last bug. I have attached the correct one this time but i see you might have a fix. I will check out the fix today and let you know if that worked out.


Thanks,
Mark
4

This is still throwing an error. Please use my updated test document that i attached in my last post and it will throw the same error. I have attached a screen shot from my last run with the 7.2.0.1 update.


Thanks,
Mark
5

Hi,

I was not able to replicate the bug using the latest version:
Aspose.Cells
for .NET v7.2.0.1

Please provide me your test code to replicate this issue.

Below is my test code and see the output file, it is ok.

C#


string filePath = @“F:\Vlookup+with+indirect.xlsx”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();

workbook.Save(filePath + “.out.xlsx”);


6

Sorry if i misspoke or misguided with the screen shot. The screen shot was created from the workbook in memory using c#.


It is not the output file that i am concerned with it is the value that i am getting from the named range. You are correct the output is always fine as excel correctly evaluates the formula. The issue is getting the value from the named range. 
                    var range = _workbook.Worksheets.GetRangeByName(cell.Text);

if (range == null) return;

                value.Text = range.Value.ToString();



The value.Text will contain #Ref with the 7.2.0.1 version.
7

Hi,

Thanks for your feedback.

I was able to replicate the problem using the code below. Below is the output image as you can see, it is not showing correct values.

We have logged this issue in our database. Development team will look into it and fix the problems. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40599.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Vlookup+with+indirect.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.OnePagePerSheet = true;


SheetRender sr = new SheetRender(worksheet, opts);


Bitmap bmp= sr.ToImage(0);


bmp.Save(filePath + “.out.jpg”);


Output Image:

8

Hi,

The value of the cell “G7” is "test ", could you remove the latest space char and change it as “test”? It can work fine.

And we will fix this issue soon.

9

Hi,

Please download and try the latest version: Aspose.Cells for NET v7.2.0.2

We have fixed the issue of calculating formulas.

10

I will pull this down and test. The removal of the space did fix my test document but not the main model that we are using. The document i had was a simple version of what i thought was causing the bug. There is still something wrong with the main workbook. I have asked to get a stripped version of it to remove any proprietary items.

11

Hi,


Thanks for your screen shot.

Please provide us your template Excel file to show the issue on our end. We will look into your issue soon.

Thank you.
12

I have created a private post with everything needed.


Thanks,
Mark
13

Hi,

Thanks for your sample project and the source files.

We were able to replicate the problems with calculation so we have logged your issues in our database.

Please follow your other thread.

Vlookup with an Indirect returns a #REF value

14

Any news on this issue? I have a client that is awaiting the fix.


Thanks,
Mark
15

Hi,

Please download and try this fix: Aspose.Cells for .NET 7.2.0.3

We fixed your second issue mentioned in your other thread:
<a href="

16

Thanks this fixed that problem.

17

Hi,

It’s good to know your issues have been fixed.

I have closed your tickets, if you encounter any other problem please feel free to post, we will help you asap.

18

The issues you have found earlier (filed as CELLSNET-40599) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.