Hi, got home and thought I'd have a look before bed.
To make it much easier, I continued the numbers in column A down for each row (I hope that's not a problem).
The first problem I found was that you had L$2 in your formula and it should have been just L2. After that it could be copied across and down to give your expected results but only for rows 13-18.
I've come up with a solution that I believe does what you want. The formula in cell L13 is:-
The main 2 changes were:-
This:- INDEX($L$2:$W$7,ROW(J9)-COUNTIF($B$13:$B13,"Chicago")*8,COLUMN(A1))
Replaced:- L2
This makes an index table at the top and automatically keeps cycling through rows 2-7 for each 6-row section you paste the formula into.
This: INDIRECT("'Raw Data'!G"&COUNTIF($B$13:$B13,"Chicago")+5&":BO"&COUNTIF($B$13:$B13,"Chicago")+5)
replaced:- 'Raw Data'!$G$6:$BO$6
This also counts the number of Chicago's that have appeared in column B up to that point and uses it to access the correct row in the Raw Data sheet.
Bookmarks