Fund an Excel worksheet from multiple external Excel workbooks

[uby]

I am trying to get certain information from multiple external Excel workbooks. There are about 5 external workbooks. Is this doable as a macro? Or do I have to use the vlookup?

Thank you in advance for any help anyone can offer.

Uby

 

[VBAjedi]

Several ways to do this: VBA code or linked cells would be two prime candidates. You should give some more detail about what you are trying to accomplish and what your environment is (where the files are stored in relationship to each other, etc). Linked cells will be the easiest, but do have drawbacks in some situations.



VBAjedi

 

[uby]

Well basically.... I have quarterly statements that must be produced.

There are 4 quarters and there are 4 files for each quarter. Under the quarters are the several fund names and the director's names that have investments in each of those funds. At the end I would like to create a separate spreadsheet that consolidates all the information into on worksheet. For example: John Smith 1st qtr 2,000 2nd qtr 5,000 3rd qtr 2,000 and 4th qtr 1,000 of the ABC fund.

In the separate spreadsheet I want to be able to add up all those amounts into one cell. The cell amount should be 10,000.

I hope I explained it correctly. Do you really think that linking it would be better? What is the VBA code way?

Thanks for responding so quickly.

Uby

 

[VBAjedi]

Ok, for a setup like that, the best approach by far is to set it up so all data for all funds/directors/quarters is on one sheet. Then it's a simple matter to use the AutoFilter to see, for example, a specific directors investments in all funds for a specific quarter. And Pivot Tables can then be used to create nice summaries in just about any format. . .

However, in some cases people are simply too set in their ways to even CONSIDER a complete change of approach, regardless of the advantages. If you're working with people like that. . . you can still pull this off, but it will be a lot more work, and the end result won't be as nice or as informative.

What's the call?

VBAjedi

 

[uby]

I have never worked with Pivot Tables. I am creating it, so I don't have to worry about the procedure to use. How would I create a Pivot Table from all the source files? Is this very time consuming?

It seems that the Pivot Table is the way to go.

Uby

 

[VBAjedi]

I would recommend putting all of the data on a single sheet. The exact layout would depend on your data, but I'm guessing the columns would be laid out something like:

Director | Quarter | Fund | Amount

In that layout, a directors investment into a given fund for four quarters would be on four seperate lines. This is probably the layout that would allow you to use Excels built-in analysis tools (like the AutoFilter and Pivot Tables) to best effect. A possible alternate layout would be:

Director | Fund | Q1 Amt | Q2 Amt | Q3 Amt | Q4 Amt | Year Total

That layout would allow you to total the amounts for the year for each director/fund combination on the same line. The only way I would recommend this layout is if that "Year Total" is the only number you're trying to get out of the data (i.e. you're not interested in using PivotTables for further analysis).

With either layout, you would have a single row of headers, then the data for all directors' investments into all funds for all quarters in the area below. At first glance it will look LESS organized and useful than having the information broken out onto seperate sheets, but when you start using the Autofilter and PivotTables on that big chunk of data you will see how powerful they really are!


VBAjedi