Phpspreadsheet: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Riepilogo!B2 -> Riepilogo!B4 -> Formula Error: An unexpected error occured

[wolly66]

This is:

• [x] a bug report
• [ ] a feature request
• [ ] not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

I load an xlsx template
I put some test data in cells
Write the new file

What is the current behavior?

When reading, get: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Riepilogo!B2 -> Riepilogo!B4 -> Formula Error: An unexpected error occured

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

To reproduce this bug, first you need to solve the ISSUE #181 as I suggested.

The excel formula is: =SE(SOMMA(B4:B80)='Liste presenze'!K8;" ";"ERRORE") in english =IF(SUM(B4:B80)='Liste presenze'!K8;" ";"ERRORE")
To test you need an external excel file, that i attach

<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//Load the file template
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('tabellaperassociazioni.xlsx');

$worksheet = $spreadsheet->getActiveSheet();
//insert test data in some cells
$worksheet->getCell('C3')->setValue('API GENOVA');
$worksheet->getCell('C5')->setValue('22/06/2017');
$worksheet->getCell('A11')->setValue('Messina');
$worksheet->getCell('B12')->setValue('Dario');
$worksheet->getCell('C11')->setValue('Italiano');
$worksheet->getCell('H11')->setValue('ARENZANO');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
//write the file
$writer->save('comunicazione.xls');

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet = develop
PHP 7.0
tabellaperassociazioni.xlsx

[JosHonig]

Hi there, I think i have the same issue.
when i use a formula with a reference to another sheet, it fails.

after i change the formula to a reference on the same sheet, it will pass... I think you are using different sheets also?

[wolly66]

Yes, in the template there are 2 sheets.

I solved, deleting the second sheet :-) and copying and pasting the first sheet, the one dinamically generated :-)

I know, it's not a solution, but it works :-)

[PowerKiKi]

@JosHonig if you are able to pinpoint exactly what goes wrong and find a fix, that would be very appreciated. Maybe the first step for that would be to reproduce the issue without external file, by creating a spreadsheet by code only.

[wolly66]

@PowerKiKi Ok, I'll do

[JosHonig]

the line below gives an error
=vlookup(A1,Sheet1!A:B,2,false)

and this one is working
=vlookup(A1,Sheet1!A1:B99999,2,false)

so refering to columns on another sheet is not working for me... (both creating from scratch and creating new)

[PieterCappelle]

Any update on this? Having the same problem with vlookup.

[PowerKiKi]

Nothing new from me unfortunately. I'm afraid my last comment still stands and one of you will have to debug it further to find a fix

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[enoola]

same issue here, when we are refering to another sheet in a formula =COUNTIF(Data!K:K;">=30"),
we get the issue reported :/

[dwainscheeren]

I also still got this error when refering to another sheet in the same file

[Peterbins]

Still Same here :

works:
$sheet->setCellValue('D15', "=SUM('name sheet!AL1:AL99999)");

error :
$sheet->setCellValue('D15', "=SUM('name sheet!AL:AL)");

[MarkBaker]

Column and Row Ranges have never been supported in formulae by the PHPSpreadsheet Calculation Engine; I'm currently working on them in the new replacement engine, but this is a complete and total rewrite, and takes time

[janchimugica]

Any updates on this? :)

[MarkBaker]

Most of the examples here are using column or row ranges, which are not cleanly supported by PHPSpreadsheet's calculation engine; references to other sheets in formulae should work, unsupported column or row ranges do not, whether in the same sheet or another sheet

[arimourao]

Unfortunately this is still a living issue. Reference to other sheet in the same file crashes. It is not out of column or row ranges. Any workaround this?

[arimourao]

The error seems to be when saving the file, the PHPSpreadsheet tries to read the formulae. I saw that on PHPExcel, lots of people had this problem but were able to solve it calling the method $objWriter->setPreCalculateFormulas(false);

Is there anything like this for PhpSpreadsheet? Couldn't find in the docs.

[kashifumar]

ok, found the issue, this happens when the formula in some cell is invalid, that would in normal excel case give an error or in some case excel auto-corrects it so you cant re-produce the issue in excel in some case. in my case, some cells contained values like
=CONCATENATE("+", "2.")
which evaluates to +2. and there cant be a decimal at the end of the value in the cell

So debug your data

[luckdragon]

so, why is it on the OLD PHPExcel I was able to read the formula: =VLOOKUP(A3,'Current Inventory'!A:B,2,FALSE)
but on PhpOffice (which is supposed to be better), I get an exception and have to change my formula to look like:
=VLOOKUP(A3,'Current Inventory'!A1:B9999,2,FALSE).. this becomes a real pain for an existing spreadsheet with 5 worksheets and at least 5000 lines on each sheet..

isn't there a simple way to edit the code to say something like..

  explode on ":" 
     if there's no cell # on the first result then make it minRow, 
     if there's no result on the 2nd result then make it maxRow?