Note that the list with numbers in column C and E is varying all the time. That means that the list in column E and F is ever changing, growing or shifting. So the formula has to be able to cover that without making any mistakes
That said I would recommend setting up some dynamic named ranges (DNR) in Name Manager because:
1. they dynamically adjust in size to fit the data
2. reduce the need to edit formulas with new data
3. make formulas much easier to write, follow, edit and trouble-shoot
You will find these in the attached Name Manager. This upload will adjust for regional settings replacing the "," argument separators with ";". (I don't trust my typing skills enough to edit the formulas accurately in this post.)
Even though, for others watching this thread here is the list of DNRs (without regional adjustments):
Note all of these are defined with reference to the size of column A (Code)
Code |
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzz",Sheet1!$A:$A)) |
Country |
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH("zzzzz",Sheet1!$A:$A)) |
Index_Match |
=Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH("zzzzz",Sheet1!$A:$A)) |
Total |
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH("zzzzz",Sheet1!$A:$A)) |
Then (CSE) the formula in F2 filled down (I did it in G2 to demonstrate) is
Also given the shifting of data you mention I recommend this change to your formula in E2 and filled down. in the event the data shifts rows and the ROW() function changes the ROWS($E$2:$E2) will maintain without need for editing.
Bookmarks