Re: [libreoffice-users] Re: Calc: Cell Reference Duplicate?

On 06/02/2012 12:51 AM, Andreas Säger wrote:
> Hello John,
>
> Go to Tools>Options>Calc>General and turn on the option "expand 
> references ...".
> Now you can insert new rows anywhere in the list and directly below 
> the list and all references in formulas, charts, conditional 
> formatting, validation formulas and form controls will follow the new 
> dimensions. SUM(A1:A199) becomes SUM(A1:A200) after insertion of new 
> cells anywhere between row #1 and row #2.
> You may define a shortcut to insert an entire new row into the sheet.
> You may use a macro of mine which takes care of the above mentioned 
> option and a few other things: 
> http://user.services.openoffice.org/en/forum/viewtopic.php?f=21&t=2350
> The problem would not exist if you would collect all data in a 
> database table which grows and shrinks as you add and remove rows. All 
> references would always refer to the correct amount of rows calculated 
> by the database program or imported into Calc.
>
>
> Hope this helps
Good Morning

I went to the 'expand references' that you mentioned.  It has to do with 
adding cols or rows and the formulas adjusting for the change.  I did 
not make my self completely clear.  I create the checking page with 
balance, cash flow, others calculations down to line 600.  At each 
month, I break that to allow for totals, and I leave a blank line to 
show where that month ended.   In my income statement page, I want to 
reference the start and end lines for each month as they are created, so 
instead of editing a bunch of formulas each month, I want the formula to 
get its line number for a month from my month/start/end chart.  I am not 
adding lines to the checking page as I enter checks, I am editing 
existing lines.

John


--
For unsubscribe instructions e-mail to: users+help@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