I wonder if someone could help with a nicer and less manual way to do what I am doing. I currently have a list of 12 items (Rows) Column 1 is a rank (1 - 12), column 2 is a country, column 3 is number of units corresponding to that country and column 4 is number of units shipped. Each day I cut and paste the 12 rows below the preceding 12 rows and put in column 5 the following =SUM(D13-IFERROR(VLOOKUP($C13,$C$1:$E$12,2,FALSE),0))
So basically because the items can move in terms of rank, a country wont always be in the same position in the list day by day. So I am checking in the next day's list (commencing row 13) what it's daily unit number is and then looking up the previous days list (range 1-12) and taking the number away. Issue I have right now, is in the formula I have to change $C$1:$E$12 to $C$13:$E$24 and then the next day $C$25:$E$36 etc. Is there anyway using offset or something similar, by copying and pasting I can check the preceeding 12 rows without manually
So basically because the items can move in terms of rank, a country wont always be in the same position in the list day by day. So I am checking in the next day's list (commencing row 13) what it's daily unit number is and then looking up the previous days list (range 1-12) and taking the number away. Issue I have right now, is in the formula I have to change $C$1:$E$12 to $C$13:$E$24 and then the next day $C$25:$E$36 etc. Is there anyway using offset or something similar, by copying and pasting I can check the preceeding 12 rows without manually