VLOOKUP with nested IF statements?

**crockwell** · 05-24-2010, 11:47 AM

Hi, I was wondering if anyone could help me.

I attached the document I am having trouble with. What I think I need is an IF statement nested in a VLOOKUP statement. I want to return the SCAR number it fits a certain number of criteria.

For instance,
if timestatus="Not yet completed"
if # workdays open=AND(">0","<5")

I want it to return the SCAR number of any that fit this criteria.

I hope that was clear! Thanks in advance to anyone who can help

**pb71** · 05-24-2010, 11:52 AM

You could tweak Advanced Filter using VBA so that the SCAR numbers that meet the criteria are transferred to a different worksheet.

**crockwell** · 05-24-2010, 12:05 PM

When I try an Advanced Filter, I get an error message; "You can only copy filtered data to the active sheet.

**contaminated** · 05-24-2010, 12:06 PM

Hi see attached
HTH!!!! ))))

**pb71** · 05-24-2010, 12:15 PM

An alternative making use of Advanced Filter:

Sub GetScar()

lastrow2 = Sheet2.UsedRange.Rows.Count

Sheet2.Range("A5:G" & lastrow2).EntireRow.Delete

lastrow1 = Sheet1.UsedRange.Rows.Count

Sheet1.Range("A1:G" & lastrow1).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("A1:B3"), CopyToRange:=Sheet2.Range("A5"), _
Unique:=False

End Sub

See attached.

**crockwell** · 05-24-2010, 12:42 PM

Thank you so much!!

**pb71** · 05-24-2010, 01:47 PM

crockwell,

A correction to my previous post:

Sub GetScar()

If Sheet2.UsedRange.Rows.Count > 4 Then
Sheet2.Range("A5:G" & Sheet2.UsedRange.Rows.Count).EntireRow.Delete
End If

Sheet1.Range("A1:G" & Sheet1.UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range("A1:C2"), _
CopyToRange:=Sheet2.Range("A5"), Unique:=False

End Sub

Revised workbook attached.