Date Recognising Formula

[BravoBravo]

Hello.

Hopefully someone can be of assistance!

I have a workbook with three worksheets - 1st is an event register, 2nd is a calender (manually done, with no formulas due to format problems I couldn't solve), 3rd is a summary.

I am trying to work out the following;
The first column of the 1st sheet (event register) requires a date.
The second column requires an 'event', from a drop box or list (of seven possibilities). These are working fine.

Is there a formula I can use theat when a user enters a date, say 1 Jan 08 (or the format is "Thursday, 1 January 2008") AND a certain event, say "CALL" that this will automatically populate the next worksheet (calender) on that corresponding date (1 Jan 08)?????

There are seven possibilities for each date (eight including "blank" or ""), which I am sure I will be able to extend to each day of the calender once I work this formula out.

I have already formulated the second worksheet (calender) to count the freqency of events into the third worksheet (summary). Hopefully these wont effect the formula I am looking for.

Thanks in advance!

 

[Stormseed]

First Sheet:

Book1
	A	B	C	D
1	Date	Event
2	Sunday,March02,2008	Call
3	Friday,March07,2008
4	Saturday,March22,2008	Call
5	Tuesday,March25,2008
6	Thursday,March06,2008
7	Monday,March03,2008
8	Saturday,March29,2008
9	Friday,March14,2008	Call
10	Saturday,March08,2008
11	Saturday,March15,2008
Sheet1

Second Sheet:

Book1
	A	B	C	D
1	Saturday,March01,2008	0
2	Sunday,March02,2008	Call
3	Monday,March03,2008	0
4	Tuesday,March04,2008	0
5	Wednesday,March05,2008	0
6	Thursday,March06,2008	0
7	Friday,March07,2008	0
8	Saturday,March08,2008	0
9	Sunday,March09,2008	0
10	Monday,March10,2008	0
11	Tuesday,March11,2008	0
12	Wednesday,March12,2008	0
13	Thursday,March13,2008	0
14	Friday,March14,2008	Call
15	Saturday,March15,2008	0
16	Sunday,March16,2008	0
17	Monday,March17,2008	0
18	Tuesday,March18,2008	0
19	Wednesday,March19,2008	0
20	Thursday,March20,2008	0
21	Friday,March21,2008	0
22	Saturday,March22,2008	Call
23	Sunday,March23,2008	0
24	Monday,March24,2008	0
25	Tuesday,March25,2008	0
26	Wednesday,March26,2008	0
27	Thursday,March27,2008	0
28	Friday,March28,2008	0
29	Saturday,March29,2008	0
30	Sunday,March30,2008	0
Sheet2

Formula in cell B2 on sheet2 copied down:

=IF(ISNA(INDEX(Sheet1!$A$2:$B$11,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$11,FALSE),2)),0,INDEX(Sheet1!$A$2:$B$11,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$11,FALSE),2))

You can replace the zeros with blanks in column B in sheet2. Just replace the above formula with the one shown below:

IF(ISNA(INDEX(Sheet1!$A$2:$B$11,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$11,FALSE),2)),"",INDEX(Sheet1!$A$2:$B$11,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$11,FALSE),2))

I hope this is what you are trying to accomplish ?