If any of the cells in Sheet1!D2:D40 contain "COMPLETE", I need cell F(row), from that sheet, to copy its contents to Sheet3!B(row). I've tried this using =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'm clearly a rookie (ESPECIALLY with Excel/VBA), but I do understand programming concepts. I feel like there's probably some array function I'm unfamiliar with that could do this.
I've tried =IF(Sheet1!$D2:$D40 = "COMPLETE", Sheet1!$F2, "")
That was useless.
I tried several IF_THEN variations in VBA, but kept getting the same result.
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