Help with posting data to Calendar with dropdowns.

Anyone able to help?

Yes, I'll get back to you later on, after I've watched a bit of TV.

Pete

Great, thank you Pete. I look forward to seeing what you come up with. Thank you in advance.

I don't understand your data in your Properties sheet. I would expect there to be a date in column F, though you just show a single number. In column G you just show another number - is that meant to be the duration in days that you want the display to be shown?

Also, I presume you would want the Item column to be displayed on the calendar?

Pete

My apologies for the confusion, i had broken the date up in my attempts to fix the problem. The month is column F and the day is column G. If the formula works better when the entire date in one cell by all means go ahead. And you are correct, i would like the item to be displayed on the calendar.

Thank you.

I'm setting this up so that you can choose the month and year for the calendar to be displayed from two drop-downs. I also have another drop-down where you can select the Property (Alpha, Beta, Gamma etc.), or leave this blank to display all of them. Can you tell me how many different property names are you likely to have? (so I can set up the named range accordingly - well, a bit bigger, to account for more data being added).

I'm assuming that you will have a start date in column F of your Properties sheet. Does that mean that column G is no longer needed, or could it be used for an end date, if the display is to represent a range of dates for each property? Are your entries only for single days at a time?

On my calendar sheet I can display up to 10 distinct "events" for each day. Is that sufficient for you, bearing in mind that you can display ALL properties, as well as individual ones? I have the facility (via another drop-down) where you can display up to 10 "events", but you can also select a range of 10, so that you can have 1-10, or 11-20, or 21-30 etc. Do you want to retain this facility?

I'm basing the calendar on the one I posted in this thread in March:

https://www.excelforum.com/excel-gen...-calendar.html

in case you want a preview of what it might look like. Please get back to me with answers to the latest set of questions.

Hope this helps.

Pete

The drop downs are key thank you.
There will be about 50 properties, we have a little less now but just optimistically speaking.
G is no longer needed if you put the whole date (mm/dd/yyyy) in column F.
10 items a day should also be sufficient as the most per day now is 4.
Your audit calendar is ideal, adding the ability to sort by individual property and see events for just that property is key.

Thank you Pete.

See if the attached file does what you are after.

You can see that I have added a sheet called Names, which has a named range (also called "Names") defined as the range B2:B101 on this sheet, and in this range you can enter your property names - just add any new ones to the bottom of the list.

In the 'Properties and Items' sheet you can enter your data into columns D to F as required. These can be entered in any order, so there is no need to insert or delete any rows (and that would probably mess up the formulae anyway). I've added some dummy data to test a few things out, so you can just delete that after you've played about with it. There are two columns of formulae in A and B, and these have already been copied down to row 1000, but you can copy these further if you wish. The formula in column A sets up a unique reference depending on the date in column E, and column B controls what is displayed. You can hide columns A to C if you wish, so that the sheet looks just like your data table.

In the Calendar sheet you can select the month and year of interest using cells K5 and K6, and the display will automatically adjust. In a display that covers the week from Sunday to Saturday, a 30-day month which starts on a Saturday or a 31-day month which starts on either a Friday or a Saturday will need 6 weeks worth of rows to display the complete month by spilling into the Sunday (and possibly the Monday) of the 6th week. In order to reduce the amount of screen space, I have arranged for these "orphan" days to appear on the top row of the calendar, as can be seen for March 2019 and June 2019.

There is another drop-down in cell K1, and with this you can select the name of the property - doing so will result in only those property details being shown. You can also leave K1 blank, and this will result in all details being displayed. In this case, the display will also include the property name, for clarity.

Anyway, play about with it, and let me know what you think.

Hope this helps.

Pete

Pete,
This is perfect. You are an absolute legend. Can you point me in the direction of some material where i can better understand exactly what you did to get these results? I'd like to learn more about excel than the basics i learned during schooling.

Again thank you so much

You're welcome, and I'm glad it works for you.

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).

It is difficult to suggest where you might be able to find sites to give you a better understanding of how it works, as there is so much out there on the internet. If you have any specific questions about any of the formulae, then I will try to answer them here, and in fact this site is a great resource for learning more about Excel, so just browse through some of the threads as takes your fancy.

Hope this helps.

Pete