On Fri, Jan 9, 2015 at 3:51 PM, <darren@xxxxxxxxx> wrote:
I confess that I've never tried the INDIRECT function before. I just read
about it, and that really provides for some interesting abstraction.
It is indeed cool. And relatively advanced. This is
"programmer-level" Excel functionality.
Or is this more of an Excel
formula question, and less of an RPG one?
I strongly suspect this is the case.
Bill Roehmer writes:
what I don't know is
what it takes to make =AVERAGE(INDIRECT("'"&A6&"'!F6:$F$18")) work.
And earlier, he gave this example of the desired formula:
SUM(INDIRECT(\"Sheet2! B1 : C3 \"))
Bill, I think you may be misunderstanding INDIRECT, and perhaps
overthinking Excel formulas in general. Or getting confused between
what parts are getting handled by your program and what's being
handled by Excel.
You almost never need INDIRECT. If you think you do, it will probably
help if you give us a more detailed explanation of your use case. The
way you're attempting to use it is definitely not going to work,
because it's not a valid Excel formula.
I suggest taking a step back and just thinking about what you would
put into Excel if you were doing it by hand. If you have a 6-cell
range, from B1 to C3, and you want to get its sum, what you'd enter in
Excel is
=SUM(B1:C3)
If that range is on Sheet2, but you're entering your formula on a
different sheet, you'd do
=SUM(Sheet2!B1:C3)
So what you need to pass to ss_formula as the third parameter is the
string which contains that formula (without the leading equals sign).
In other words, you want to pass the string
'SUM(Sheet2!B1:C3)'
Note that the single-quotes above are RPG's string delimiters, not
part of the data itself. The first character of the string is 'S' and
the length of the whole string is 17.
Try taking it from there.
In general, you'll want to build formulas by manipulating strings at
the programming language level (RPG in your case). INDIRECT is for
Excel power users who don't have another programming language to work
with. Excel *is* their programming language, so to speak.
John Y.
midrange.com
