Syntax for an IF, AND, OR formula...

Posted by Dustin on March 08, 2001 7:59 AM

Good morning Excel wizards,
I am trying to figure out the correct syntax for a formula.

Here is an example of a spreadsheet (Sheet1) that contains information about all PO Numbers tracked throughout the year:

------A--------------B-------------C-----------
1--PO Number----Start_Date------End_Date-------
2---000123-------2/2/01----------3/5/01--------
3---000987-------2/4/01----------(blank)-------
4---000567-------2/6/01----------(blank)-------
5---000999-------7/4/01----------(blank)-------

I need to figure out the syntax for a formula to be placed on a different sheet(Sheet 2) to do the following:
1) If a PO Number falls under a specific date range --my date ranges will be either the 1st quarter (Jan-March), 2nd quarter (April-June), etc..the PO number should populate a cell on Sheet 2.

2) Ideally, there should be no blank rows in the range created on Sheet 2 --- if the formula would result in no value (because the PO did not fit the date range) the formula should goto the next record.

Thanks for any assistance.
Dustin

Posted by Mark W. on March 08, 2001 9:00 AM

Dustin, why don't your add a new column D with
a header of "Qtr", enter the formula,
=VLOOKUP(B2,{"1/1/01",1;"4/1/01",2;"7/1/01",3;"10/1/01",4}+0,2),
into cell D2, and then copy down.

Next, enter the labels {"PO Number","Qtr"} into
Sheet2!A1:B1, and then use the following macro:

Sub Macro1()
Range("A:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sheet2!A1:B1" _
), Unique:=False
End Sub