Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linked cell auto update???
We are using a workbook for our payroll. The file has aheet with every
employee's name plus a summary page. The file is 5.67MB. The summary page is linked to each employee sheet. The summary page has 7 rows for each emplyee that is linked to the emplyee's sheet. If you move a sheet or remane the sheet the cells linked on the summary page don't automatically update. example cell D3 on the summary sheet would be =Blank14!$C$3 if you go and change the linked employee's sheets name cell D3 will only update if you double click the cell. Is there a way to have it auto update? Thanks, Chance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linked cell auto update???
While I do not recommend having seperate sheets for each employee (I recomend
having all of the data for all employees in one sheet and then using a pivot table to get the data out) here is a solution to your problem. You need to add two sheets to your workbook. One at the beginning of the of the employees called for example "Begin" and another sheet after the last employee called "End" so that the list of sheets would be similar to this: Summary/Begin/Bob/Dave/Mary/End Now on your summary sheet select the cell that you would like to sum and type "=Sum(" now select al of the sheets from Begin to End (Select Begin - hold down the shift key - Select End. Now specify the cell that you would like to sum on all of the sheets. Now typw ")" Your formula will look like =Sum('Begin:End'!A1) Finally hide the begin and end sheets so that no sheets can be added outside of their range. (You are summing up all of the sheets between the two sheets)... HTH "Chance224" wrote: We are using a workbook for our payroll. The file has aheet with every employee's name plus a summary page. The file is 5.67MB. The summary page is linked to each employee sheet. The summary page has 7 rows for each emplyee that is linked to the emplyee's sheet. If you move a sheet or remane the sheet the cells linked on the summary page don't automatically update. example cell D3 on the summary sheet would be =Blank14!$C$3 if you go and change the linked employee's sheets name cell D3 will only update if you double click the cell. Is there a way to have it auto update? Thanks, Chance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linked cell auto update???
Sorry what I suggested will not work... I misunderstood the layout of the
summary sheet. I go back to putting all of the data on one master sheet (or in a database) and pivoting that data out of the master... Oops... "Jim Thomlinson" wrote: While I do not recommend having seperate sheets for each employee (I recomend having all of the data for all employees in one sheet and then using a pivot table to get the data out) here is a solution to your problem. You need to add two sheets to your workbook. One at the beginning of the of the employees called for example "Begin" and another sheet after the last employee called "End" so that the list of sheets would be similar to this: Summary/Begin/Bob/Dave/Mary/End Now on your summary sheet select the cell that you would like to sum and type "=Sum(" now select al of the sheets from Begin to End (Select Begin - hold down the shift key - Select End. Now specify the cell that you would like to sum on all of the sheets. Now typw ")" Your formula will look like =Sum('Begin:End'!A1) Finally hide the begin and end sheets so that no sheets can be added outside of their range. (You are summing up all of the sheets between the two sheets)... HTH "Chance224" wrote: We are using a workbook for our payroll. The file has aheet with every employee's name plus a summary page. The file is 5.67MB. The summary page is linked to each employee sheet. The summary page has 7 rows for each emplyee that is linked to the emplyee's sheet. If you move a sheet or remane the sheet the cells linked on the summary page don't automatically update. example cell D3 on the summary sheet would be =Blank14!$C$3 if you go and change the linked employee's sheets name cell D3 will only update if you double click the cell. Is there a way to have it auto update? Thanks, Chance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linked cell auto update???
We are forced to set up the workbook this way due to coroporate policy. The
summary page does not add the cells from the sheets but rather equal the values. The problem is when we rename a sheet or move a sheet the summary page does not update the linked cells to the renamed or moved sheet. If you double click the cell to edit it and then press enter it updates. Thanks for any additional help. Chance "Jim Thomlinson" wrote: While I do not recommend having seperate sheets for each employee (I recomend having all of the data for all employees in one sheet and then using a pivot table to get the data out) here is a solution to your problem. You need to add two sheets to your workbook. One at the beginning of the of the employees called for example "Begin" and another sheet after the last employee called "End" so that the list of sheets would be similar to this: Summary/Begin/Bob/Dave/Mary/End Now on your summary sheet select the cell that you would like to sum and type "=Sum(" now select al of the sheets from Begin to End (Select Begin - hold down the shift key - Select End. Now specify the cell that you would like to sum on all of the sheets. Now typw ")" Your formula will look like =Sum('Begin:End'!A1) Finally hide the begin and end sheets so that no sheets can be added outside of their range. (You are summing up all of the sheets between the two sheets)... HTH "Chance224" wrote: We are using a workbook for our payroll. The file has aheet with every employee's name plus a summary page. The file is 5.67MB. The summary page is linked to each employee sheet. The summary page has 7 rows for each emplyee that is linked to the emplyee's sheet. If you move a sheet or remane the sheet the cells linked on the summary page don't automatically update. example cell D3 on the summary sheet would be =Blank14!$C$3 if you go and change the linked employee's sheets name cell D3 will only update if you double click the cell. Is there a way to have it auto update? Thanks, Chance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Update of Linked Cell References | Excel Discussion (Misc queries) | |||
Auto update a linked Cell | Links and Linking in Excel | |||
Auto Update Linked data | Excel Worksheet Functions | |||
How do I have a linked cell auto-size to fit the linked data? | Excel Discussion (Misc queries) | |||
how do i auto update cell values after pasting linked references | Excel Worksheet Functions |