Adding a row to worksheet does not update cell references in another.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

My address is
ragdyeratmsndotcom

"blausen" <[email protected]> wrote in
message news:[email protected]...

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