If any of the cells in Sheet1!D2:D40 contain "COMPLETE", I need to copy cell F(row) contents to Sheet3!B(row).
I tried =IF, but that gives 39 rows of #SPILL!.
I need the cells in Sheet3 to remain blank until said condition on Sheet1 is met.
After that, I need them to fill in, row by row (as conditionally required).
i.e., If Sheet1!D6 is "COMPLETE", I want Sheet1!F6 to copy to Sheet3!B1.
Then, if Sheet1!D17 is "COMPLETE", then Sheet1!F17 should copy to Sheet3!B2, etc.
I feel there's some array function that could do this.
I tried =IF(Sheet1!$D2:$D40 = "COMPLETE", Sheet1!$F2, "")
You can get the matching result with worksheet formula.
=IFERROR(INDEX(Sheet1!$F$2:$F$40, FILTER(ROW(Sheet1!$D$2:$D$40), Sheet1!$D$2:$D$40="COMPLETE")-1, 1), "")
Array formula (Press <Ctrl+Shift+Enter>)
=IFERROR(INDEX(Sheet1!$F$2:$F$40, SMALL(IF(Sheet1!$D$2:$D$40="COMPLETE", ROW(Sheet1!$D$2:$D$40)-1), ROW(1:40))), "")
VBA is a option for you too.
Sub demo()
Dim arr, res, idx
arr = Sheets("Sheet1").Range("D2:F40").Value
ReDim res(1 To UBound(arr), 1 To 1)
idx = 1
For i = 1 To UBound(arr)
If arr(i, 1) = "COMPLETE" Then
res(idx, 1) = arr(i, 3)
idx = idx + 1
End If
Next
If idx > 1 Then
Sheets("Sheet3").Cells(1, 2).Resize(idx - 1, 1).Value = res
End If
End Sub