Can anyone tell me why this formula will not update automatically?

jason061872

New Member
Joined
Apr 1, 2019
Messages
13
=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,""))

This formula is designed to average a series of scores overmultiple worksheets in a single workbook based on criteria that may change overtime. The cells B132:K132 do notautomatically update if I add or remove a row that comes before line 132. Can anyone tell me why or better yet how tofix this issue?


 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Probably because you are deleting/adding the rows in a different worksheet than the one the formula is in. You could try using a named range for B132:K132, then use that in the formula.
 
Upvote 0
Thanks for the tip! I have been attempting to create a named range and plug it in to the spreadsheet, but I keep getting a #REF error. I named the range "totalscore", set the scope to the entire workbook and have tried setting the refers to section with both !$B$132:$K$132 and 'Week 1!$B$132:$K$132:'Week19!$B$132:$K$132 (I have designed the spreadsheet to record 19 weeks of data - 1 week per worksheet), but it isn't working. Any ideas why?
 
Upvote 0
The range is not updating because you are defining it in a text string.

Code:
[SIZE=2][COLOR=#000000][FONT=Calibri]INDIRECT("'"&$A$4:$A$22&[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri]"[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]'!B132:K132[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri]"[/FONT][/COLOR][COLOR=#000000][FONT=Calibri])[/FONT][/COLOR][/SIZE]


Excel does not change anything in a text, whether it's the word "banana" or a range reference. It only changes directly defined range references. If you want the row to change if you add a row above it, you can try something like:

Code:
[SIZE=2][COLOR=#000000][FONT=Calibri]INDIRECT("'"&$A$4:$A$22&"'!B"&ROW(132:132)&":K"&ROW(132:132))[/FONT][/COLOR][/SIZE]


The row number is now out of the text, so it will update. However, like JLGWhiz noted, this will change the formula if you add a row in the current worksheet, not if you add a row in the other sheet. You can change it to:

Code:
[SIZE=2][COLOR=#000000][FONT=Calibri]INDIRECT("'"&$A$4:$A$22&"'!B"&ROW(Sheet2!132:132)&":K"&ROW(Sheet2!132:132))[/FONT][/COLOR][/SIZE]


and it will adapt if you add a row to Sheet2. And since your formula looks at multiple sheets, you have to make sure that you add a row in all of those sheets too.

And how to change it if you want to allow for adding columns?!
:eeek: It gets a lot more complicated quickly! You might want to look at some kind of INDEX/MATCH to find the proper range instead of hard coding it. Good luck!

 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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
Back
Top