How do I link 3 files together ?

[craigr75]

I have created a file for measuring staff attendance. By entering information into staff specific sheets, the information is then collated onto a front sheet that shows average holidays, absence etc. I need however to link say, 3 of these similar files together that would now show the Department attendance as opposed to individual teams. I am thinking of macros, but I'm not sure how or if this is the best method. HELP PLEASE !!!

 

[Jay Petrulis]

On 2002-04-16 01:08, craigr75 wrote:
I have created a file for measuring staff attendance. By entering information into staff specific sheets, the information is then collated onto a front sheet that shows average holidays, absence etc. I need however to link say, 3 of these similar files together that would now show the Department attendance as opposed to individual teams. I am thinking of macros, but I'm not sure how or if this is the best method. HELP PLEASE !!!

Hi Craig,

If you are using Excel XP, I believe you can group the tabs so you can put all the sheets in one workbook and reference the relevant team sheets for each team. Then you can build a department summary.

Specific to your question:

1. Build the three files separately with a common style and with an identically formatted final summary table/report.

2. Create a 4th workbook which references the three report summary sheets in the other files.

I don't know your data requirements, but this should give you a good start.

A macro route would ideally have one data input sheet and one lookup table sheet for standing data (employee name, department, team, etc.). The macro will then build the reports as needed and group according to the criteria you want.

It would require some careful planning, but would be very quick.

HTH,
Jay

 

[Yogi Anand]

Hi craigr75:
Further to what Jay suggested, you can also consider DATA|CONSOLIDATE and also DATA|SUBTOTALS