help with adding up mutiply spreadsheets

DaveG247

Hi all, I'm looking for a bit of help on excel 2003,

My Manager has created a sheet so that we can log what we do each 6 minutes of the day, we have various codes for different activates which are entered into the spreadsheets. Each member of staff has there own file containing a work book with 5 sheets, 1 for each week of the month and a totals sheet. The total sheet on each person work book counts all the codes using a Countif formula.

I now need all the totals on one master spreadsheet I’ve had a go and set up a spreadsheet with all the activities on the left and week numbers across the top which is the same layout as the total spread on each persons individual file, but it proving a bit of a pain having to type = then open the persons folder and file in order to choose the correct field to enter it into the master, 60 different time for each person every month.


I wondered if any one could offer any advice on the best way to add up all the totals.

attached a picture of the sheet





Thanks
Dave

JasX

Not sure I completly follow what you're trying to do, can you reference the cells directly?

if you move the 'totals' sheet from the separate individual files into (moving the entire sheet) a single master and keep the sheet name consistent you should be able to reference the target cell on each sheet and have the info automatically pulled in?

ie 'Steve' sends you his timesheet file, you open it an right click his 'month total' sheet and 'move' it into your file with everyone's total in. rename it in the master sheet 'Steve Total' and reference the appropriate cells



....every 6 minutes..... Lawyers by any chance?

DaveG247

JasX wrote: »

Not sure I completly follow what you're trying to do, can you reference the cells directly?

if you move the 'totals' sheet from the separate individual files into (moving the entire sheet) a single master and keep the sheet name consistent you should be able to reference the target cell on each sheet and have the info automatically pulled in?

ie 'Steve' sends you his timesheet file, you open it an right click his 'month total' sheet and 'move' it into your file with everyone's total in. rename it in the master sheet 'Steve Total' and reference the appropriate cells



....every 6 minutes..... Lawyers by any chance?

JasX sorry its really hard to explaination, I think I see what you mean in your example about copying each person total onto the new master sheet,so for example sheet 1 steve totals, sheet 2 jenny totals and so on and then the final sheet would be for the total of all the totals (so to speak).

googler

So the basic problem is that each person's total is in a different field, and different worksheet each week?

If you could rely on each person's total being in the same place each week you'd be fine, and would just need to refresh the master sheet.... I think.

DaveG247

All the totals should be in the same place as everyone total sheet is identical.

for example looking at the picture I up loaded to work out the number of AD01 which I have been entered I would go into the total of totals sheet click in cell C4, click sum and then click into steve's totals choose the figure in cell C4 type + then click into jennys cell C4 and so on untill I have all the figure and press enter which should give be a total. Its just a pain having to do this for every code in each week for 16 different people.

enigma52

let your manager sort it, sounds like he should do it anyway. Or pay for it to be done professionally

Mobeer

DaveG247 wrote: »

All the totals should be in the same place as everyone total sheet is identical.

for example looking at the picture I up loaded to work out the number of AD01 which I have been entered I would go into the total of totals sheet click in cell C4, click sum and then click into steve's totals choose the figure in cell C4 type + then click into jennys cell C4 and so on untill I have all the figure and press enter which should give be a total. Its just a pain having to do this for every code in each week for 16 different people.

Would you not just do this once? Agree a filename per person (Steve.xls, Jenny.xls etc), copy all the latest month's worksheets into the same folder once each month.

Have formulae in your master spreadsheet to link to the cells in known locations in each person's spreadsheet. For example cell H4 in your master sheet has formula:

='\\Asset99\FolderX\[jenny.xls]Totals'!H4+'\\Asset99\FolderX\[steve.xls]Totals'!H4

This gets the AD01 code totals for Jenny and Steve. Then just drag copy this formula into all the other cells it is needed (from the picture H4 to H9, etc).

Each time you refresh links (eg when workbook is opened), this spreadsheet refreshes its values.

To keep the values, copy the worksheet in the master worksheet to a new sheet, and copy and paste special the values only in the new sheet.


A database would make a more flexible solution here.

DaveG247

Cheers all think I've managed to get it sorted now got all the seperate totals in a workbook and then the final sheet showing all the totals added together. Only thing which is a pain now is every now and then I keep getting one sheet filled with #VALUE which seems to mess up my totals, but once I reopen the original file everythings OK again ???