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

[jason061872]

=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?

 

[JLGWhiz]

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.

 

[jason061872]

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?

 

[Eric W]

The range is not updating because you are defining it in a text string.

[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:

[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:

[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?! 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!