Great big file!

[AyJayEL]

I have created a spreadsheet in excel with 10 pivot tables. 5 of which are based on 5 others. It looks good, does what the customer wants but and this is a big but it is 48Mb! I can't possibly email it to him. What can I do to make it smaller?

Any help gratefully accepted. Learn something new every day *:->*

 

[Hasit]

Compress it using Winzip or something like that. Make it a self extracting executable and then email it.

 

[xlbo]

Do you need to keep the functionality of the pivot tables ??
If not, just copy and paste as values, then get rid of the original pivot tables
HTH
Geoff

 

[AyJayEL]

Yes I do need to keep the functionality, but it's a thought for the future thank you.

I have zipped the file as suggested by Hasit.

Thank you to both of you. Learn something new every day *:->*

 

[RayWilson]

If you do this type of thing alot (for customers?) perhaps you should invest in a CD writer?

Ray

 

[AyJayEL]

Good idea! I work for local government though. Not much chance! Learn something new every day *:->*

 

[xlbo]

Probably a good idea. For future reference, pivot tables are very useful but take up a LOT of memory. In excel 97 (and 2000 I think), they use several "very hidden" sheets which hold all the calcs necessary to perform the pivot table functions. In XP I believe they use OLAP cubes which should significantly reduce the filesize. IMHO, I would never have 10 pivot tables in a single wb - I have found (to my cost) that the probability of a wb crashing / corrupting increases in direct proportion to the number of pivot tables within. Something to think about for the future is to use a data validation dropdown and lookup formulae to achieve a very similar effect.
Rgds
geoff

 

[AyJayEL]

Woo now, data validation dropdown sounds interesting! Any quick tips on how to do it? I am familiar with VLookup and HLookup.

Andrea Learn something new every day *:->*

 

[xlbo]

Well, it needs to be set up quite carefully but you would have a standard dataset (similar to what you would use for a pivot table, have a "key" on the far left (or top if you want to use HLOOKUP), then you just make your "report" ie what would be a pivot table, filled with vlookups that check for whatever is in the data validation cell and lookup the key in the dataset. If you want to make it look prettier, just have a middle step that uses a formula to lookup what is in the data validation dropdown and convert it to the lookup key needed. Can send an example file if you would like...
HTH
Geoff

 

[AyJayEL]

Geoff

I think an example file is definitely needed! I really appreciate your help with this!

If you are zipping the file can you let me know as our system here will stop it and I will need to request IT to release it. Thank you.

Andrea

PS What is HTH? Learn something new every day *:->*

 

[xlbo]

Hope This Helps - won't be zipping the file as it is only 200k....hope your system can handle that ;-)

ps...........email address ??
G

 

[AyJayEL]

Geoff

oh yes. Whoops. alane@buckscc.gov.uk Learn something new every day *:->*

 

[xlbo]

Sent you a file - if you have any queries on it, either post to this thread or email me
Ta
G

 

[AyJayEL]

Geoff

Having a little trouble but I've emailed you a cutdown version of my file.

Andrea Learn something new every day *:->*