At 17:57 11/04/2009 -0700, John R. Sowden wrote:
I have a accounting journal broken by months with totals per
month. I want to create a report by gen led account per month.
The starting and ending lines per month vary, so I set up a chart:
Jan start
Jan end
Feb Start
Feb End
with the line number in each cell. I want to put these values into
the formula which sums the amounts per gl account, within these 2
line numbers. How do I get the value in the
cell to be part of the =sum(month.start:month.end) formula.
This is one of those problems that are very easy once you know
how. You have probably got as far as constructing a text string
which looks just like the expression that you need as the argument of
the SUM() function, but if you use this directly, Calc will treat it
just as a text string and not the cell range reference that you
need. The missing link is provided by the INDIRECT() function, which
interprets the string as the required cell range reference.
Suppose your actual data is in column A, the month names in column B,
and the row numbers in column C. If the January start and end values
are in rows 1 and 2, you can sum the January data with something like:
=SUM(INDIRECT("A"&C1&":A"&C2))
The numeric values in C1 and C2 are automatically converted to string
values as are required by the concatenation operator &.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
