0

I have a cell in an excel sheet that i am trying to read. When I use the function getCalculatedValue it's throwing following error:

Financials!LU83 -> Financials!LU81 -> Formula Error: An unexpected error occured

I wrote following function to read from a cell

private function getCellValue($data)
{

        //example of data variable
        //$data = [0, 'G79'];

        $excel = $this->excel;
        $excel->setActiveSheetIndex($data[0]);
        \PHPExcel_Calculation::getInstance($excel)->flushInstance();
        \PHPExcel_Calculation::getInstance($excel)->clearCalculationCache();
        return $excel->getActiveSheet()->getCell($data[1])->getCalculatedValue();

    }

that cell that I am trying to read has following value

=LU83+LT84

where LU83 has following value

=LU73-SUM(LU76:LU81)

LU81 has value

=VLOOKUP(LU8,'Wiser Return'!$O:$S,5,0)

I have no idea why I am getting this error. I wish there was a way to debug? Is there a way?

Any help is appreciated.

Thanks

vick
  • 476
  • 6
  • 18
  • 1
    Possible duplicate of [PHPEXCEL getCalculatedValue or getFormattedValue returns fatal error](http://stackoverflow.com/questions/29966752/phpexcel-getcalculatedvalue-or-getformattedvalue-returns-fatal-error) – Laposhasú Acsa May 03 '17 at 06:56
  • So what does cell LU81 contain? That's the cell that it's complaining about – Mark Baker May 03 '17 at 07:14
  • =VLOOKUP(LU8,'Wiser Return'!$O:$S,5,0) – vick May 03 '17 at 07:20
  • I will look at that post and update my code, I will update this thread as soon as I do – vick May 03 '17 at 07:22

2 Answers2

1

The problem is that PHPExcel's calculation engine does not fully support row or column ranges.

=VLOOKUP(LU8,'Wiser Return'!$O:$S,5,0)

contains the column range $O:$S

If this can be converted to a cell range instead, e.g

=VLOOKUP(LU8,'Wiser Return'!$O1:$S1024,5,0)

then it should handle the formula correctly

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
0

I can't comment now, but I found some link that might help you.

In this question the accepted answer states, that you can get further information about the error with this gist.

(I suggest deleting your question if this helped you.)

Community
  • 1
  • 1
Laposhasú Acsa
  • 1,550
  • 17
  • 18
  • ok, I will read that post and try it in my code and delete this topic if it solved it thank you – vick May 03 '17 at 07:22