Creating a master sheet that when I update it, automatically updates others

**Melvinrobb** · 04-08-2013, 04:54 PM

I am glad i attempted to clarrify, as I did indeed "miss something".
I did not notice the change from absolute to relative references for the columns. Now that i notice that difference, I can see the initial benefits for sure. I'll simply need to incorporate that element into the next file I make and see how it works out.

SuzyRussell - please upload a sampleworkbook if the file I attached earlier is unclear, or you are not sure how to take what is there and apply it to your scenario.

**SuzyRussell** · 04-09-2013, 07:10 AM

Hi all,

Thanks for your replies. OK, the formulas you have included look horrendously complicated to me. So, I've attached sheet. As you'll see there is a tab for April 13 and there would be tabs for the next 11 months giving a full year. projects or areas of work are listed down the left hand side with hidden rows for additional projects as the year goes on. Each staff member will have a sheet like this and fill in their time, which then links back to the summary sheet to show overall work in each area per month - so the summary sheet would have a column for every staff member (currently numbered 1-7) for each month. I also want to add in somewhere the budgeted time for each project so we can compare budgeted and actual time spent, but i think i'll need a separate summery sheet for this as otherwise it will get very unwieldy. The main problem at the moment is that every time a new project comes in I have to change it on every month of every individuals time sheet. So if we get a new project in May I'd be changing it on months may - Mar (or 11 tabs) per person so that's 77 changes which take forever! So what i want to be able to do is add new project into my master and then for that to automatically update everyone's timesheets. Ideally it would update across all the months left in the year, or I guess I could just add the next month at the end of the previous one, but then I'd want everyone's to automatically gain this new sheet.

Does that make sense?

Thanks

**newdoverman** · 04-09-2013, 10:20 AM

Having managed a number of large projects, I would approach this entirely differently using a database program. Seeing that you are using Excel, I would still use a different approach.

Every project that I ever worked on used Financial Coding for identification. I would use that financial code for the identification of the project at every opportunity possible instead of project names that can be long and easily entered with mistakes.

Instead of having a tab for each month that has all the projects listed, I would have a tab for each employee. Each employee's tab would have a column for the financial coding for each project and columns for start date and time, end date and time and elapsed time. As the employee starts to work on a project the Financial code would be entered and then the date and time etc.

A summary section for each employee can then be created in identical ranges on each employee's sheet using for example the SUMIFS function or possibly the SUMPRODUCT function that would calculate the time worked on each project in each time period. That data can then be brought into the summary sheet.

If the Summary is set up properly, you will be able to make use of the Pivot Table to extract various reports from your data.

**SuzyRussell** · 04-10-2013, 11:48 AM

Hmmm, this would mean that if you worked on a project for a couple of hours in the morning, then again in the afternoon and worked on two different ones in between you'd have to make 12 entries for that day wouldn't you - 4 dates and 8 start and finish times? I don't think this would work for us as we tend to get to the end of the day, summarise what we've done and then fill in timesheet...but thanks - maybe another way...? And coding definitely a good idea - we are thinking about this.

**newdoverman** · 04-11-2013, 09:00 AM

Time keeping for projects in the manner that these projects are being worked on is a time consuming activity if the statics gathered are to be of any real use. Without recording start and end times for each project, even multiple times a day for each project is the only accurate way to accumulate the data that you want. Guessing is the only other way.

I have had to do this kind of time use analysis and it isn't easy.