Re: HSSF Indirect

Hello Scott,

Just wanted to thank you and John Young. The SS_formula works as
suggested. Just by
putting the quotes in the right place as you suggested gave me a perfect
result.
It correctly sums and averages an associated sheet's columns.



Bill Roehmer
Senior Pgmr/Analyst
The C.D. Hartnett Company





From: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>
Date: 01/09/2015 09:45 PM
Subject: Re: HSSF Indirect
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



Like Darren said... ss_formula() should work with any formula you can
insert in Excel (except, without the leading = sign)

So you should be able to do something like:

ss_formula(row: 3: 'AVERAGE(INDIRECT("''"&A6&"''!F6:$F$18"))': numeric);

but I haven't a clue what that formula does... I'm just copying it from
your e-mail (and doubling-up the single quotes.)



On 1/9/2015 2:04 PM, broehmer@xxxxxxxxxxxxxxx wrote:

Hello Scott;

Yes. Yes. Yes. Yes. No.
Just looking for an example that I can get that works.
Right now I'm building the spreadsheet that has multiple
sheets and each has to summarize to the front sheet.
I just this minute finished converting the HSSF to XSSF so
that I can use your example for start and end. Now
I'm diving into the formula. If I understand from your
example that I need ss_formula(row: 3: 'AVG('+start+' : '+end+')'
Numeric);
will average a start and stop for columns but what I don't know is
what it takes to make =AVERAGE(INDIRECT("'"&A6&"'!F6:$F$18")) work.



Bill Roehmer
Senior Pgmr/Analyst
The C.D. Hartnett Company


"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the

attachments accompanying it) contain confidential information belonging to
the sender. The information is intended only for the use of the intended
recipient. If you are not the intended recipient, you are hereby notified
that any disclosure, copying, distribution or the taking of any action in
reliance on the contents of the information is strictly prohibited. Any
unauthorized interception of this transmission is illegal under the law.
If you have received this transmission in error, please promptly notify
the sender by reply e-mail, and then destroy all copies of the
transmission."