-1

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.

braX
  • 11,506
  • 5
  • 20
  • 33
James Greene
  • 3
  • 4

1 Answers1

0

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
taller_ExcelHome
  • 2,232
  • 1
  • 2
  • 12
  • I'm going to have to reverse engineer and research to understand how this works, but it works beautifully! I added it to a few cells, modified sheet names and range parameters as needed and BAM! Perfect solution to what would have been 2 weeks of tearing out handfuls of hair. Thank you so much! – James Greene Aug 07 '23 at 02:22
  • Well, sad development. I was doing all this to simplify my job by cutting back on redundancy. It worked perfectly at home. I never paid attention to the fact that my employer is running Office Pro 2019 which, sadly, doesn't have the FILTER function. I've only taken a couple of coding classes so far and iterating over an array was a weak point in both (though Python was slightly less aggravating than Java). I guess it's time to learn. Thanks again! – James Greene Aug 08 '23 at 00:43
  • Consider using an array formula. – taller_ExcelHome Aug 08 '23 at 02:36