Formula will not automatically adjust when adding/removing rows?

jason061872

New Member
Joined
Apr 1, 2019
Messages
13
I could use some help with this formula:
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B123:K123"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$9))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$9)),IF(N4=1,SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$4:$A$22&"'!B123:K123"),INDIRECT("'"&$A$4:$A$22&"'!B4:K4"),B4,INDIRECT("'"&$A$4:$A$22&"'!B5:K5"),$Q$9))/1,""))

This formula is a multi-worksheet averaging of numbers given specific criteria.
A4:A22 refers to the names of the worksheets. In this case Week 1, Week 2, Week 3…, Week 19.
B123:K123 refers to the final line on each of the worksheets,which is an average of rows 7 – 122.
B4:K4 is a criteria reference; in this case it refers to a specific state.
B5:K5 is also a criteria reference; referring to a product type.
B4 refers to the specific state I want the formula to search for in B4:K4
Q9 refers to the specific product type I want the formula to search for in B5:K5.

The formula works fine in that it successfully searches through all the worksheets, finds the task and state match and averages the results. If there is only one instance of the state/task, it returns the one result. If there are no matches, it leaves the cell blank.

The problem with this formula is that if I need to add or remove a row above line 123, the B123:K123 reference does not automatically update to reflect the removal or addition of the new row. In order to save myself some time if I make a change, I would love to be able to find a way to make this formula update on its own.
Any help or assistance you could provide would be greatly appreciated!
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows
Excel doesn't change anything inside double quotation marks. Try this:

INDIRECT("'"&$A$4:$A$22&"'!B" & ROW(B123) & ":K" & ROW(K123))
 

Forum statistics

Threads
1,141,476
Messages
5,706,604
Members
421,460
Latest member
Taamrak

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