Spreadsheet size

[AnimalMagic]

Hi,

I have a spreadsheet which is twenty fields (cells) which are all
lookups from twenty different spreadsheet "tables". So this main page
holds info from 135 thousand plus lines of data, all of which could not
all fit onto one spreadsheet.

This main viewing page only contains 25 or so lines (rows), and 8 or ten
cells wide and less than half that number are the actual lookups. Why
would such a small page take on the size of all the lookup pages added
together?

 

[NoodNutt]

G'day Animal

You would be so much better off using a Database to store such a large
volume of records.

A DB stores all your data/records in tables and you can input/access the
data/records from forms, additionally, you can even create relationships
between the tables, also you can generate almost any type of report that you
require.

The added bonus of using a DB, such as MsAccess or similar is that you can
import all your existing records you have in your workbook directly into the
tables, which will save you a boat load of time not having to re-input them.

Something worth thinking about, especially when your records/data balloon to
500K, or even 1Mil.

If that doesn't make you all misty eyed and excited, then your probably
gonna have to keep adding worksheets, then tying them into your main page.

Good luck
HTH
Mark.

 

[AnimalMagic]

G'day Animal

You would be so much better off using a Database to store such a large
volume of records.

A DB stores all your data/records in tables and you can input/access the
data/records from forms, additionally, you can even create relationships
between the tables, also you can generate almost any type of report that you
require.

The added bonus of using a DB, such as MsAccess or similar is that you can
import all your existing records you have in your workbook directly into the
tables, which will save you a boat load of time not having to re-input them.

Something worth thinking about, especially when your records/data balloon to
500K, or even 1Mil.

If that doesn't make you all misty eyed and excited, then your probably
gonna have to keep adding worksheets, then tying them into your main page.

Good luck
HTH
Mark.

Thanks for not even coming close to actually answering the question I
asked.

I didn't really need a primer on database benefits.

Note that all of my workbooks are only one sheet.

 

[NoodNutt]

Yep

And sarcasm's gonna get you a boat load of responses.

Ever heard of NG etiquette, or even social etiquette, generally you thank
anyone in advance and put your name at the end.

You stated in your original thread:

"I have a spreadsheet which is twenty fields (cells) which are all
lookups from twenty different spreadsheet "tables"."

yet in your last message thread you stated:

"Note that all of my workbooks are only one sheet."

Multiple "Worksheets" are contained within a "Workbook".

So which is it, twenty different spreadsheets or one.

When you work it out and decide what you have, re-post your question and you
may get lucky and someone may help you or provide you with some friendly
advise and point you in the right direction.

Have a great day.

 

[AnimalMagic]

Yep

And sarcasm's gonna get you a boat load of responses.

As if you knew anything about hauling things in boats.

Ever heard of NG etiquette,

Yeah... Usually it involves the person answering a question to at least
attempt to actually answer the question that was asked.

or even social etiquette,

I think you need to direct this unqualified assessment toward him, not
me.

generally you thank
anyone in advance and put your name at the end.

Uhhh... he didn't provide any answers to be thanked for. Did you even
read the thread?

You stated in your original thread:

"I have a spreadsheet which is twenty fields (cells) which are all
lookups from twenty different spreadsheet "tables"."

Yep. One spreadsheet, 20 fields, all lookups FROM 20 different
spreadsheets. You seem to have a reading problem.

yet in your last message thread you stated:

"Note that all of my workbooks are only one sheet."

Yes. Note that it obviously only refers to the 20 "table" spreadsheets,
not the twenty field spreadsheet that was mentioned... DUH!

Multiple "Worksheets" are contained within a "Workbook".

I don't need a primer on spreadsheet basics.

So which is it, twenty different spreadsheets or one.

Read what was posted, dork.

When you work it

When you learn to read.

out and decide what you have, re-post your question and you
may get lucky and someone may help you or provide you with some friendly
advise and point you in the right direction.

That was the entire design behind the initial post, bright boy.

Have a great day.

I have it (the spreadsheet) both ways. I have twenty different
workbooks, two fields each, all in one spreadsheet (I know the
difference, dufus), AND I have 20, two field spread sheets, all being
called to by one,20 field spreadsheet, just like I stated.

 

[Beege]

I have it (the spreadsheet) both ways. I have twenty different
workbooks, two fields each, all in one spreadsheet (I know the
difference, dufus), AND I have 20, two field spread sheets, all being
called to by one,20 field spreadsheet, just like I stated.

Sooo confused.

You can't have 20 workbooks in a spreadsheet (worksheet). It doesn't
work that way.

You CAN have 20 worksheets in a workbook (aka file).

So if I understand, which is questionable here, you have:

1] One file containing 20 worksheets, each of which has two fields.
2] Twenty workbooks (files) with one active worksheet each, and two
"fields" in each worksheet.
3] One workbook containing one active worksheet, pulling information
from the 20 workbooks' worksheets.

Even from the information you've given, I can't understand what the
question is. Does it have to do with the "zoom" feature of the Excel
window, or maybe the "print area setup"?

Please re-state what you are looking for.

Beege

 

[Gord Dibben]

What do you consider as a "spreadsheet"?

Spreadsheet is a generic term for any application that crunches numbers.

In Excel we refer to a file as a workbook which can be comprised of one or more
worksheets.

You cannot have twenty different workbooks all in one spreadsheet.

You could have 20 different worksheets all in one workbook.

Or you could have 20 different workbooks each with 2 worksheets.

Which is your setup?


Gord Dibben MS Excel MVP

 

[NoodNutt]

Well

It seems I'm not the only Dork that doesn't understand you question.

By the way, if you bothered to look at other questions that people post in
any other NG, you would notice that many have "TIA" at the end of their
question.

it stands for "THX in Advance"

Advice and help here is provided because some of us would like to think we
can help someone else in some way, I and many others give it freely of our
own time, it just makes me annoyed that I have wasted my time in a vein
attempt to assist someone such as yourself who seems to be full of contempt,
and appears totally ungrateful.

I'm sure the others will assist you, once you re-post your question,
structured in a manner that will help others to better understand your
problem.

Oh, if you don't need a "primer" in spreadsheet basics from me, then might I
suggest you go back to your Excel manual and grab a refresher on Excel
Basics again.

Enjoy the rest of your day.

 

[AnimalMagic]

I have it (the spreadsheet) both ways. I have twenty different
workbooks, two fields each, all in one spreadsheet (I know the
difference, dufus), AND I have 20, two field spread sheets, all being
called to by one,20 field spreadsheet, just like I stated.

Sooo confused.

You can't have 20 workbooks in a spreadsheet (worksheet). It doesn't
work that way.

You CAN have 20 worksheets in a workbook (aka file).

So if I understand, which is questionable here, you have:

1] One file containing 20 worksheets, each of which has two fields.
2] Twenty workbooks (files) with one active worksheet each, and two
"fields" in each worksheet.
3] One workbook containing one active worksheet, pulling information
from the 20 workbooks' worksheets.

Even from the information you've given, I can't understand what the
question is. Does it have to do with the "zoom" feature of the Excel
window, or maybe the "print area setup"?

Please re-state what you are looking for.

Beege

OK look. I have a 30 MB workbook. It has a master panel worksheet
that performs lookups to a number of worksheets within this main
workbook. The data displayed on the master panel is data from a single
row in each worksheet, so all are VLOOKUPs.

I then created 20 separate workbooks, all with a single worksheet in
each, all only two cell columns wide. I then made a master panel that
performs lookups into those workbooks.

My question is why is that single sheet, 25 row workbook 30MB in size?
Why does it appear to add the file size of any workbooks that have
lookups performed into them added to the file size of the workbook doing
the lookup.

My reason is that this is a 135k row long set of records and the only
way to make it compatible with the older excel engines is to break it up.

 

[AnimalMagic]

Oh, if you don't need a "primer" in spreadsheet basics from me, then might I
suggest you go back to your Excel manual and grab a refresher on Excel
Basics again.

The question I asked is not about basics, so why would I need some dip
that mis-interpreted it spewing forth with an un-needed basics primer?

You need a refresher on how to answer the question asked, not consider
everyone as some lay person.

 

[NoodNutt]

Thats it my friend, keep digging the hole deeper.

have a great day.

 

[AnimalMagic]

Thats it my friend, keep digging the hole deeper.

You're the only one anywhere close to a ho, you crappy headed bro.

have a great day.

You're a disingenuous punk as well.

 

[Spiky]

The question I asked is not about basics, so why would I need some dip

that mis-interpreted it spewing forth with an un-needed basics primer?

That may be, but you asked it in a decidely juvenile fashion. And left
out the pertinent details for a week.

You need a refresher on how to answer the question asked, not consider
everyone as some lay person.

An expert user asking an advanced question should know not to treat an
Excel discussion group like his local bar. Unfortunately, public life
is all about image. And image on Usenet is text only. You want to kick
back after fighting with Excel all day? Try a bar. Suggest you use
business etiquette on the group.

The answer is that Excel's memory management sucks. It sucks in RAM
use, it sucks in saving files, etc. If you can find a method that
Excel uses memory, it is buggy. I've been trying to find answers to
similar issues myself. I'll spare you my rant on Microsoft, IT guys,
and red tape over the last 20 years for another day.

What I think happens:
Excel has to "open" the other files in order to access their data. So
while the program is open, it needs the full 30MB in RAM to operate
your summary file. It SHOULD

You can try copying your formulas to a new file, see what happens
saving that. Do not copy the whole sheet/tab, as that will bring its
memory issues along for the ride. You can also make sure the data
files are not larger than they need to be. If you are downloading data
repeatedly, they may be bloated. Because, and I may have mentioned
this, Excel's memory usage SUCKS.

 

[Spiky]

Stupid keyboard. This paragraph got cut off:

What I think happens:
Excel has to "open" the other files in order to access their data. So
while the program is open, it needs the full 30MB in RAM to operate
your summary file. It SHOULD release the memory when saving and/or
closing and not have your file be that size, too. But Excel's memory
usage sucks.

 

[NoodNutt]

That is O so mature

I am so offended!

Take the advice of the other respondants.

Spike has put forward some food for thought for you.

Heed his suggestion also with regard to etiqette.

Enough said.

and Goodbye from me.