help auto populating a calendar

Please help, I've been trying to figure this out from a number of examples of auto populated calendars but I can't figure out how to take the information from sheet 1 to auto populate into my calendar on sheet 2, I would like it to look like sheet 3 when complete.
I hope my example attached....
Thank you!

Thank you so much for this suggestion - it doesn't seem to fit exactly with what I want though. We keep adding information to sheet 1 so I need the calendar to automatically update and sort by each auditor assigned. I've attached a file I found in another thread that seems to be exactly what I need, but I can't figure out how to change it to my data set.
Thanks again!

That's one of mine (there are many others, with slight variations to suit each OP's data arrangements).

It's not clear what you want to do with the data, as the layout in the file you submitted seemed to suggest a Gannt chart style arrangement.

Tell me what you want to display and I'll try to amend the file to suit your requirements. Are you hoping to give each auditor a separate colour? Do you want each auditor to appear on the same relative line of the calendar? How many auditors do you hope to display ? (up to 7 can be displayed on that particular version, but I have other versions which display 10, 12, 25, and even 100+ in ranges of 10).

I shall be going out shortly, but I can pick up your responses later on.

Hope this helps.

Pete

Hi Pete - it's awesome that I found you!
I would love to keep the look of the calendar linear like my example, but using an actual calendar format like your sheet is great too. However (and I've seen your version like this) there would need to be 2 columns under the date, 1 that has the consultant and the other that has the location. Right now we would only need to display 10, but we may need to increase that in the future. Maybe you could show me how to change it or give me an example for 10 and also one for 20. It probably would be best if the same consultant would be listed on the same line so that we can clearly see their continuous days.
I was thinking after the calendar is done I can just do a conditional format to highlight each consultant name with a different color for the whole sheet?
Thank you so much for your help and if you have any sort of tutorial on what the code is doing, I would love that so that I actually understand it and can change it in the future if needed.

Alexis

Originally Posted by atrout

Hi Pete - it's awesome that I found you! …

I lurk around the site on most days ...

Originally Posted by atrout

... there would need to be 2 columns under the date, 1 that has the consultant and the other that has the location ...

I disagree. If consultant Joe Bloggs is always listed on line 1 of the calendar (in column A), then you can just show the location and project number in the calendar displays (that's if you want the project number, which is what you showed in your original example).

Originally Posted by atrout

… Maybe you could show me how to change it or give me an example for 10 and also one for 20 ...

I'll set it up in one that gives you a range of up to 10 rows per day, where you can select the ranges in increments of 10.

Originally Posted by atrout

... I was thinking after the calendar is done I can just do a conditional format to highlight each consultant name with a different color for the whole sheet ...

I was thinking of that originally, but you could easily run out of colours, so if each auditor has their own row then the CF could use the same colour of them all - the bands (date ranges) will still show up clearly.

As regards the working of the file, it is all formula-driven, so there are no macros to enable and the formulae are fully visible for you to work through. I do not have a tutorial, but I'm here most days to help explain what is happening.

Hope this helps for now.

Pete

You are absolutely right about not needing 2 columns, your suggestion would work great.
I'm excited to see the file - thanks so much!

Alexis

Hi again Pete, to throw in one possible complication we use this sheet live through excel online and I just found out that some of the drop down lists don't work in the online version of excel. Is there a way to change the months without a drop down list, or anything that is compatible with the online version.
Thanks so much!

I've not used Excel online, so I don't know what problems that gives rise to.

You don't need to use the drop-down, but it is important that the numbers entered are limited to between 1 and 12, so I think data validation is still needed. I can set it up so that you can enter a whole number between 1 and 12, rather than have a list to choose from. Does the year drop-down work okay? (That also uses a list at the moment). I was also thinking of having another drop-down so you can choose the range to display, i.e. "1 to 10", "11 to 20" and so on, so that might require a re-think.

Hope this helps.

Pete