Re: Spreadsheet::WriteExcel question

I don't think you can do this with repeat_formula. I believe you can only replace cell references with other cell references. Some evidence for this:

IV65536 is the cell reference for the last possible addressable cell. It seems that this is used whenever the replacement string cannot be parsed as a cell reference.
In the documentation for the module it states that repeat_formula() "does not modify the tokens." I suspect this means that the type of a token cannot be modified, only their names can be modified. So you can't change a cell reference into a constant.
Note that the most likely use of the repeat_formula feature of the Excel spreadsheet format is to accommodate the fill-up/down and fill-left/right operations. In those cases you are only replacing cell references with other cell references.

I would ask the maintainer of the module for confirmation of these ideas. As a workaround, you probably have to store the result of the calculation ($diff) in a cell and reference the cell.

Comment on Re: Spreadsheet::WriteExcel question Select or Download Code

Replies are listed 'Best First'.
Re^2: Spreadsheet::WriteExcel question by vlilich (Initiate) on Jul 01, 2008 at 20:59 UTC
figured it out... you can do number to number or cell reference to cell reference substitutions so that the following will work: `my $formula1 = $ws->store_formula('=(12*9999)+MONTH($L39-1)-MONTH(A92) ++1'); my $diff=111; $ws->repeat_formula($row, $col, $formula1, $fmt->{'number'}, qw/^9999$/, $diff, qw/^A92$/, 'A' . $current_row );` [download]	[reply] [d/l]