[libreoffice-users] Re: spreadsheet question

Am 07.06.2012 19:48, Eric S. Johansson wrote:
> I'm using a spreadsheet to track a project (yes I know not a good idea
> but that's what the customer wants).

Worst idea of millions of spreadsheet fools, indeed.

Each task is listed with certain
> number of days plus a slip factor. I calculate the project completion
> date by summing the number of days and the slope factor and add that to
> the previous date (which may have slipped which is why I reference the
> next task off of that date).
>
> How can I encapsulate the time calculations so that I use the same cell
> equation for all of them?

Well, the user needs to copy down the formula for each new "database 
record". That's what spreadsheet addicts do since 20 years.

 The patter I'm currently using is letter -1
> and letter -2 (number of days for project plus days of slippage) plus
> date in number -1 (up one cell).
>
> another question is if I insert a new row because I'm adding a task, or
> delete a row, how can I make the total display automatically without
> having to tweak the cell numbers?is there any form of symbolic reference?
>
> Many thanks
> --- eric

Spreadsheet default across several application is as follows:
Having a list in row #1 to row #99, all references expand when you 
insert cells between row #2 and row #99.
SUM(A1:A99) becomes SUM(A1:A100) in _all_ formula expressions of the 
same document (cell formulas, named references, database ranges, 
validations, conditional formats, charts and form controls)

When you insert cells at row #1 all references will shift one row 
downwards.
SUM(A1:A99) becomes SUM(A2:A100) in _all_ formula expressions of the 
same document.

When you insert cells directly below the list at row #100 the references 
won't be affected.
SUM(A1:A99) remains SUM(A1:A99) in _all_ formula expressions of the same 
document.

With global option Tools>Options>Calc>General: "Expand references ..." 
you get kind of "list keeping mode".
SUM(A1:A99) becomes SUM(A1:A100) in _all_ formula expressions of the 
same document even when you insert cells at row #1 or directly below the 
list at row #100

If I understand you request correctly, you are a perfect candidate for a 
well tested and established macro of mine:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=21&t=2350
It turns "expand references" on, inserts into the current region rather 
than the entire sheet, copies down any formulas from the row above, 
selects the remaining blank cells for editing and turns off "expand 
references" if it used to be off before. A second macro does the 
analogue type of thing to delete rows.

Hope this helps


--
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