Using Index, Match and Search to populate items to a calendar view

[hjneedshelp]

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,States))+0),(MATCH(G$2&"_"&$A4,DateRef,0)))),"",INDEX(EventTitle,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.

 

[Pete_UK]

Send me an email with a small sample of what you have (in .xls format,
NOT .xlsx) to:

pashurst <at> auditel.net

(change the obvious) and I'll send you a file that will do this for
you.

Pete

 

[Max]

Maybe this easy formulas model would appeal to you (think it delivers what
you are trying to do) ...

Source data assumed in Sheet1, in cols A to C, data from row2 down, where
col A = State (eg NV, IL, TX, etc)
col B = Event titles
col C = Event dates (these must be real dates recognized by Excel)

Then in another sheet,
Assume
Input/DV in A2 for State, eg: NV
Input/DV in A3 for Date (these are assumed Text dates) eg: Jan 2010

Put in C2:
=IF(AND(Sheet1!A2=A$2,TEXT(Sheet1!C2,"mmm yyyy")=A$3),ROW(),"")
Copy C2 down to cover the max expected extent of source data in Sheet1, eg
down to C500

Then place this in D2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 across to F2, fill down by the smallest range large enough to cover
the max expected events per any state in a given month/yr, say down to F20?
Format col F as dates to taste. Cols D to F will return only the lines
dependent on the inputs in A2:A3, all neatly bunched at the top. When you
change the inputs in A2 and/or A3, you'd get only the results set satisfying
the dual inputs displayed in cols D to F

Note: If the input/DV in A3 is a real date (but formatted to show as eg: Jan
2010) use this variant instead in C2, copied down:
=IF(AND(Sheet1!A2=A$2,TEXT(Sheet1!C2,"mmm yyyy")=TEXT(A$3,"mmm
yyyy")),ROW(),"")

Above of any worth? Hit the YES below