# Cell References not updating when a row is added/removed

#### jason061872

##### New Member
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.

#### jason061872

##### New Member
still looking for some help with this if anyone has any ideas.

Replies
3
Views
355
Replies
1
Views
273
Replies
2
Views
351
Replies
2
Views
219
Replies
0
Views
280

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?

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