Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Autofilter Macro Code with OR Operator

Status
Not open for further replies.

schakarian

Programmer
Jun 18, 2002
7
US
Hi,
In Excel I have a drop down list that has each month as an item to select from.

The data looks like the following:

Item Jan Feb Mar Apr ...
ABC x x
DEF x
GHI x x x x

When a user selects a month from the list, a macro runs to filer the column(month) selected and display all items that have an x. (ie. if "Feb" is selected items DEF and GHI are displayed.)

I would like to add a YTD item to this list. I don't have a problem with identifying what the current month is and creating if statements accordingly, but my problem is the following: How can I specify multiple criteria on the Autofilter with having them ALL be valid? If i'm in Feb and choose YTD, I want to select all items that have JAN = x "OR" all items that have FEB = x (DEF and GHI above)...

Sorry for the long post, any help is appreciated.

 

Hi,

This assumes that your table example starts in A1 and the Selected Month value (Jan, Feb, Mar etc) is in V1.

Enter this formula in ROW 2 in an adjacent column. THIS NEW COLUMN is the one to use the AUTO FILTER on.
[tt]
=IF(ISERROR(SUMPRODUCT((OFFSET(B2,0,0,1,MATCH($V$1,$B$1:$E$1,0))="X")*(OFFSET(B2,0,0,1,MATCH($V$1,$B$1:$E$1,0))))),"X","")
[/tt]

Skip,

[glasses] [red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top