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

It would be useful to post a sampleworkbook of the format and structure you have in mind for the master sheet and the individual sheets, including examples showing what the individual sheet would display for specific master sheet data.
I am guessing the attached file is simliar to what you are hoping to accomplish.

To Melvinrob. I like your solution and I would like to suggest this modification to the formula on sheets A to C C5. This can be copied across to D5 and then with C5 and D5 selected, copied down the range as required. Format column D as Date.

Formula:

Please Login or Register  to view this content.

newdoverman... The difference between the formula I was using, and the one you posted, is that there is no MATCH function for the column #, so it automatically defaults to the column in the current range, correct?
I see how that is a shorter formula, whch is preferable, but in some instances you may want tables A-C to only show certain columns, or perhaps the order of the columns in the report sheet may change. excluding the MATCH formula for the columns could cause issues.
Perhaps I am missing something, but is the benefit of your formula over mine keeping the formula shorter by removing the second match function?

Don't get me wrong, I'm not criticizing your formulae as it works quite nicely.....I'm simply giving a suggestion with the possible situations that came to my mind when I saw your workbook.

Maybe it is I who is "missing something".

My thinking was more along the possibility of adding columns to the right of the existing columns on the first page. All that is required with the shorter proposed formula is to add the column to each page and then just copy the formula across to the new column and it will automatically have the correct cell references to the report sheet.

The relative references seem to work ok when I "shuffled" the columns on the report sheet. The data on sheets A to C remained intact no matter what I did. The same was true when I went to one of the A to C tabs and moved the columns into different orders or hid columns...the data seemed to remain intact.

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.

What you describe now is different then how I originally interpretted it.
Essentially you just want to add a new project to all of the sheets simultaneously. This can be done with the following steps (for this example lets insert a new activity above line 21 - Genetics):
1. Make sure the headers in Column A are the same for all of the monthly sheets (ex. Row 3 on every sheet is expected, rows 4:64 are the "Project and Activity" headers.)
2. Select all of the monthly sheets (click on the first sheet, hold shift, and click on the last sheet OR click on the sheets while holding CTRL).
3. Highlight row 21 and select copy
4. Right click on row 21 and select "Insert Copied Cells".
5. change the value in A21 to your new Project.
6. Click on the summary tab to unselect the group of sheets.

After I wrote up these steps, I realized you had Excel 2003, so the steps may be different. I would suggest uploading another sample workbook with a few monthly sheets included (specifically the month of February, and a 30 day month, and a 31 day month). In order for what I am describing to work, your monthly sheets need to have the same structure for all of them, and I should be able to quickly make the changes you may need.

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.

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.