Compiling assets across workbooks

Dear excel forum,

I am looking for help creating a daily report using excel, with which I hope macros can help. However, I have very limited experience with them, and am therefore hoping someone on these boards can help.

Essentially, I have an excel table that shows asset classes across three accounts.
1.JPG

Each day, I go into a system, and export into excel each of the three accounts positions. They are classified by asset class in one column, and have their market value in another column.
2.JPG

I want to set up a macro in the summary page that when I click, it sums up all of the values of the asset classes by account.

I would ideally like to be able to do this without having to merge the 3 separate position lists into the same workbook as the summary page, but understand if that's not possible.

I tried compiling a workbook with the summary sheet and 3 account sheets, and setting up a =SUMIF that references the Asset Class and Value columns of each tab. This works for each cell but I could not compile it into a macro that would do it all with one click.

Thanks in advance for the help!

Andy

Summary.xlsx
Q006 7.23.2012.xlsx

Hi Arlu,

Thank you for checking it out.

Here are two files.

Summary.xlsx has the summary page that I am trying to update. It also includes sheets of the three accounts that I am consolidating.

Q006 7.23.2012.xlsx is one of the accounts itself, with the positions exported from our systems.

What I want is to be able to download the three account positions and save them in a folder for that day. Then I want to open the Summary file and use a macro that looks to those 3 account position sheets, and consolidates the data on the summary page.

Can anyone offer any suggestions. Could I record a macro? How can I point a Macro to open a file in a directory?