Best way to set up analysis workbook

[Guest]

Hi all

Over the past few weeks I have been putting together (with the help of you guys) a large analysis workbook

I was hoping to use it as a template for future projects, so others can also use it. The problem I have is that the original data (Sheet 1) can be between 1000 & 20000 rows. The columns remain constant

In sheet 2 I have a summary of unique From & To destinations from sheet 1. (using advanced filter, copy, goto, special, visable cells only, paste

In sheets 3 to 12 I have lookup tables with formulas to atomatically cost the original data and compare with existing costs. These are suppliers, 10 of them and can change from project to project, so using defined names is futile

Once all data is analysed, I then need to produce a series of pivot tables, which are always the same

What is the best way of automating these processes, so that a virtual beginner in excel can use with ease?

Lastly, the latest workbook is 33,928 kb and takes forever to recalculate. (use F9 only when I want to) Anyway to reduce the size and speed it up. Reckon a few macros will blow my machine up!

Is excel the way to go or access

Thanks
Alby

 

[BrianB]

My 2 cents. A couple of ideas.
1. General setup
It seems that the way you have set up the sheets with pivot table
should make it possible to just change the data sheet contents and hav
everything else re-calculate. Putting additional columns here can sav
all kinds of problems elsewhere in the workbook. I do something simila
on a daily basis for the first couple of weeks each month, using th
same file to start the following month. Changing the month on the firs
page makes other sheets fall into line.

2. Regarding the file size.
a. There is a pivot table option to not save the pivot table data. N
need if an enclosed sheet has it anyway. Do need to refresh tables i
data changes.
b. When setting up a pivot table there is an option to use the data o
an existing pivot table instead of duplicating it. This is mainl
memory saving unless you save the data as mentioned in a.
c. You could have the data sheet in another workbook (?