+ Reply to Thread
Results 1 to 7 of 7

List All Birthdays and Anniversaries in Calendar Months From Events Sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    04-05-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    45

    List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    Hello,

    I would like the Calendar months 1-12 to have the Birthdays and Anniversaries listed from the Events sheet with the day they occur in the bottom yellow area of the Calendars.

    For instance in March month (3):

    Ken's Birthday is on the 26th
    Fred's Birthday is on the 28th
    Karl's Birthday is on the 28th

    Does anyone know formulas to do this please and also highlight the day of the month that these occur?

    I manually entered them in March and highlighted the days they occur.

    Thank you so much for your help
    Last edited by ynab; 04-15-2020 at 01:54 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    Please try at J35
    Formula: copy to clipboard
    =IFERROR(INDEX(Events!$F$54:$F$63&" is on the "&Events!$I$54:$I$63&MID("thstndrdth",MIN(RIGHT(Events!$I$54:$I$63),4)*2+1,2),AGGREGATE(15,6,ROW(Events!$H$54:$H$63)/(MONTH($B$4)=Events!$H$54:$H$63),ROWS(J$35:J35))-ROW(Events!$F$53)),"")


    For highlighted
    CF formula applies to =$B$4:$O$39

    =COUNTIFS(Events!$I$54:$I$63,DAY(B4),Events!$H$54:$H$63,MONTH(B4))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-05-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    45

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    Thanks but it isn't getting the Wedding Anniversary on May 9 or Jessica's Birthday in September
    Last edited by ynab; 04-15-2020 at 04:12 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    change the formulas beside the day
    =IF(ISERROR(MATCH(H$28;Events!$L$9:$L$50;0));"";INDEX(Events!$F$9:$F$50;MATCH(H$28;Events!$L$9:$L$50;0)))
    to
    =IF(ISERROR(MATCH(H$28;Events!$L$9:$L$63;0));"";INDEX(Events!$F$9:$F$50;MATCH(H$28;Events!$L$9:$L$63;0)))
    $50 --> $63

  5. #5
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    change the formulas beside the day
    =IF(ISERROR(MATCH(H$28;Events!$L$9:$L$50;0));"";INDEX(Events!$F$9:$F$50;MATCH(H$28;Events!$L$9:$L$50;0)))
    to
    =IF(ISERROR(MATCH(H$28;Events!$L$9:$L$63;0));"";INDEX(Events!$F$9:$F$50;MATCH(H$28;Events!$L$9:$L$63;0)))
    $50 --> $63

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    Please change to $C$2

    Formula: copy to clipboard
    =IFERROR(INDEX(Events!$F$54:$F$63&" is on the "&Events!$I$54:$I$63&MID("thstndrdth",MIN(RIGHT(Events!$I$54:$I$63),4)*2+1,2),AGGREGATE(15,6,ROW(Events!$H$54:$H$63)/(MONTH($C$2)=Events!$H$54:$H$63),ROWS(J$35:J35))-ROW(Events!$F$53)),"")

  7. #7
    Registered User
    Join Date
    04-05-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    45

    Re: List All Birthdays and Anniversaries in Calendar Months From Events Sheet

    Thanks Bo_Ry that was the issue and it is solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto populate calendar from list of events in 2 tables
    By blndsjy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-08-2022, 01:05 PM
  2. Replies: 2
    Last Post: 12-30-2018, 07:51 AM
  3. [SOLVED] Populate calendar based on external list of events
    By cnak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2018, 10:12 AM
  4. If Then / Offset Formula [populate calendar from a list of events]
    By trlear in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2018, 07:44 PM
  5. Auto populate events to excel calendar from a list
    By lridley2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2016, 05:22 PM
  6. [SOLVED] Calendar to identify birthdays
    By David HK in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 02-27-2013, 01:05 PM
  7. Populating a calendar with list of events
    By bwells3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2011, 05:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1