Best way to work with External Workbook

[dhx10000]

Instead of asking questions about how to overcome some of the problems I've run into, I'd rather ask how you have found the best way to work with two separate workbooks, workbook1 contains columns of data, workbook2 contains pivot tables and graphs pulling from workbook1. Is there a better way to get data from a second workbook than the way I did, which was to use pivot tables and named ranges (which caused me a lot of problems).

The main reason I tried to do this was to minimize on the size of the workbooks. If I had all pivot tables, graphs and source data in one excel sheet I would have had a 10MB size file, which is going to continue to grow with added source data. Another problem is the workbook is located in Microsoft Sharepoint which is basically a website, which added to the concern of a large file.

 

[BrianB]

I have worked for lots of years with setups such as yours.

Rest assured that you are using the best method possible. Not only are you saving yourself lots of time processing data, you will find that it is very easy to make global changes if you need to restructure data - such as when a company re-organises.

You may be doing this already, but don't forget to use Dynamic named ranges - so you can just add records to the end of a table and refresh pivot tables without changing the links.

eg. Insert/Name/Define and a formula like this instead of a fixed range :-
= OFFSET(Mysheet!$A$1,0,0,COUNTA(Mysheet!$A:$A),10)
It counts non-blank cells so make sure you pick a column with no blanks in the data. The example is a 10 column table.

Hope this helps.