 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Spreadsheet size
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? 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. >G'day Animal > [quoted text clipped - 19 lines] >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. 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. >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. > 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 >> I have it (the spreadsheet) both ways. I have twenty different >> workbooks, two fields each, all in one spreadsheet (I know the [quoted text clipped - 23 lines] > >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. 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 > 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. 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. >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. Thats it my friend, keep digging the hole deeper. have a great day. >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. 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. > 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. 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.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.