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

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
>

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
> >

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
> >