Autofilter Macro Code with OR Operator

[schakarian]

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.

 

[SkipVought]

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,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}