Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic updating between worksheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic updating between worksheets
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. "Gareth R" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to disable automatic updating | Links and Linking in Excel | |||
Automatic Updating of Graphs | Charts and Charting in Excel | |||
Automatic updating from one worksheet to another... | Excel Discussion (Misc queries) | |||
automatic updating | Excel Discussion (Misc queries) | |||
Automatic updating of 2nd sheet from first | Excel Worksheet Functions |