Hello,

I need assistance figuring out a formula. Here's the situation:

I have two worksheets.

Worksheet 1 lists, in spreadsheet form, names and dates of events, including
a column that details what state(s) the event is in (a row may list, "IL," or
"IL, WI, TX," etc...). It is sortable by state, and a number of other
features. Unfortunately, my readers want to see a monthly calendar view - not
a spreadsheet... so...

Worksheet 2 shows a monthly calendar view, with a drop down list to select
the Month and Year, and one to select a single State.

What I want to achieve is to populate the monthly calendar view/Worksheet 2
with the event names indexed on the appropriate date AND for ONLY the State
that was selected in the drop-down; i.e., if someone selects January 2010,
for Nevada, the monthly calendar would show when events that will be occuring
in January for Nevada only. Please note that the formula must look for
instances of the State name as opposed to a specific match (b/c as noted
above, a cell may include one or multiple states).

The formula I've arrived at is below, and according to Excel, contains no
errors - but the calendar view is populating ALL events, regardless of state.
I think I'm missing a "FALSE" statement that says, if there is NOT a match
between the State and Date, then a zero/blank value should be returned.

=IF(ISNA(AND(SUMPRODUCT(ISNUMBER(SEARCH($K$6,State s))+0),(MATCH(G$2&"_"&$A4,DateRef,0)))),"",INDEX(E ventTitle,MATCH(G$2&"_"&$A4,DateRef,0)))

In the formula above, "$K$6" refers to the State drop-down list on Worksheet
2; "G2" refers to the calendar month/day on Worksheet 2, with A4 the row;
"States" refers to the named range of states on Worksheet 1; "EventTitle" =
named range for the event names on Worksheet 1; "DateRef" = named range for
event dates on Worksheet 1.

Thank you for any assistance you can provide.