Hi.
On 2014-03-28 03:58, MBB wrote:
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
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.