problem with getting cell values from another worksheet...

Topics: Developer Forum, User Forum
Sep 14, 2010 at 3:09 AM

Does anyone has the same problem as mine when getting a cell values from another worksheet with a if statement for validation and it can not work out. here is sample code:

$objPHPExcel->getActiveSheet()->setCellValue('C'.$cns, "=if('$worksheetname'!D".$cns."="","",$worksheetname'!D".$cns.")")

without the if statement, it works, but return 0 value if the cell pointed contains nothing. 

so i added a if statement to verify the cell, but it turns out nothing page can't open.

Please help!


Sep 14, 2010 at 2:46 PM

Processing this through the Evaluator tool in Excel, the 0 value is the correct return for Excel when a cell is blank, but the referenced worksheet still exists: if the referenced worksheet doesn't exist, then Excel will return a #REF! error. By default, this will be displayed as a 0. So the result for a formula of =IV65535 will display as 0 in Excel if IV65535 isn't populated. Likewise, if sheet2 exists, then the formula =Sheet2!IV65535 will return a 0; but =Sheet999!IV65535 will return a #REF! error (assuming Sheet999 does not exist)

The behaviour of PHPExcel isn't strictly correct here. If the referenced cell is in the current worksheet, then PHPExcel correctly returns a NULL; but it always returns a #REF! error if the cell reference is in another worksheet, but doesn't exist, even if the referenced worksheet itself is valid. This behaviour needs modifying to return a NULL if the worksheet exists, and only to return a #REF! error if the referenced worksheet doesn't exist

Sep 14, 2010 at 2:47 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Sep 15, 2010 at 2:38 AM

Hi MarkBaker,

Thanks for the reply.

I have explained some more on this problem on the work item you have referenced.

Please have a check!