We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

VLOOKUP with setSharedFormula method breaks spreadsheet

When creating a spreadsheet using Aspose.Cells version 21.3 with a shared VLOOKUP formula (i.e. using the setSharedFormula method), Excel fails to open the spreadsheet correctly. The error message is:

"We found a problem with some content in ’VLookupTest.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

However, when creating a spreadsheet using Aspose.Cells version 21.3 without a shared VLOOKUP formula (i.e. using the setFormula method instead of the setSharedFormula method), Excel opens the spreadsheet no problem.

Is this a bug or am I doing something wrong? Here is my sample code:

final Workbook wb = new Workbook();
wb.getWorksheets().add();

final Worksheet ws1 = wb.getWorksheets().get(0);
final Worksheet ws2 = wb.getWorksheets().get(1);

final Cell ws2CellA1 = ws2.getCells().get(“A1”);
ws2CellA1.setValue(“BHX”);

final Cell ws2CellB1 = ws2.getCells().get(“B1”);
ws2CellB1.setValue(“England”);

final Cell ws1CellA1 = ws1.getCells().get(“A1”);
ws1CellA1.setValue(“BHX”);

final Cell ws1CellB1 = ws1.getCells().get(“B1”);
ws1CellB1.setSharedFormula("=VLOOKUP(A1,Sheet2!A:B,2,FALSE)", 1, 1); // THIS DOES NOT WORK
// ws1CellB1.setFormula("=VLOOKUP(A1,Sheet2!A:B,2,FALSE)"); // THIS WORKS

wb.save(“VLookupTest.xlsx”);

@gary.belcher,

Please notice, I am able to reproduce the issue as you mentioned by using your sample code. I found VLOOKUP formula (i.e. using as setSharedFormula method) corrupts an Excel file. I have logged a ticket with an id “CELLSJAVA-43423” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

1 Like

Thank you, @Amjad_Sahi. I look forward to your reply. :slightly_smiling_face:

@gary.belcher,

Sure, please spare us little time to evaluate your issue in details. Hopefully your issue will be figured out soon.

1 Like

@gary.belcher,
This is a limitation of ms excel, that is, ms excel takes it as invalid to use references to other sheets in the shared formula.

Thank you, @ahsaniqbalsidiqui. Is this a limitation of the most recent versions of Excel then? I’ve noticed that this works fine in Excel 2016 for example.

@gary.belcher,
We have noted your query and will write back soon to share our feedback.

@gary.belcher,

Could you also give us your working sample Excel file (where VLOOKUP works as desired), we will test it in MS Excel 2016.

@ahsaniqbalsidiqui @Amjad_Sahi Sure, please find a sample Excel file attached generated using my code above and version 21.3 of Aspose.Cells.

VLookupTest.xlsx.zip (7.8 KB)

In this sample Excel file, the VLOOKUP is in cell B1 in Sheet1, and it simply uses the value of cell A1 in Sheet1 to lookup a value from Sheet2. If you enter “BHX” it should find “England”. If you enter “DUB” it should find “Ireland”.

This file does not open correctly using the latest version of Excel, but it does open correctly using Excel 2016, and it does open correctly using the latest version of LibreOffice.

@gary.belcher,

I tried to open your attached file into MS Excel 2016 but I got the error, so the file is corrupted. see the attached screenshot.
sc_shot1.png (94.4 KB)

@Amjad_Sahi The file definitely opens for me using Excel 2016 for Windows. I will attach a screenshot shortly. The file does not open for me using Excel for Mac 16.47 (21031401).

@gary.belcher,

Please share screenshot for MS Excel 2016 (Windows) with version details.

@Amjad_Sahi Please find screenshots attached showing VLOOKUP working in Excel 2016 on Windows.

Lookup1.png (45.4 KB)
Lookup2.png (45.4 KB)
Version.png (46.8 KB)

@gary.belcher,

Thanks for the screenshots.

We have recorded your provided screenshots against your issue into our database. We will evaluate your issue further and get back to you soon.

@gary.belcher,

We found when using some older versions of MS Excel to open such kind of file, no error message pops up. However, even in those versions of MS Excel, you cannot manually generate such kind of shared formulas as MS Excel will only create ordinary formulas for those cells one by one. We are afraid, we cannot solve the issue because it is MS Excel’s limitation and you cannot create such kind of shared formulas manually if you need to generate similar resultant Excel files.

@Amjad_Sahi Thank you for the update and taking the time to investigate this. I guess the solution then is to use the setFormula method instead for these kind of formulas instead of the setSharedFormula method. :+1:

@gary.belcher,

Yes, you may set formulas separately for those cells in the worksheet. In the event of further queries or other issue, feel free to write us back.