Too many sheets?

users
#1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

An Calc file I'm working on is 1.61 meg in size

There are 35 sheets within the file

The file is becoming rather slow when saving and loading it (probably
because my computer is 10 years old, etc) No, the drive isn't full-
only 2% used so far.

What can I do to speed up the manipulation of the Calc file? Some of
the sheets could be just put into another Calc file and referenced to
the main file....I have no idea how to do that but it sounds good.

Any ideas?

#2

Anybody have any ideas?

#3

Search the mailing list archive for posts 'database vs spreadsheet'
and consider whether you are organising your data in an efficient
design.

#4

Are all the spreadsheets needed? Do you have formulas that connect all 35
sheets? Or are many of them complete and not updated?

If you need all 35 tables, then you are using a spreadsheet as a database :slight_smile:

#5

Yes, all of the spreadsheets are needed.
  
I have a main sheet named START wherein all of the "data" is entered, such
as names and birth dates. Then on each of the 35 sheets an array has been
created which shows all of the data that was entered in the sheet named
START. But....as each sheet is specific to a certain person (and not all
people) only that persons "data" will appear on a given sheet in the cells
that I want them to.

Does that make any sense?

#6

Yes, all of the spreadsheets are needed.
  
I have a main sheet named START wherein all of the "data" is entered, such
as names and birth dates. Then on each of the 35 sheets an array has been
created which shows all of the data that was entered in the sheet named
START. But....as each sheet is specific to a certain person (and not all
people) only that persons "data" will appear on a given sheet in the cells
that I want them to.

Here's how it appears- Spreadsheet at the top (starting at A1) (let's call
the sheet CARLTON) wherein all of the information from the sheet START
appears. Below that spreadsheet CARLTON with all of the data is an array
that shows ALL of the data from the sheet START. CARLTON has cells with
formulas that get the necessary information/data from the array below it.

Does that make any sense?

#7

Hi :slight_smile:
Wow!  That really does sound like something that would be better handled by a database.  Plus it sounds as though you have already made the most difficult step that most people have with databases which is the conceptualisation in your brain.  Most people never get that far.

Please forgive me my excitement but i just thought this was a heavy and intractable spreadsheet issue which i tend to find very, very dull.

The "Start" sheet would be the table of data.  All the other sheets would be Forms/Reports built up from Queries.  Initially people would probably feel more comfortable using the Queries directly as they look like a spreadsheet.  Forms&Reports display the same info in a prettier gui so they might get built-up later.  Queries are probably easier to work with and are clearer about exactly how they choose which data to show from the table.   It's probably easier to modify or tweak Queries if things change in the future.

I take it the information in "Start" is confidential?  Is it possible for you to let us see the formulas of 1 or 2 of the other sheets?  or even just a couple of the formulae from 1 of the sheets that reads the data?  This list doesn't handle attachments so either copy and paste a couple directly into email/message or use Nabble? 
Regards from
Tom :slight_smile:

#8

Hi :slight_smile:
Is the 20 short for 1920? Here is an advert starring the incredible William
Shatner just to show off how far in the future the product is, errr was.
http://www.youtube.com/watch?v=PUEI7mm8M7Q

There is documentation here
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
which can help you fill in the gaps even tho you already have most of the
plan already drawn-up.

Queries don't tend to have nice layouts and fonts so they look like raw
spreadsheets but that is where Forms&Reports come in. It's best (imo) to
have the Queries do the calculations and filtering and then have the
Forms&Reports read the data from them rather than directly from the Table.
Some people have the Forms&Reports do all the calculations and filtering
themselves but i find that has a tendency to be less flexible and less easy
to fix or modify.

Regards from
Tom :slight_smile:

#9

Hi :slight_smile:
One advantage of using a database should be greater stability partly due to
less ram getting used up. There might be other things like greater control,
multi-user (maybe, but i'm not certain), maybe smaller file-size.

Vic20 had 16kb of "memory". I had a ZX81 and eventually got a 16Kb extension
pack that plugged into the back of it. It kept making the machine crash.
Accepted wisdom at the time was roughly the same as the cure for a wobbly
table. We put a book under the computer and let the expansion pack hang
loosely off the back. Magically no more crashes. Well, not so many.
http://oldcomputers.net/zx81.html

Yes, have a good weekend and don't worry about not replying until Monday or
whenever. The thread stays here.
Regards from
Tom :slight_smile:

#10

Have you tried following link?
http://help.libreoffice.org/Calc/Referencing_a_Cell_in_Another_Document

Maybe each sheet(person) can be separate calc files with reference to main(start sheet) file.

cK

#11

Tom,
    
    Oh man oh man....sign me up for one!  My very first computer was the
    Timex/Sinclair TS-1000. 
    
    Thank you for the information about LO Base.  I'll look it over in a
    bit.
    
    'Have a nice weekend.
    
    Alek

#12

Well, the complex part is that each of the Calc sheets has to be printed.
Each of the sheets has different fonts applied to the text to make it more
pleasant to look at. I'm trying to create a spreadsheet(s) that are usable
on Calc and M$ Excel

Database programs have always been a mystery to me. I guess I'll
learn....somehow.

Another "handicap" to what I'm doing is largely determined by the speed of
the ancient computers I use. Most 'em are trash picked wrecks....here...let
me take a look at the label on this one...hold on a minute....it says
something like, "Vic 20". Maybe that's someone's name? (well, I thought it
was funny)

#13

Hi :slight_smile:
I think this is the best, fastest option for the short-term.  Copy&paste might help to adjust the formulas quickly of course. 
Regards from
Tom :slight_smile:

#14

My son called it the "Victim 20"