jason061872
New Member
- Joined
- Apr 1, 2019
- Messages
- 13
I am in the process of creating a spreadsheet that will be used to track the development of a new employee over a period of weeks, based on certain criteria that will be identical for each week of their development. From the base information, I have created other sheets in the workbook that take that information and break it down so that at a glance, I can identify potential areas to help them improve and be successful in their role.
As this spreadsheet is still in development and the criteriabeing audited will likely evolve over time, I need to be able to add or removerows as needed and have the formulas that breakdown the information updateautomatically. Here is the formula Ihave currently:
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B132:K132"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4)),IF(D4=1,SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B132:K132"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4))/1,""))
The objective of this formula is to create an average scorefor a task based on individual state and task type.
A4:A22 refer to the worksheet names Week 1 Week 19 These will not change
The Q4 in the formula refers to the type of task being performed For purposes of discussion, this will not change.
B4:K4 refers to the state the task is being performed inacross all the weekly worksheets This will not change
B5:K5 also refers to the task being performed, but on all theweekly worksheets as opposed to the breakdown sheet This will not change
B132:K132 refers to the total score the employee earnedwhile performing their task across all weekly worksheets These are the cellreferences that will change in this formula as I either add or remove auditcriteria. If I remove a row, this willchange to B131:K131 as an example.
The issue I am having is that the B132:K132 cell referencesare not updating automatically as I add or remove audit criteria; which forcesme to continuously go in and manually update the cell references. I am looking for a way to have thisinformation update on its own. Anyassistance would be greatly appreciated!
As this spreadsheet is still in development and the criteriabeing audited will likely evolve over time, I need to be able to add or removerows as needed and have the formulas that breakdown the information updateautomatically. Here is the formula Ihave currently:
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B132:K132"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4)),IF(D4=1,SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B132:K132"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$4))/1,""))
The objective of this formula is to create an average scorefor a task based on individual state and task type.
A4:A22 refer to the worksheet names Week 1 Week 19 These will not change
The Q4 in the formula refers to the type of task being performed For purposes of discussion, this will not change.
B4:K4 refers to the state the task is being performed inacross all the weekly worksheets This will not change
B5:K5 also refers to the task being performed, but on all theweekly worksheets as opposed to the breakdown sheet This will not change
B132:K132 refers to the total score the employee earnedwhile performing their task across all weekly worksheets These are the cellreferences that will change in this formula as I either add or remove auditcriteria. If I remove a row, this willchange to B131:K131 as an example.
The issue I am having is that the B132:K132 cell referencesare not updating automatically as I add or remove audit criteria; which forcesme to continuously go in and manually update the cell references. I am looking for a way to have thisinformation update on its own. Anyassistance would be greatly appreciated!
