Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

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:
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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.