How Do I Automatically Link New Spreadsheets to One Master File?

[gabonais]

Also posted under WORKSHEET FUNCTIONS

Dear all,

Set-up: I have created a template using Excel to manage projects for our
clients. The template contains a budget forecast, invoices to be generated,
an expense tracking worksheet and a budget reconciliation worksheet. I use
the template for each new project by saving it as a stand-alone document
named for the client and project in question.

Need: I now wish to create a Master Spreadsheet that can track all projects
throughout the year. My goal is to be able to review invoices, budgets,
profits, etc. at a glance through the Master Spreadsheet. I know I can go in
and manually get the information, but I am hoping this can be done in a more
elegant and automated fashion. In essence, whenever I use the template for a
new project, I'd like the pre-determined information I enter in it to also
automatically be captured in the Master Spreadsheet.

Any suggestions?

Many, many thanks,

(e-mail address removed)

 

[Gord Dibben]

My suggestion is to create the Template using the Template Wizard with Data
Tracking.

This will allow you to create a DB file wherein all data from each workbook
generated by the Template is stored for perusal and retrieval.

If you don't have it installed(was not included with 2002 or 2003) it can be
downloaded from this site.

http://support.microsoft.com/kb/873209/en-us


Gord Dibben MS Excel MVP

 

[gabonais]

Dear Gord,

Thank you for your recommendations (including the no multipositing
recommendation). I followed your steps but am unable to install report
manager and the template with data tracking. I have the xecutable files for
rptmgr.exe and tmplwiz.exe on my desktop but they don't seem to execute
properly (I use Excel 2003). I even did a search for the .xla files but
without any success.

Any suggestions on how to address this issue?

Thanks.

Karim Benjelloun

 

[gabonais]

Dear all,

I finally figured out what I was doing wrong!!!

Although the files I downloaded are .exe files, I HAD TO USE winzip to
extract all the files before proceeding. After this step, I found both
rptmgr.xla and tmplwiz.xla in the extraction folder along with other .dll
files. I copied the whole lot and pasted them into the Library Folder in
Microsoft Office under Program Files and the 2 add-ins finally appeared in
the appropriate dialog box.

A lot of people seem to have had the same problem with trying to run the
..exe files directly because they did not have the .zip extension which would
have pointed to the need for the winzp operation.