amarsha4
01-08-2009, 07:41 AM
Hello,
I have several spreadsheets each representing a department in the hospital where I work. Each of these files contains information about each cleaning post within the department. e.g.
POST NAME HOURS
101 J SMITH 37.5
102 (vacant)37.5
103 A BROWN 15.5
I wanted to combine all the files into one workbook, and to include a summary sheet which, with some VBA coding would list all vacant posts, total all hours etc.
The problem I have is that the files are on a shared drive and are accessed by four managers, so if two are trying to edit at the same time, they cannot.
Since under normal circumstances each manager would only need to edit the sheets for their departments I thought I could keep the files seperate and simply reference cells from these seperate files in the summary sheet (which would only be viewed by the managers, not edited), but I can't find how to reference cells from a seperate, unopened workbook.
I would prefer to try to get the first solution working (seperate sheets in same book) but if this is not possible then I'll still need help trying it the second way (all seperate workbooks).
Much obliged,
Andy
I have several spreadsheets each representing a department in the hospital where I work. Each of these files contains information about each cleaning post within the department. e.g.
POST NAME HOURS
101 J SMITH 37.5
102 (vacant)37.5
103 A BROWN 15.5
I wanted to combine all the files into one workbook, and to include a summary sheet which, with some VBA coding would list all vacant posts, total all hours etc.
The problem I have is that the files are on a shared drive and are accessed by four managers, so if two are trying to edit at the same time, they cannot.
Since under normal circumstances each manager would only need to edit the sheets for their departments I thought I could keep the files seperate and simply reference cells from these seperate files in the summary sheet (which would only be viewed by the managers, not edited), but I can't find how to reference cells from a seperate, unopened workbook.
I would prefer to try to get the first solution working (seperate sheets in same book) but if this is not possible then I'll still need help trying it the second way (all seperate workbooks).
Much obliged,
Andy