PHPExcel read formula with references to another spreadsheet

Topics: Developer Forum
Sep 3, 2012 at 10:02 AM
Edited Sep 3, 2012 at 10:03 AM

I use PHPExcel 1.7.6. I currently encounter a problem with a formula.
The formula in question:

$formula = '=IF(ISNA(VLOOKUP(F:F,References!$A$1:$B$4,2,FALSE)),"",VLOOKUP(F:F,References!$A$1:$B$4,2,FALSE))';

I write in English in my PHP code for the locale defaults to 'en_us'. When I open my Excel file (in French), the formula is correctly written in French and it works.
My problem is when I try to read the generated Excel file via the command:

$this->objPHPExcelSheet->getCellByColumnAndRow($column, $row)->getCalculatedValue();

I get the value "#REF!". It can not calculate the formula, because there is a reference problem. In fact, my formula uses another spreadsheet called "References".
Can you help me?

Thank you very much.

Sep 3, 2012 at 10:25 AM

Technically this is correct behaviour, because PHPExcel cannot calculate a formula that references data that it is unable to access.

A possible solution for this was included in the 1.7.7 release, where (if it encountered an external reference in a formula) it would fall back to the last calculated value from when the file was accessed in MS Excel itself. This is not a guaranteed solution, as it is possible to disable autocalculation in MS Excel, or if the external spreadsheet had subsequently been updated) but it is a best possible solution for this issue.

Sep 3, 2012 at 10:33 AM

Thank you for your quick response! So if I understand correctly, PHPExcel is unable to access cells from another spreadsheet in a formula?

Sep 3, 2012 at 11:48 AM

That isn't quite what I said, but I also misread your question. I thought you were trying to reference data in another workbook, not another worksheet.

You're referencing another worksheet within the same workbook: that should work correctly. If it isn't doing so, then you can enable formula debugging to identify where it is failing.


PHPExcel_Calculation::getInstance()->writeDebugLog = true;
$result = $this->objPHPExcelSheet->getCellByColumnAndRow($column, $row)->getCalculatedValue();

This will generate a log showing each step of the calculation


Sep 3, 2012 at 12:41 PM

Thank you very much!it helped me to find my mistake.

The problem was not a reference to another spreadsheet in the same workbook. The problem here was the range "F: F". In my debug log, I recovered each time the value of the cell "F1" and not the "F" value of the current line. After a few corrections from right to left in my code, it works very well.

Thank you!