Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() 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 |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Adding the contents of a cell to a formula | Excel Discussion (Misc queries) | |||
update a date cell only when worksheet is edited | Excel Discussion (Misc queries) | |||
macro help | Excel Discussion (Misc queries) | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions |