Calendar Template Auto Update from Data

Sorry, Im new to the forum, I hope i'm posting this correctly. I have a seemingly complex problem.

date name fruit
A B C
4/15/2018 bob apples, pears
4/16/2018 fred apples, pears
4/16/2018 bob apples, pears, (oranges)
4/16/2018 phil apples, pears, oranges
4/17/2018 fred apples, pears
4/19/2018 fred pears, oranges
4/19/2018 bob pears, apples, (oranges)
4/19/2018 phil apples, pears

I have a calendar template. And I want to automatically update that calendar when the above table is updated or added to.
Everytime "oranges" appears, I want the corresponding date in the calendar to show the corresponding name.
Another problem im having is that oranges is sometimes surrounded by (). Thanks!!!

Can you pleasepost a sample of your calendar template as otherwise we are working "blind", Thank you.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hey John, thanks! I think I was able to attach it. I want the calendar in April to look like that based on the info in Sample Data, but I want the calendar to automatically change if Sample Data is changed or added to. Please let me know if the file attached correctly

So you only want to display anything if "oranges" appears in the Fruit column of the Sample data sheet, although you don't want to include "oranges" (and associated brackets if present) in the display?

You have 5 lines available for each day in your monthly calendars, but you want to display a name and a list of fruits, so wouldn't it be better to have an even number of rows?

Also, do you really need twelve sheets, one for each month? I prefer to just have one monthly sheet and in that you can select the month (and year) of interest.

Hope this helps.

Pete

Thanks Pete. I'm ok with one sheet, That was just the template I downloaded. Are you saying its easier to make my own calendar. I am definitely a novice when it comes to excel calendars. But yes, only display anything if "oranges" appears in the fruit column. If it makes it easier, I don't mind if the entire cell with oranges in it, is auto displayed on the date on the calendar. But I definitely need it to display the corresponding name. Thanks!

Okay, I'll take a look at this tomorrow (or later tonight if I have chance). I should be able to modify one of my calendar files to do what you are trying to do.

How many rows per day are you likely to need?

Would it be better to have the name and the fruits on the same row within the day?

Pete

Thanks PETE! I shouldn't need more than 6 rows. That should be enough to not need to combine the names and fruits into one row. Thanks again!

I've attached the file. You can record your data in any order in columns B to D of the Activities sheet. I suspect that in your real data you are not really looking for "oranges", so I've set this up so that you can record your actual keyword in cell F1.

In the Calendar sheet you can select the Month and Year of interest using the drop-downs in cell K11 and K12 - the display will automatically adjust, with the keyword (and any brackets around it) being substituted out.

Hope this helps.

Pete

Pete. This is absolutely amazing. Thank you so much! Not a big deal, but how do you make the Keyword NOT case sensitive. So it finds it whether its capital letter or not?

Change FIND to SEARCH.

You can change the formula in A2 of the Activities sheet to this:

=IF(AND(C2="",B2=""),"-",IF(ISNUMBER(SEARCH($F$1,D2)),B2&"_"&COUNTIF(A$1:A1,B2&"_*")+1,"-"))

(changes in red), and then copy it down by double-clicking on the fill handle with A2 selected. SEARCH is not case-sensitive, so you can use "Oranges" for your keyword and it will respond to "oranges" in the data.

However, on the Calendar sheet I have used the SUBSTITUTE function to remove the keyword from the list of fruits, and this IS case-sensitive, and so "oranges" and "(oranges)" will not be removed.

Hope this helps.

Pete