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