How to Auto Populate Info from Table to Calendar based on date

Hello Everyone,

I am new to the forum but would love to seek your expertise on an excel query.

I am working on a TRP schedule with dates in table format(enterprise roll-up calendar). My goal is to have the calendar view (Monthly TRP Calendar) auto populate meeting information based on the date in the enterprise roll up calendar. My standard process is to update the table view, but I'm trying to avoid double entry in updating the calendar as well. I would like to update the worksheet for the date 2/20 and have the calendar auto populate column J on the date of 2/20.

Thank you in advance and I have attached a sample of the data.

Tanya

What is it that you want to appear on the calendar sheets? The data from the Time and Location columns?

Pete

Originally Posted by Pete_UK

What is it that you want to appear on the calendar sheets? The data from the Time and Location columns?

Pete

I would like the leader information to populate in the calendar on the corresponding date.

You already have some comments on some days, e.g. Valentine's Day on 14th February. How do they get populated in your calendar? If I give you a formula to populate the cells underneath the day, it will overwrite those other entries.

Pete

Originally Posted by Pete_UK

You already have some comments on some days, e.g. Valentine's Day on 14th February. How do they get populated in your calendar? If I give you a formula to populate the cells underneath the day, it will overwrite those other entries.

Pete

I'm fine with the holidays being overwritten since they are not required. The leader information in column J is more important.

I've re-worked your calendar so that it now operates off dates, rather than the numbers that you had before. This means that there are formulae on the top line of each week which basically increment the date, and the cell is formatted to show only the day number. This has another benefit, in that it can be made to adjust automatically - you can put a different year in cell B1, and a calendar for that year will be automatically generated. I have used conditional formatting to shade the days in alternate months.

The information that you want can be derived using this formula in C4:

=IFERROR(INDEX('Enterpise Roll-Up TRP Calendar '!$J:$J,MATCH(C3,'Enterpise Roll-Up TRP Calendar '!$N:$N,0))&"","")

which I have copied across on the second line for each week. This returns the data from column J of the other sheet if column N of that sheet has a date matching the cell that the formula is in (these columns shown in red above, for clarity). You can see the names Hacey, Drew and Cork on the dates 20th Feb, 5th March and 10th March respectively.

Hope this helps.

Pete

Originally Posted by Pete_UK

I've re-worked your calendar so that it now operates off dates, rather than the numbers that you had before. This means that there are formulae on the top line of each week which basically increment the date, and the cell is formatted to show only the day number. This has another benefit, in that it can be made to adjust automatically - you can put a different year in cell B1, and a calendar for that year will be automatically generated. I have used conditional formatting to shade the days in alternate months.

The information that you want can be derived using this formula in C4:

=IFERROR(INDEX('Enterpise Roll-Up TRP Calendar '!$J:$J,MATCH(C3,'Enterpise Roll-Up TRP Calendar '!$N:$N,0))&"","")

which I have copied across on the second line for each week. This returns the data from column J of the other sheet if column N of that sheet has a date matching the cell that the formula is in (these columns shown in red above, for clarity). You can see the names Hacey, Drew and Cork on the dates 20th Feb, 5th March and 10th March respectively.

Hope this helps.

Pete

It functions exactly as I had envisioned. Thank you so much for your help, I appreciate it more than you know

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete