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

I've been using the =(TEXT, "ddd") formula to specify the day! I believe it's the same?

As for the other sheet, is there any particular order that you want the items from Sheet1 to appear in?

I would like the information in sheet 2 to be arranged in chronological order, just so I can have a better idea of what events are coming up next.

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?

A single day's entry is sufficient.

Regarding the calendar in #4, is there a way that the events' title in the calendar view retain the colours used in the sheet it extracted the information from?

Okay, I'll set this up for you later - I'm working on a file for another poster at the moment, so I'll get back to this calendar file later on.

Pete

I've done the necessary modifications to the calendar file, as attached.

In the Data_Entry sheet you can add the details of your events/classes in any order in columns A, B and D, and the formulae in columns C, E and F will automatically fill in. You will notice that I've added a ranking column in F, which is used on the Activities sheet to derive the combined table in chronological order by column B. The formulae have been copied down to row 100 (as can be seen by the hyphens), and you can copy down further as required.

The Activities sheet combines the two tables into one, as requested, and shows the entries in chronological order by column B of the Data_Entry sheet. This is fully automatic, so you shouldn't need to do anything on this sheet, other than copy the formulae down below row 100 if required. Note that I have added Conditional Formatting to the table, to give you the colours that you used in your sample file.

The Calendar sheet enables you to choose the month and year of interest using the drop-downs in K3 and K4, and I have changed the year ranges in K4 as discussed earlier. I've also applied conditional formatting on this sheet to give you the colours for the two languages. I've actually amended all the formulae in this sheet, which means that it is no longer compatible with early versions of Excel, but as your version of Excel is 2013, that shouldn't be a problem.

Hope this helps.

Pete

Hi Pete,

Thank you, that was exactly what I wanted! Appreciate the help!

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this 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

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:

Please Login or Register  to view this content.