Automatic updating between worksheets

[Gareth R]

Hi,
I have 3 worksheets with each worksheet having a the same data as the
previous one but in finer detail, ie. Sheet 1 is the Master Timetable, sheet
2 has only the Timetable of a select individuals from the Master, and sheet 3
has only a select few timeslots of the select individuals from Sheet 2.
How do I go about linking in all 3 worksheets so that if I make a change to
worksheet 1,2 or 3, they automatically update the other worksheets ie, any
changes to 2 or 3 will update the master Diary?

Any advice would be greatly appreciated.

Regards.

 

[JLatham]

Having several sheets that have finer details updating a single sheet is a
little tough; that is, if you want changes on both sheets 2 and 3 to affect
sheet 1 is kind of hard to do directly.

But if you take a 'roll up' approach, where sheet 2 is updated from sheet 3
for those individuals you have on sheet 3 and 2, and then where sheet 1 is
updated from sheet 2, it all works very smoothly.

Basically think of all the sheets in the workbook as one large sheet. Lets
think of how you might have things set up on the 3 sheets.

On Sheet 3 for Bill Jones you have a group of cells that show hours worked
for just Friday broken down into portions of the day on row 3, with the total
hours worked for a given day over at H3.

On Sheet 2 you have group of cells showing total hours worked each day on
row 2 for Bill Jones, with Friday's hours at F2. In F2 you could put formula:
='Sheet3'!H3
to bring the hours from Friday from Sheet 3 into F2 on Sheet2.
Total hours for the week for everyone on Sheet2 are in column H again.

On Sheet1, a formula like
='Sheet2'!Hn
where n is a row number would pull the total hours for an individual onto
Sheet1.

All of the values on Sheet1 would update when appropriate changes were made
to either Sheet2 or Sheet3.

Excel will help you get the formula correct. Pick a cell (on Sheet1 for
example) and start the formula by typing the = sign then choose Sheet2 and
click in the cell you want to link back to Sheet1 and just press the [Enter]
key and Excel will create the formula ='Sheet2'!Hn for you. That's why I
said think of all the sheets as one big sheet - it's just like referencing
another cell on the same sheet, but you choose the other sheet as part of the
selecting the cell to reference process. Even works across multiple
workbooks like that.