Re: [libreoffice-users] Schema in Calc

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

Paul





On Wed, 30 Jul 2014 09:53:54 +0100
Tom Davies <tomcecf@gmail.com> 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
> word-processor.
>
> 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 <mistyhaven@gmail.com> wrote:
>
> > Hi,
> >    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.
> >
> > Thanks
> >
> > --
> > 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


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