Question: Is there a way to make Excel automatically update these links
to the other sheets of the same workbook? The links will update IF I
insert a row or a column in the SAME worksheet but will not update if I
add the row or column in a different worksheet that is referenced by
that sheet.

I and my colleges are building a set of worksheets using EXCEL 2003
that will keep track of employee productivity. It would seem that this
is more involved that first expected, however we are getting around a
few of the intricacies and quarks that we have come across. We have set
up the Excel workbook to have a total of 32 sheets labeled Total Average
and 1 – 31 (consecutive days) each having a listing of our
employees. Each sheet allows us to enter numerical data (ex. 1, 2, or
3) for their productivity, which is then averaged using {
=IF(ISERR(AVERAGE(C3:I3)),"!",AVERAGE(C3:I3)) }, placing an
exclamation point in the cell to keep it from being blank (Assistance
by RagDyer on 'www.excelforum.com' (http://www.excelforum.com) for the
formula). This works like a dream; however we are now running in to a
new issue. This being that when we add an employee to the worksheet
(Example: Day 22) then sort the page to place the person in the sheet
in alphabetical order, the worksheet “Total Average” does
not update the links for the other employees. For better clarification:
I have Brian in cell A2, Elaina in cell A3, Joseph in cell A4, and Mike
in cell A5 with columns labeled across row 1 as A=Name, B=Day1, C=Day2,
D=Day3, E=Day4, ECT. Each cell/row has a link to an adjacent cell/row in
their corresponding day tab (Example: A2 = Brian B2 = Day 1 Average
Formula on sheet 2 cell J2, C2 = Day 2 Average Formula on sheet 3 cell
J2, ECT.). Then when you go to the sheet for the corresponding day such
as sheet 2 for Day 1, I have Brian in cell A1, Elaina in cell A2, Joseph
in cell A3, and Mike in cell A4 with my average formula in cell J1:4. I
put Jeff in cell A5 along with the averaging formula and then select
all cells from A1 to J5 and sort by Row A. This then of course places
Jeff and all the rest of the information in his row up to row A3 and
then moves Joseph and Mike down to A4 and A5. Then when you click on
[Total Average] Tab you of course will need to do the same thing to
update this sheet also. However before adding the new employee to the
list of employees I check the cell links to see if they changed to show
the new placement of the employee and they have not. They still
reference the old cells where the Employee’s average was.


--
blausen


------------------------------------------------------------------------
blausen's Profile: http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

Just how are you establishing your links?

Your sheet containing the links should display your sorted data exactly as
it appears on the source sheet.

That is, of course, assuming that you have even linked the blank rows of the
source sheet to the destination sheet, in anticipation of adding additional
data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"blausen" wrote in
message ...

Question: Is there a way to make Excel automatically update these links
to the other sheets of the same workbook? The links will update IF I
insert a row or a column in the SAME worksheet but will not update if I
add the row or column in a different worksheet that is referenced by
that sheet.

I and my colleges are building a set of worksheets using EXCEL 2003
that will keep track of employee productivity. It would seem that this
is more involved that first expected, however we are getting around a
few of the intricacies and quarks that we have come across. We have set
up the Excel workbook to have a total of 32 sheets labeled Total Average
and 1 – 31 (consecutive days) each having a listing of our
employees. Each sheet allows us to enter numerical data (ex. 1, 2, or
3) for their productivity, which is then averaged using {
=IF(ISERR(AVERAGE(C3:I3)),"!",AVERAGE(C3:I3)) }, placing an
exclamation point in the cell to keep it from being blank (Assistance
by RagDyer on 'www.excelforum.com' (http://www.excelforum.com) for the
formula). This works like a dream; however we are now running in to a
new issue. This being that when we add an employee to the worksheet
(Example: Day 22) then sort the page to place the person in the sheet
in alphabetical order, the worksheet “Total Average” does
not update the links for the other employees. For better clarification:
I have Brian in cell A2, Elaina in cell A3, Joseph in cell A4, and Mike
in cell A5 with columns labeled across row 1 as A=Name, B=Day1, C=Day2,
D=Day3, E=Day4, ECT. Each cell/row has a link to an adjacent cell/row in
their corresponding day tab (Example: A2 = Brian B2 = Day 1 Average
Formula on sheet 2 cell J2, C2 = Day 2 Average Formula on sheet 3 cell
J2, ECT.). Then when you go to the sheet for the corresponding day such
as sheet 2 for Day 1, I have Brian in cell A1, Elaina in cell A2, Joseph
in cell A3, and Mike in cell A4 with my average formula in cell J1:4. I
put Jeff in cell A5 along with the averaging formula and then select
all cells from A1 to J5 and sort by Row A. This then of course places
Jeff and all the rest of the information in his row up to row A3 and
then moves Joseph and Mike down to A4 and A5. Then when you click on
[Total Average] Tab you of course will need to do the same thing to
update this sheet also. However before adding the new employee to the
list of employees I check the cell links to see if they changed to show
the new placement of the employee and they have not. They still
reference the old cells where the Employee’s average was.


--
blausen


------------------------------------------------------------------------
blausen's Profile:
http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

I checked all of the links and found that they do link to each cell in
the same row. (EXAMPLE: I am linking only to Column J Cell 2 (as my
start) and then I allow EXCEL to copy the link downwards by grabbing
the handle in the lower right corner and then dragging it down to all
of the cells below it until I reach the last employee)

However I do not have links to any of the other columns (IE. Column A,
Column B, Column C, ECT.) is this what you mean? That I need to have
links to these Columns also? If so that would negate the use of the
first page to average all of the other days and giving a quickly
viewable reference of all the other days of the month instead of
having to flip from one sheet to another sheet. This first sheet will
also (at a later date) be used to set up to use a chart so that
upper-management can see the trends.
EXAMPLE:
Sheet “Total Average”
A B C D E F G
1 Name Group Day 19 Day 20 Day 21 Day 22 Total Average

2 Charles MRC ='19'!E3 ='20'!E3 ='21'!E3 ='22'!E3 =AVERAGE(A2:E2)
3 Essie MRC ='19'!E4 ='20'!E4 ='21'!E4 ='22'!E4 =AVERAGE(A3:E3)
4 Kenya MRC ='19'!E5 ='20'!E5 ='21'!E5 ='22'!E5 =AVERAGE(A4:E4)

I know that these are not lining up like they would if they were in a
notepad with everything TABed. As you can see each of the days on this
sheet are linked to another sheet that coincides with a worksheet for
that specific day. Now when I try to add information to a day such as
Day 20 which would look like this:

EXAMPLE:
Sheet “20”
A B C D E E
1 Name Group Job 1 Job 2 Job 3 Average

2 Charles MRC 1 3 =IF(ISERR(AVERAGE(C4:I4)),"!",AVERAGE(C4:I4))
3 Essie MRC =IF(ISERR(AVERAGE(C5:I5)),"!",AVERAGE(C5:I5))
4 Kenya MRC 1 2 =IF(ISERR(AVERAGE(C6:I6)),"!",AVERAGE(C6:I6))

Once again this looks better pasted in to a notepad. As you can see
Kenya did not work any of the three jobs so there is no data placed in
her row, but we have information in Essie and Charles which would then
be averaged by Row E. This is where the problem comes in, I put David
in as a new hire on the 20th and then sort the sheet in Ascending order
by Column A, this will move all of the data in to correct alphabetical
order on sheet 20. With that done I go to sheet “Total Average” to
verify that the links also updated. This has so far not happened. I
have also not added David as of yet (which I will do later). Is there a
way to get these rows and cells to update so that they continue to pull
the correct averages for the correct people?


--
blausen


------------------------------------------------------------------------
blausen's Profile: http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

Cut out cutout from my address and send me your e-mail address and I'll send
you a sample of what I think you're looking for.
DON'T post any addresses in these groups ! ! !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"blausen" wrote in
message ...

I checked all of the links and found that they do link to each cell in
the same row. (EXAMPLE: I am linking only to Column J Cell 2 (as my
start) and then I allow EXCEL to copy the link downwards by grabbing
the handle in the lower right corner and then dragging it down to all
of the cells below it until I reach the last employee)

However I do not have links to any of the other columns (IE. Column A,
Column B, Column C, ECT.) is this what you mean? That I need to have
links to these Columns also? If so that would negate the use of the
first page to average all of the other days and giving a quickly
viewable reference of all the other days of the month instead of
having to flip from one sheet to another sheet. This first sheet will
also (at a later date) be used to set up to use a chart so that
upper-management can see the trends.
EXAMPLE:
Sheet "Total Average"
A B C D E F G
1 Name Group Day 19 Day 20 Day 21 Day 22 Total Average

2 Charles MRC ='19'!E3 ='20'!E3 ='21'!E3 ='22'!E3 =AVERAGE(A2:E2)
3 Essie MRC ='19'!E4 ='20'!E4 ='21'!E4 ='22'!E4 =AVERAGE(A3:E3)
4 Kenya MRC ='19'!E5 ='20'!E5 ='21'!E5 ='22'!E5 =AVERAGE(A4:E4)

I know that these are not lining up like they would if they were in a
notepad with everything TABed. As you can see each of the days on this
sheet are linked to another sheet that coincides with a worksheet for
that specific day. Now when I try to add information to a day such as
Day 20 which would look like this:

EXAMPLE:
Sheet "20"
A B C D E E
1 Name Group Job 1 Job 2 Job 3 Average

2 Charles MRC 1 3 =IF(ISERR(AVERAGE(C4:I4)),"!",AVERAGE(C4:I4))
3 Essie MRC =IF(ISERR(AVERAGE(C5:I5)),"!",AVERAGE(C5:I5))
4 Kenya MRC 1 2 =IF(ISERR(AVERAGE(C6:I6)),"!",AVERAGE(C6:I6))

Once again this looks better pasted in to a notepad. As you can see
Kenya did not work any of the three jobs so there is no data placed in
her row, but we have information in Essie and Charles which would then
be averaged by Row E. This is where the problem comes in, I put David
in as a new hire on the 20th and then sort the sheet in Ascending order
by Column A, this will move all of the data in to correct alphabetical
order on sheet 20. With that done I go to sheet "Total Average" to
verify that the links also updated. This has so far not happened. I
have also not added David as of yet (which I will do later). Is there a
way to get these rows and cells to update so that they continue to pull
the correct averages for the correct people?


--
blausen


------------------------------------------------------------------------
blausen's Profile:
http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

RagDyer, I am sorry I do not quite understand what you mean by cut out
your address so as to get in touch with you... I even tried to look you
up using the search and the forum says that you are not a registered
user...? Is there something that I am missing? Also because I have not
said it before, I really do appreciate the assistance that you have so
graciously given to myself and my associates with the formula from the
other day on placing another character or even a statement in a cell
rather than just having a blank cell in our work sheet. -So thank you
very much!-


--
blausen


------------------------------------------------------------------------
blausen's Profile: http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

My address is
ragdyeratmsndotcom

"blausen" wrote in
message ...

RagDyer, I am sorry I do not quite understand what you mean by cut out
your address so as to get in touch with you... I even tried to look you
up using the search and the forum says that you are not a registered
user...? Is there something that I am missing? Also because I have not
said it before, I really do appreciate the assistance that you have so
graciously given to myself and my associates with the formula from the
other day on placing another character or even a statement in a cell
rather than just having a blank cell in our work sheet. -So thank you
very much!-


--
blausen


------------------------------------------------------------------------
blausen's Profile:
http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059