Getting data from a range matching certain creteria

**cndesu** · 12-27-2012, 11:59 PM

I have a table which contains events and the dates which can be performed in the second column. In the next worksheet I have a month's calendar. I need all the events which matches the date.Yearly Event Calendar Sheet 2.xlsx

Any idea how can I do? There are lots of errors in it.

Thanks

**:) Sixthsense :)** · 12-28-2012, 12:25 AM

Not sure whether the 1,2,3 etc., boxes (In First Sheet) refers to months data or Days data, since Check Chemical Reagents is falling in 7-Jan-12 but it is getting mentioned in 1st box. Could you please clarify this one???

**cndesu** · 12-28-2012, 09:42 AM

Originally Posted by :) Sixthsense :)

Not sure whether the 1,2,3 etc., boxes (In First Sheet) refers to months data or Days data, since Check Chemical Reagents is falling in 7-Jan-12 but it is getting mentioned in 1st box. Could you please clarify this one???

Sheet2 has the trimonth calendar. Unsorted Events sheet has all the events with empty cells unsorted. Now First is the sheet which is a monthly calendar showing all events. (My mistake, Name error popped out, January 2012 month). Now the first grey cell should have all the events which is of January first. Now first two events are correct based on the date after that my formula is giving error and gets the other data instead of January first. Now the first column in grey cell are just bullets, apperars only if the right side has text (just conditional formatting).

Thanks

**cndesu** · 12-28-2012, 06:00 PM

Thanks I got through from other support topic. Used a helper column using countif() function. It gave serialized activities on the same dates. I used match function in each row of the excel with that serial number if it exists or not and then outputs on the cell.

But Thanks for the help provided.

**:) Sixthsense :)** · 12-28-2012, 11:47 PM

In C6 cell of First Sheet - Array Formula - Requires Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

Drag it down and copy and paste the C6 formula to the right side of the cells.

Regarding your B3 cell of First Sheet, there is no Named Range is available for CalendarYear and CalendarMonth =UPPER(TEXT(DATE(CalendarYear,CalendarMonth,1),"mmmm yyyy"))

**cndesu** · 12-29-2012, 10:00 AM

Thanks for the help provided. It worked out good. I used the same formula but with current row
=IFERROR(INDEX(events,SMALL(IF(event_dates=B$5,ROW(events)),ROW(A1))),"")
I could not understand why we have to take previous row if we are checking and confirming current row?

Thanks for the help.