Enter the formula you want in J4, with J4 the active cell, go to the vba immediate window and enterhit enter and you'll see what the code is for that formula, except that the quotes need doubled up, have your code put this into J4 only, then use .autofill to copy down as necessary.

Hi, Thanks so much for your hel - im getting really close to solving my problem.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Range("J4:J40").FormulaArray = "=IFERROR(INDEX(C$2:C$14, SMALL(IF($B$2:$B$14=1, ROW($B$2:$B$14)-1),ROWS(G$2:G2))),"""")"
Sheets("Sheet1").Range("J4:J40").Columns().AutoFit
ElseIf CheckBox1.Value = False Then
Range("J4:J40").Value = " "
End If

End Sub

I just need the (G$2:G2) to occur sequentially {e.g. ((G$2:G3), (G$2:G4)), (G$2:G5)) etc}when the formula is repeated across the rane. Is this possible?