RE: Poi linking to other spreadsheets -- RPG400-L

Darrell,

 Oops - I didn't realize you already used formulas. Good on ya'.

 An Excel formula is just a string evaluated at runtime - as long as the
sheet & cell references are correct, Excel is happy. I'd never had the
occasion to link to another sheet but this looked interesting so I just ran
a test and it worked OK for me.

 My test consisted of the following: I already have an RPG that creates a
multi-sheet .xls, so I let the program create the 1st sheet (called
"Overview") and used the debugger to halt the program immediately before
writing a formula in the 2nd sheet (called "Detail"). I substituted the
following string: "Overview!C13+Overview!D13+Overview!E13" for the valueChr
field and then executed the hssf_formula( thisRow: Cell : %trim(valueChr) :
StyleHdr) statement. Of course, "C13", "D13" and "E13" are valid cells in
the "Overview" sheet. Sure 'nuff, the resulting .xls had a cell on the 2nd
("Detail") sheet that summarized C13, D13 & E13 from the 1st ("Overview")
sheet. Cool!

If you're summing cells, the following syntax works
"sum(Overview!C13+Overview!D13+Overview!E13)"

If you're summing ranges, the following syntax works
"sum(Overview!C13:E13)+sum(Overview!F13:H13)"

 The short story is that links work just like any other formula long as you
don't include that '=' sign at the front of the formula and you respect
Excel syntax. Dunno whether the sheet names, cells and ranges are hardcoded
or variable, but somehow they all need to be embedded in a string that is
passed to the 3rd param of hssf_formula.

gwp02_c    = hssf_cellname( rowcount: 2)
gwp12_c    = hssf_cellname( rowcount: 12)

somestr = +
  'sum(' + %trim(sheetname1) + '!' + gwp02_c + ':' gwp12_c + ')' +
  'sum(' + %trim(sheetname2) + '!' + gwp02_c + ':' gwp12_c + ')' +
  'sum(' + %trim(sheetname3) + '!' + gwp02_c + ':' gwp12_c + ')' +
  'sum(' + %trim(sheetname4) + '!' + gwp02_c + ':' gwp12_c + ')'

hssf_formula( row: 14: %trim(somestr): Numeric);


 Note: Excel appears to treat the following commands the same:
"=Overview!C13+Overview!D13+Overview!E13"
"=sum(Overview!C13+Overview!D13+Overview!E13)"

JK

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of DLee@xxxxxxxx
Sent: Wednesday, July 19, 2006 4:39 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Poi linking to other spreadsheets

JK

Thanks for your reply.  I currently also use Klements stuff, and have done
several workbooks using formulas, and have really enjoyed using the
product.

My problem this time, is I want to link the cells in several sheets, in
the same workbook and bring the total of those cells to a cell in the
final sheet.

NAIC10, NAIC12, NAIC14, NAIC16 are the sheets I want to link to.

Normally, I would do the following to get the cell address, and create a
formula.

gwp02_c    = hssf_cellname( rowcount: 2)
gwp12_c    = hssf_cellname( rowcount: 12)
hssf_formula( row:14: 'SUM('+gwp02_c +':'+gwp12_c +')':Numeric);

Since sheet names are involved, I don't know how to code it.

Below is an example of how the formula would look in sheet NAIC18, in cell
O9 using ms excel.
Notice in the example below NAIC10 is a sheet name, and O9 is a cell in
that sheet, and so on.

=(NAIC10!O9+NAIC12!O9+NAIC14!O9+NAIC16!O9)

Appreciate your help and comment.