Auto populate calendar from list of events in 2 tables

In the attachment you referred to , change the Data Validation list in K4 of "Calendar" to the years you require.

Hi, thanks for the help but I've tried it to this message:

"The value you entered is not valid. A user has restricted values that can be entered into this cell."

Any ideas how to resolve that?

You need to select cell K4, then click on Data | Data Validation | Data Validation (yes, twice), and then you will see the list of years separated by a comma. Add other years to the end of the list, with a comma between them, and you can remove earlier years from the list if you no longer need them - Data Validation drop-downs only show 8 items on screen, so by removing the earlier years you won't have to scroll down so much.

It would help to understand your 2-table query if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Hi Pete,

Thanks a lot for the help!

I've attached the sample with this post. Sheet 1 contains information that I will input manually. Sheet 2 is the table I would like to auto populate.

You don't really have to specify the day in columns C or E in the first sheet, as this can be done with a formula, e.g. with this in C8:

=IF(B8="","",TEXT(B8,"ddd"))

and this in E8:

=IF(D8="","",TEXT(D8,"ddd"))

Copy those two formulae down as far as you think you might need.

As for the other sheet, is there any particular order that you want the items from Sheet1 to appear in? If you want to retain the colouring for English and Chinese, it might be better to have another column for "E" or "C", so that you can easily see where the data has come from. Are you likely to want to enter a range of dates in Sheet1 (i.e. from start_date to end_date), or is a single day's entry for each event sufficient?

Pete

This is great, however, I have two types of events, daily and weekly. You spreadsheet works great for daily but I'm having trouble with weekly.
I expanded the calendar by adding another row above Day of week. Some events only happen, for example of Tuesdays, every Tuesday.
Can you help?

Originally Posted by chuckf201

This is great, however, I have two types of events, daily and weekly. You spreadsheet works great for daily but I'm having trouble with weekly.
I expanded the calendar by adding another row above Day of week. Some events only happen, for example of Tuesdays, every Tuesday.
Can you help?

Problem solved:

=IF(ISNA(MATCH(D$10&"_"&$A2,Activities!$A:$A,0)),"",INDEX(Activities!$C:$C,MATCH(D$10&"_"&$A2,Activities!$A:$A,0)))