Formula error mixing SUM() and INDIRECT()

[Giulio]

Spreadsheet::WriteExcel 2.36

I have a problem using INDIRECT() as an argument of a SUM() function.

== test script
#!/usr/bin/perl
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('1.xls');
my $worksheet = $workbook->add_worksheet();
$worksheet->write('A5', '=SUM(A1:A4)' );
$worksheet->write('B5', '=SUM(A1:INDIRECT("A4"))' );
$worksheet->write('C5', '=SUM(A1:INDIRECT("A"&ROW()-1))' );
$workbook->close();
==

When executed it produces the following error:
Unknown defined name SUM in formula at myscript line 6.

I understand that "unknown defined name" is a general error when a formula
cannot be parsed.

I was hoping there might be some trick to "force" it to be parsed (ie:
adding spaces?, quoting in a special manner?).

The reason I need to use INDIRECT is because due to the way the data is
extracted/produced the script using WriteExcel is just converting data from
a custom tabulated format (containing, numbers, strings, formulas) into
WriteExcel format, and so it doesn't actually know anything about the data,
it just translates into write_string, write_formulas something it's fed by
something else which uses this formula.

Thanks.

=======
Perl version : 5.008008
OS name : linux
Module versions: (not all are required)
Spreadsheet::WriteExcel 2.36
Parse::RecDescent 1.94
File::Temp 0.16
OLE::Storage_Lite 0.19
IO::Stringy 2.110
--
giu...@pobox.com

[jmcnamara]

On Aug 24, 5:34 pm, Giulio <giul...@gmail.com> wrote:
> Spreadsheet::WriteExcel  2.36
>
> I have a problem using INDIRECT() as an argument of a SUM() function.
>
> == test script
> #!/usr/bin/perl
> use Spreadsheet::WriteExcel;
> my $workbook  = Spreadsheet::WriteExcel->new('1.xls');
> my $worksheet = $workbook->add_worksheet();
> $worksheet->write('A5', '=SUM(A1:A4)' );
> $worksheet->write('B5', '=SUM(A1:INDIRECT("A4"))' );

Hi,

Unfortunately the Spreadsheet::WriteExcel parser cannot handle
formulas like that.

Spreadsheet::WriteExcelXML can handle them though if you can use that.

John.
--