PHPExcel error when reading calculated value

Question

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

Possible duplicate of PHPEXCEL getCalculatedValue or getFormattedValue returns fatal error — Laposhasú Acsa
– Laposhasú Acsa, Commented May 3, 2017 at 6:56
So what does cell LU81 contain? That's the cell that it's complaining about — Mark Baker
– Mark Baker, Commented May 3, 2017 at 7:14
I will look at that post and update my code, I will update this thread as soon as I do — vick
– vick, Commented May 3, 2017 at 7:22

Mark Baker · Accepted Answer · 2017-05-03 12:01:25Z

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

answered May 3, 2017 at 12:01

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:26:13Z

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.)

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered May 3, 2017 at 6:56

Laposhasú Acsa

1,59017 silver badges18 bronze badges

1 Comment

vick Over a year ago

ok, I will read that post and try it in my code and delete this topic if it solved it thank you

Collectives™ on Stack Overflow

PHPExcel error when reading calculated value

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related