Hi, my issue is pretty much what I listed above, I've dragged to copy the formula below and it's listing all the right cell changes in the A column, but it's not actually changing the outcome of the formula until I click individually into each box and hit enter. This is feasible of course (only 1.5k samples), but I was curious to see if there was a shortcut. Thanks!
Formula:
=CONCATENATE(
(IF(1<COUNTIF('R1'!B$4:U$23,A3),COUNTIF('R1'!B$4:U$23,A3),"")),
(IF(COUNTIF('R1'!B$4:U$23,A3)," R1 ,","")),
(IF(1<COUNTIF('R2'!B$4:U$23,A3),COUNTIF('R2'!B$4:U$23,A3),"")),
(IF(COUNTIF('R2'!B$4:U$23,A3)," R2 ,","")),
(IF(1<COUNTIF('R3'!B$4:U$23,A3),COUNTIF('R3'!B$4:U$23,A3),"")),
(IF(COUNTIF('R3'!B$4:U$23,A3)," R3 ,","")),
(IF(1<COUNTIF('R4'!B$4:U$23,A3),COUNTIF('R4'!B$4:U$23,A3),"")),
(IF(COUNTIF('R4'!B$4:U$23,A3)," R4 ,","")),
(IF(1<COUNTIF('R5'!B$4:U$23,A3),COUNTIF('R5'!B$4:U$23,A3),"")),
(IF(COUNTIF('R5'!B$4:U$23,A3)," R5 ,","")),
(IF(1<COUNTIF('FC1'!B$4:U$23,A3),COUNTIF('FC1'!B$4:U$23,A3),"")),
(IF(COUNTIF('FC1'!B$4:U$23,A3)," FC1 ,","")),
(IF(1<COUNTIF('FC2'!B$4:U$23,A3),COUNTIF('FC2'!B$4:U$23,A3),"")),
(IF(COUNTIF('FC2'!B$4:U$23,A3)," FC2 ,","")),
(IF(1<COUNTIF('FC3'!B$4:U$23,A3),COUNTIF('FC3'!B$4:U$23,A3),"")),
(IF(COUNTIF('FC3'!B$4:U$23,A3)," FC3 ,","")),
(IF(1<COUNTIF('GT1'!B$4:U$23,A3),COUNTIF('GT1'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT1'!B$4:U$23,A3)," GT1 ,","")),
(IF(1<COUNTIF('GT2'!B$4:U$23,A3),COUNTIF('GT2'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT2'!B$4:U$23,A3)," GT2 ,","")),
(IF(1<COUNTIF('GT3'!B$4:U$23,A3),COUNTIF('GT3'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT3'!B$4:U$23,A3)," GT3 ,","")),
(IF(1<COUNTIF('GT4'!B$4:U$23,A3),COUNTIF('GT4'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT4'!B$4:U$23,A3)," GT4 ,","")),
(IF(1<COUNTIF('GT5'!B$4:U$23,A3),COUNTIF('GT5'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT5'!B$4:U$23,A3)," GT5 ,","")),
(IF(1<COUNTIF('GT6'!B$4:U$23,A3),COUNTIF('GT6'!B$4:U$23,A3),"")),
(IF(COUNTIF('GT6'!B$4:U$23,A3)," GT6 ,","")),
(IF(1<COUNTIF('B1'!B$4:U$23,A3),COUNTIF('B1'!B$4:U$23,A3),"")),
(IF(COUNTIF('B1'!B$4:U$23,A3)," B1 ,","")),
(IF(1<COUNTIF('B2'!B$4:U$23,A3),COUNTIF('B2'!B$4:U$23,A3),"")),
(IF(COUNTIF('B2'!B$4:U$23,A3)," B2 ,","")),
(IF(1<COUNTIF('B3'!B$4:U$23,A3),COUNTIF('B3'!B$4:U$23,A3),"")),
(IF(COUNTIF('B3'!B$4:U$23,A3)," B3 ,","")),
(IF(1<COUNTIF('B4'!B$4:U$23,A3),COUNTIF('B4'!B$4:U$23,A3),"")),
(IF(COUNTIF('B4'!B$4:U$23,A3)," B4 ,","")),
(IF(1<COUNTIF('Bl1'!B$4:U$23,A3),COUNTIF('Bl1'!B$4:U$23,A3),"")),
(IF(COUNTIF('Bl1'!B$4:U$23,A3)," BL1 ,","")),
(IF(1<COUNTIF('Bl2'!B$4:U$23,A3),COUNTIF('Bl2'!B$4:U$23,A3),"")),
(IF(COUNTIF('Bl2'!B$4:U$23,A3)," BL2 ,","")),
(IF(1<COUNTIF('Bl3'!B$4:U$23,A3),COUNTIF('Bl3'!B$4:U$23,A3),"")),
(IF(COUNTIF('Bl3'!B$4:U$23,A3)," BL3 ,","")),
(IF(1<COUNTIF('Bl4'!B$4:U$23,A3),COUNTIF('Bl4'!B$4:U$23,A3),"")),
(IF(COUNTIF('Bl4'!B$4:U$23,A3)," BL4 ,","")),
(IF(1<COUNTIF('FTA'!B$4:U$23,A3),COUNTIF('FTA'!B$4:U$23,A3),"")),
(IF(COUNTIF('FTA'!B$4:U$23,A3)," FTA ,","")),
(IF(1<COUNTIF('M'!B$4:U$23,A3),COUNTIF('M'!B$4:U$23,A3),"")),
(IF(COUNTIF('M'!B$4:U$23,A3)," M ,","")),
(IF(1<COUNTIF('Black3'!B$4:U$23,A3),COUNTIF('Black3'!B$4:U$23,A3),"")),
(IF(COUNTIF('Black3'!B$4:U$23,A3)," Bla","")))
Bookmarks