Date Recognising Formula

B

BravoBravo

New Member
Joined
Aug 10, 2008
Messages
14
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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)?????
Click to expand...

What is it that you want to *populate* when the user selects or activates a particular date & event on the first worksheet ?
 
Upvote 0
Ideally, what I am looking to do is for the entry on the event column to be populated into the calender.

For instance if the entry is:
A1 - Thursday, 1 January 2008
A2 - Call

I am trying to 'automatically' have the calender (the 2nd worksheet) record 'call' on the date 1 January 2008.

The calender format is a standard excel template, each month is spread over 3 columns and 14 rows and is manually numbered. At the moment, I have a drop down box (list) of the seven possible entries for the calender (eight including no entry), including 'call'. This then flows onto summaries, such as event by month and by year.

I am hoping to make the entire worksheet one entry (directly into the 1st workbook) and have everything behind it function automatically.

Hope this helps??
 
Upvote 0
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
Click to expand...

As you said earlier, there are 2 columns. First is the date and second is the event. Date as it can be in any format and event is a dropdown of 7 entries.

How come you are showing *A2=Call* as an event in your above post then ? You can write a simple IF() with AND() to accomplish your need or a basic SUMPRODUCT() will also do the job. I will help you once you are clear with your statements.
 
Upvote 0
Thanks Stormseed,

I'll try to clarify again.

Workbook 1 - A1 = date (for instance "Thursday, 1 January 2008")
Workbook 2 - A2 = dropdown menu of event types (for instance "call" - this is one of seven event types)

Workbook 2 - E14 = 1 Jan 08.

I am trying to work out ho when a user enters the date, such as "Thursday, 1 January 2008" can I get the event type, such as "call" to populate into the next workbook, BUT under the corresponding date.

I only want to allow users access to the first workbook but have the information reflected in the more visual calender and the summaries.

I can't work out how to use a IF and/or AND formula to do this.

The problem is that the A1 date could be "Tuesday, 9 January 2008" and I want this to populate in the next workbook under the corresponding date as equal to the event type. And so on .

Any clearer?
 
Upvote 0
Yeah, you diverted from what you said earlier. Ok, no problem.

We would like to see your data and the format. Are the dates in column A not consecutive ? I mean, 1st January in A1, then 2nd January in A3 - just because A2 has a dropdown of events ? This is some kind of weird format, dont you think so ?

Clarify this once and for all - Are the DATES in column A and EVENTS in column B ? And do you want to populate the value of column B in your second sheet depending upon the user who selects whichever date in column A ?

If yes, than I will post the answer to your problem in the next thread :)
 
Upvote 0
Thanks Stormseed

Dates are in column A and Events are in column B. However, both columns are dependant on user entry.

Column A does not contain any values - this is for the user to populate and they will not be sequential. A1 may be "Tuesday, 1 January 2008" and A2 may be "Wednesday, 9 January 2008".

Column B will also vary, with seven event types in the list for this column (including "call"). B1 may be "call" and B2 may be "briefing".

The goal is for the user to only need to enter information into this workbook, and the information will automatically flow into the other workbooks (calender and summary) - thus eliminating error and will be simpler for the user (but apparently not for me!).

The difficult I am having is getting the information to be recognised in the calender - there are 365 days and 7 events (2555 true possibilities).

Any ideas?
 
Upvote 0
Yes, if the date and events are not sequential, it does NOT matter. You could use a formula in your second worksheet as and when the user enters a date and an event on the first sheet. I will get back to you in a short while.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
2 QUESTIONS - DATE SEARCH AND OUTLOOK CALENDER
Replies
0
Views
255
nick_rubingh
N
J
  • Solved
Nested VLOOKUP + Date Comparison Error
Replies
3
Views
471
KRice
K
S
  • Question
How to shade a cell within a column if the date is in the next 6 months or less?
Replies
2
Views
510
Kerryx
K
C
  • Solved
Count number of entries that have a date stamp of last week
Replies
6
Views
477
Joe4
Joe4
P
  • Solved
Count Dates
Replies
3
Views
367
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,216,100
Messages
6,128,829
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top