Sum of sub-section: Calc named row/column error (bug, feature or wrongfull implementation?)

users
#1

Hello

v4.2 (Calc made in Lo 4.0 or 3.6)

I have a spreadsheet where I list expenses by year, with subsections for
months that I want the sum of.

In LO Calc I named rows so I could use the OFFSET formula to make sure that
sum calculations would always include inserted rows. According to tutorials
this was the advised way for older pre-table Excel versions. I never used
Excel much, but I assumed this should work in Calc too.

=SUM((OFFSET(Feb2013!Overig;-1;0)):(OFFSET(Jan2013!Overig;1;0)))
Takes the sum between row Feb 2013 and jan2013 in column Overig

While the formula itself works, it's intended implementation does not.
Because when adding a column later, it gives a reverence error.
Because the name for the row is assigned from
$Uitgaven2013.$A$7:$AMJ$7
So when adding a column, it wants to more ^AMJ one up, except because the
entire row was named, that is of course the last column.
So that gives the name range an error, which is moved to the fomula, which
defeats the entire purpose.

(adding a row does not work either, but I have not yet tried to find out
why)

Does anyone know if I am just doing this wrong and knows where I can find
the Calc way, or if I should file a bug report for this?

Thanks in advance (and more after solving)

MBB

#2

Hi.

Hello

v4.2 (Calc made in Lo 4.0 or 3.6)

I have a spreadsheet where I list expenses by year, with subsections for
months that I want the sum of.

In LO Calc I named rows so I could use the OFFSET formula to make sure that
sum calculations would always include inserted rows. According to tutorials
this was the advised way for older pre-table Excel versions. I never used
Excel much, but I assumed this should work in Calc too.

=SUM((OFFSET(Feb2013!Overig;-1;0)):(OFFSET(Jan2013!Overig;1;0)))
Takes the sum between row Feb 2013 and jan2013 in column Overig

While the formula itself works, it's intended implementation does not.
Because when adding a column later, it gives a reverence error.
Because the name for the row is assigned from
$Uitgaven2013.$A$7:$AMJ$7
So when adding a column, it wants to more ^AMJ one up, except because the
entire row was named, that is of course the last column.
So that gives the name range an error, which is moved to the fomula, which
defeats the entire purpose.

(adding a row does not work either, but I have not yet tried to find out
why)

Does anyone know if I am just doing this wrong and knows where I can find
the Calc way, or if I should file a bug report for this?

Thanks in advance (and more after solving)

MBB

AMJ is the limit of the number of columns in calc, so I would not expect you to be able to insert a column.
If you have all the columns in your range, why do you need to insert a column. Can you just add the information into one of the existing columns (type in or paste)..
A solution if you need to insert columns may be to not select all the columns in your range, thus allowing room for expansion, i.e. have the range $Uitgaven2013.$A$7:$AZ$7 or as required for existing columns. Note that you can't insert a column into a named range at the end, you need to insert one before the end and sao include one more column than needed if you think you will need to append.
Steve

#3

Thank you for your advise.

Yes, I'll guess I'll have to do that, redefine the ranges to (a little more
then) the needed range, then it should probably work. I just wish I had
tested this sooner.

And the reason why I wanted to do this is simple; when you have a sheet like
this: the easiest way to insert new data 'C' inbetween column B and D is
inserting a new column rather then cut-pasting everything to the right - and
usually the savest way, but not this time.
And I'd named the entire rows because when you have more then one screen of
data, it is the easiest way to just click the row header (A) and define the
name in the Name-box on the left top.
None of the tutorials I used warned against the problem that when inserting,
it would drop the (unused) excells allright, but not move the formula along.

ABDEF
ABDEF
ABDEF
ABDEF
ABDEF