I have created the enclosed spreadsheet and need a macro to move columns W & X in both WS (Schedule & Players) to the end of the loop of 30 columns and then all shift columns left 2 spots to replace W & X with advanced data. I have 6 groups of cells (3 for each column) with a locked reference (examples below) to columns W & X that then would renew based on new data coming across. The macro I recorded moved the columns OK but the W & X columns referenced below moved with them and now read AZ & BA respectfully.
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$W$2:$W$101="TUES"),ROWS($3:3))),"")
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$X$2:$X$101="TUES"),ROWS($3:3))),"")
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$W$2:$W$101="WED"),ROWS($3:3))),"")
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$X$2:$X$101="WED"),ROWS($3:3))),"")
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$W$2:$W$101="THUR"),ROWS($3:3))),"")
=IFERROR(INDEX(Players!U$2:U$101,AGGREGATE(15,6,Players!$U$2:$U$101/(Players!$X$2:$X$101="THUR"),ROWS($3:3))),"")
Thanks in advance
Bookmarks