如何在PHPExcel中使用INDIRECT进行求和

Question

I need PHPExcel to sum fields form G31 to field upon my total field (G-the-number-depends). A formula

=SUM(G31:INDIRECT(ADDRESS(ROW()-1;COLUMN())))

which normally works, when passed to PHPExcel changes on the output into

=SUM(INDIRECT(ADDRESS(ROW()-1;COLUMN())&": G31"))

which doesn't. My syntax in PHPExcel is:

$objPHPExcel->getActiveSheet()->setCellValue('G32', "=SUM(G31:INDIRECT(ADDRESS(ROW()-1;COLUMN())))");

Where am I wrong? Thanks.

Answer 1

The reference to the cell should be in the INDIRECT statement, not in SUM and mustn't contain space. That is

=SUM(INDIRECT("G31:"&ADDRESS(ROW()-1;COLUMN())))

on the output which is triggered by

$objPHPExcel->getActiveSheet()->setCellValue('G32', "=SUM(INDIRECT(\"G31:\"&ADDRESS(ROW()-1,COLUMN())))");

in PHPExcel. This works.