Birthday got overlooked

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone.

I have a problem that I hope someone can help me with. I'm a teacher, and
I've been using excel only a short time. I use it to keep track of my
students grades, and other info. What I've been doing is checking the
students grades weekly. When I run my report, I wanted to know if any of my
students birthdays are coming up in the next week, I accidentally overlooked
two in the last week. The way its setup is I have my students birthday in
column D6 to D37, and in column E6 to E37 their names right next to the dates
(this is on a seperate sheet). On the worksheet, in cell A1, I wanted it to
print the names of the students that are having their birthdays. I have each
week already set up with the dates that will be included. Here's an example.

(on the info sheet)

03/27/1998 Billy Jones
04/15/1998 Sally Smith
01/21/1998 Tina Wescott (and so on)

On the worksheet I have

XXXXXX Elementary School
Week of 03/27/2006 to 03/31/2006

(What I want in Cell A1)
Happy Birthday Billy Jones

Please help me. I felt really bad when I overlooked the two last week, and
don't want a repeat. Thank you for your help in advance. Suzie
 
Hi Suzie,

Is it possible to use column F on the info sheet (I assumed that its name is
"infosheet") as a helper column? If yes, insert this formula into cell F6 and
fill it down until F37:
=TEXT(MONTH(D6),"00")&TEXT(DAY(D6),"00")

Then insert this formula into cell A1 on the worksheet:

=IF(ISERROR(MATCH(TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00"),infosheet!F6:F37,0)),"Today
nobody has a birthday!","Happy birthday
"&INDIRECT("infosheet!E"&MATCH(TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00"),infosheet!F6:F37,0))&"!")

Regards,
Stefi

„Suzie Weston†ezt írta:
 
Perhaps something like this:

The below will list up to 3 students with birthdays in the specified week.

With your data in D6:E37 (birthdays in Col_D, Names in Col_E)
D4: (contains the first Monday of the week in any date format)

A1:
=INDIRECT("$E"&SMALL(IF(ISNUMBER(MATCH(--TEXT($D$6:$D$37,"mmdd"),--TEXT($D$4+{0,1,2,3,4},"mmdd"),1)),ROW($D$6:$D$37),9999),ROW(A1)))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Copy A1 and paste into B1:C1

Select A1:C1
<Format><Cells><Number Tab>
Category: Custom
Type: "";"";""
Click the [OK] button

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Small but significant formula correction:

Should be:
A1:
=INDIRECT("$E"&SMALL(IF(ISNUMBER(MATCH(--TEXT($D$6:$D$37,"mmdd"),--TEXT($D$4+{0,1,2,3,4},"mmdd"),0)),ROW($D$6:$D$37),9999),ROW(A1)))

(Still must Ctrl+Shift+Enter)

I changed the number 1 in this section:
{0,1,2,3,4},"mmdd"),1)
to a 0 (zero)
{0,1,2,3,4},"mmdd"),0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Ron Coderre said:
Perhaps something like this:

The below will list up to 3 students with birthdays in the specified week.

With your data in D6:E37 (birthdays in Col_D, Names in Col_E)
D4: (contains the first Monday of the week in any date format)

A1:
=INDIRECT("$E"&SMALL(IF(ISNUMBER(MATCH(--TEXT($D$6:$D$37,"mmdd"),--TEXT($D$4+{0,1,2,3,4},"mmdd"),1)),ROW($D$6:$D$37),9999),ROW(A1)))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Copy A1 and paste into B1:C1

Select A1:C1
<Format><Cells><Number Tab>
Category: Custom
Type: "";"";""
Click the [OK] button

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


Suzie Weston said:
Hello everyone.

I have a problem that I hope someone can help me with. I'm a teacher, and
I've been using excel only a short time. I use it to keep track of my
students grades, and other info. What I've been doing is checking the
students grades weekly. When I run my report, I wanted to know if any of my
students birthdays are coming up in the next week, I accidentally overlooked
two in the last week. The way its setup is I have my students birthday in
column D6 to D37, and in column E6 to E37 their names right next to the dates
(this is on a seperate sheet). On the worksheet, in cell A1, I wanted it to
print the names of the students that are having their birthdays. I have each
week already set up with the dates that will be included. Here's an example.

(on the info sheet)

03/27/1998 Billy Jones
04/15/1998 Sally Smith
01/21/1998 Tina Wescott (and so on)

On the worksheet I have

XXXXXX Elementary School
Week of 03/27/2006 to 03/31/2006

(What I want in Cell A1)
Happy Birthday Billy Jones

Please help me. I felt really bad when I overlooked the two last week, and
don't want a repeat. Thank you for your help in advance. Suzie
Click to expand...
Click to expand...
 
Stefi

I really like the original question and your reply. However, it didn't work for me. I re-created the OP's infosheet and worksheet, data and titles. When I enter your code, the Happy Birthday message quotes the name in D8 even tho that person's b'day is not today. I added more names after D8. One name is newborn, and entered birthday as =today(); and I entered personx with birthday May 31, 1999 and neither gave me a positive result. I think I entered your code exactly as written; I copy/pasted and then deleted the linefeeds.

Thanks in advance for any suggestions.
 
Back
Top