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

While what Tom said about databases versus spreadsheets is largely
true, it is also true that a lot of people use spreadsheets as a sort
of report, without the database bit. This is often easier to put
together, especially for people that don't have the know-how to develop
a database application complete with reports, and sometimes easier to
maintain, especially in situtations where the requirements change
often, even if only slightly.

And what the OP asked for should be fairly trivial to implement, at
least for a simple case. I don't think it'll ever be possible to see a
proper "schema", not given how flexible spreadsheets are, but seeing
some sort of cell dependencies should be possible.

And in fact, looking through Calc, I have found exactly that.
Under "Tools | Detective " you have both "Trace Precedents" and "Trace
Dependents" which show you what cells a given cell depends on, and what
cell depend on a given cell. This should be most of what the OP needs.
You can use "Fill Mode" to select multiple cells, but it doesn't seem
to work on a whole page at a time, nor very well across pages.

What would be better is a filterable list of formulae, and given that
all non-empty cells have to be saved in the .ods file, it must surely
be relatively trivial to pull out a list of formulae (and any other
dependencies that it might be possible to make), present that list in a
filterable manner, and allow the user to click to see the depender and
dependee of the formula in some fashion, by highlighting the cells or
double-clicking to go to them. At least that way you could, for example,
pull up a list of formulae in the current spreadsheet, filter that list
by one of the worksheets, and see all the formulae that depend on the
worksheet name, or on a specific column in the worksheet, etc. This may
not allow one to get a grand overview of how the data hangs together
like with a database schema, but that is the price you pay for having
the flexibility to have the data not hang together in a specific way.
It would allow one to check how certain pieces hang together, and to
establish before making changes what the effects might be.

Just my thoughts


On Wed, 30 Jul 2014 09:53:54 +0100
Tom Davies <> wrote:

Hi :)
I seriously doubt it!  It is database functionality.

It would be like expecting a word-processor to have DTP
functionality, or a text-editor to do the same things as a

One option might be to get Base to read the tables as it's external
back-ends.  That would get you to the same screen showing all the
different tables/work-sheets and their headings.  However since the
calculations take place within the work-sheets Base wouldn't show any
links between the tables just yet.

Although you don't see the links directly it might help you figure
out the obvious ones and a print-out (or screen-shot) of that screen
might help you be able to draw in the rest as you figure them out by
hunting through the worksheets.

This process might help you figure out how to set-up Queries to do
the work that is mostly done within worksheets at the moment and that
would probably increase reliability quite a lot.

So, that route might help you migrate the whole work-book and all it's
sheets into a proper database.  One problem with spreadsheets is that
a column or row might not have every field doing the same calculation
as all the rest in the row or column.  People sometimes put a lot of
effort into finding errant cells that misbehave in that way and there
are a lot of tools to help track such cells down.  A database allows
you to write a (or modify) a calculation (formula) in one place, in a
Query usually.  Then you can be certain that exactly that formula is
applied to every single line with no exceptions (although IF type
statements help deal with special cases).  The Query contains no
actual data and only has the formula written once so it's extremely
light-weight but when viewed as a table it looks like one of the tabs
(a worksheet) within a spreadsheet.  If you want it to look pretty
then set-up a Form or Report to present the output of the Query in a
more pleasant manner.

Errr, part of the power of spreadsheets is that it does have the
flexibility to have very different calculations in a column or row
but then each cell needs to be labeled usually in an adjacent
cell/field so that you know what it's for a few months or years
later.  Unfortunately many people keep using spreadsheets to do what
a database would do better = which is ok as part of the planning
process but often becomes unwieldy in the longer-term.
Regards from
Tom :)

On 30 July 2014 07:37, Pat Brown <> wrote:

   In a relationall DB there is a facility to display all the
tables and this also shows how the tables are related and also
which fields are related and how they are related. This is
extremely useful to get an overall pcture of how things fit
together and, importantly in this case, what the effects would be
of changing something in one table on the data in the other tables.
Does such a feature exist in Calc? With a large number of sheets
and many cells looking up data in cells on other sheets it would be
great to be able to see how changing a sheet name or a column
heading would affect the rest of the data.


To unsubscribe e-mail to:
Posting guidelines + more: List archive: All messages sent
to this list will be publicly archived and cannot be deleted

To unsubscribe 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.