Cell References not updating when a row is added/removed

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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,141,074
Messages
5,704,147
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top