Selected Answer
Consider that =LOOKUP(2,1/(A:A<>""),ROW(A:A)) returns the number of the last used row in column A. Therefore, LOOKUP(2,1/(A:A<>""),ROW(A:A))-11 returns the first of a 12-months' period ending in the last row. Therefore the formula below would return the last 12 months recorded on the sheet, depending upon the value of "11".
=INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))-11)
Working from there toward a solution consider replacing your data table in row 71 with a proper Excel table. By default its name will be Table1 but I changed that to Tbl because it has less characters. Use Table Tools > Design or the Name Manager to change the name.
Now I can use =ROWS(Tbl) to determine the last row and, accordingly, ROWS(Tbl)-11 the rolling year. The formula below will have the same effect as the one above. It's not only much shorter but also eats less computing power.
=INDEX(Tbl[MMM-YY],ROWS(Tbl)-11)
In the folowing, I will now show you how to create a copy of your rolling year table (A5:M12) starting from cell I17. Start by understanding the ROW() function. It returns the number of the row which it is in. Accordingly, =ROW() has a value of 18 when entered in row 18 and changes that value to 19 when copied down. Therefore it is a simple way of creating consecutive numbers. The COLUMN() function works the same way horizontally. This is important when you think of transposing data. Enter this formula in I18.
=INDEX(Tbl[#Headers],ROW()-16)
Tbl[#Headers] identifies the range of the table's headers (predictably so lol). You want the value in the second column ("Receipts") which is 2, of course, but you want to express this as a ROW() number so that you can copy it down. This is where the 16 comes in. ROW(), being in row 18, returns 18. 18-16 = 2. Now copy the formula down to I24 and you get a neat listing of the table headers.
Now we take the formula =INDEX(Tbl[MMM-YY],ROWS(Tbl)-11) already developed above. It already returns the correct result for J17. However, we want the 11 to become a dynamic counter, changing with the column. We might replace it with COLUMN()-1. In column J COLUMN() returns 10, in context -10, which we need to increase it by another 1. But when we copy this formula to the right the deduction increases whereas we need it to decrease.
12-COLUMN() reverses the counter. 12 - 10 (for column J) = 2. Add another 9 to make up the 11 we need: 12-COLUMN()+9 = 21-COLUMN(). I created a positive number intentionally. Surround the expression by parentheses to improve its readability. -(21-COLUMN()). The formula below goes to J17. Copy from there to the right. Note that the 12 is an arbitrary number I chose because you want 12 months. However, if the number is smaller than the COLUM() a negative column number will result which Excel can't use. If this happens to you, increase the number 12 to any number which will be larger than the largest COLUMN() value expected.
=INDEX(Tbl[MMM-YY],ROWS(Tbl)-(21-COLUMN()))
The rest of the table follows the same pattern. Just the column Tbl[MMM-YY] needs to become dynamic. for that we use =MATCH("Receipts",Tbl[#Headers],0) and, since "Receipts" must also be variable, we arrive at =MATCH($I18,Tbl[#Headers],0) to specify the column in the INDEX function below.
=INDEX(Tbl,ROWS(Tbl)-(21-COLUMN()),MATCH($I18,Tbl[#Headers],0))
The location of the MATCH function in the formula seems reversed but it isn't. The INDEX function specifies the cell by Row and Column, in that sequence. In the formula =INDEX(Tbl[MMM-YY],ROWS(Tbl)-(21-COLUMN())) the column number is omitted because the range only has a single vector. However, because the vector is, in fact, a row the "row number" in the formula turns out to be a column number in effect.
Anyway, enter the above formula in J18 and copy to J18:U24. The list isn't the same as what you have in row 4. To achieve that result, delete rows 95:98 which demonstrates that the table changes automatically to comprise the last 12 months in the table at the bottom. I attach a copy of your file with the above changes implemented.
