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!
Hi atrout,
It looks like you're trying to create a GANNT Chart.
There are loads of examples on the 'net but I've attached one I made for someone for you to check out.
Remember you are unique, like everyone else
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
I lurk around the site on most days ...Originally Posted by atrout
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).
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.
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
Hmm....I think to get it up and running we could just keep it at 10 and just do through 2019, that way we shouldn't need drop downs since it seems that is what was causing the issue with my other excel online document.
Thanks!!
Last edited by atrout; 03-08-2019 at 01:29 PM.
Just as a trial, I've set up a small workbook (attached) where you can enter a month number between 1 and 12 in cell K5 (yellow) and a year in K6 (green) - this is restricted to 2019 to 2025.
If you make a valid entry in those two cells, you should get a confirming message in B2.
Try it out with different numbers, including numbers which are outside the acceptable ranges.
Then can you put this online and try it there, and let me know the outcome - this is quite important to the operation of the calendar.
Hope this helps.
Pete
Yes, this calendar worked perfectly online!
Hi Alexis,
I've attached the completed calendar file.
First of all, you can see a sheet called Names - in this you can define up to 100 consultant names in column B, and these will be alternately coloured in different shades of blue due to conditional formatting. These can be in any order (e.g. alphabetical), but the ordering here will determine the position that they occur on the Calendar.
I've put your example data from Post #1 into columns I to R of the Activities sheet, and there is a validation check on the consultant names in column S. I would have preferred to apply DV directly to the name, but as you said that DV drop-downs do not work online, I have a separate formula for it in column S.
On the left of your data are 4 columns of formula in blue and two more in green. I've set the data table up to row 100, so the formulae in green need to be copied at least this far, although the table can be as long as you need.
The formulae in blue expand the date ranges from your data into individual entries for each date in the range. You can see that your 7 data entries have been expanded to 27 individual dates, so the blue formulae need to be copied down much further than the number of data items that you have (I've copied down to row 1000, but you can copy much further if you need to). Column B controls what is displayed on the calendar - I've set it up to display the location (column L) and project number (column K) using this formula in B2:
=IF(OR(A2="",A2="-"),"",INDEX(L:L,A2)&" ("&INDEX(K:K,A2)&")")
but you can easily change this if you wish.
Columns A to H could then be hidden, so that your data table looks the same as the one you posted.
The Calendar sheet itself shows the names on either side of the calendar, with alternate shades of blue. Data from up to 10 names can be displayed, and the display range (cell M1) can be used to display other ranges, where 1 is the range 1-10, 2 is 11-20, 3 is 21-30, and so on.
The calendar shows one month, arranged from Sunday to Saturday across. With this arrangement, a 30-day month starting on a Saturday, or a 31-day month starting on either a Friday or a Saturday, will give rise to what I call "orphan" days, where 1 or 2 days will appear on a 6th weekly block. To avoid this, I have set the calendar up so that these orphan days appear on the top of the calendar so that only 5 weekly blocks are required. You can see this for March 2019 and June 2019.
You can change the Month and Year using the cells M5 (enter 1 to 12) and M6 (2018 to 2030 are valid years). The display will change automatically, and will show the audits in coloured bars as you requested.
That's about all, so I hope this helps.
Pete
Last edited by Pete_UK; 03-11-2019 at 06:20 AM. Reason: minor spelling
Oh my gosh, Pete, this sounds amazing. I'm going to dive into this today!
Thanks!!
Alexis
OK, so this workbook is 90% amazing. The only issue I'm having is I have a template row that we copy and paste whenever we add a new activity because there are some conditional dates in the row we need. When I copy and paste that row, it doesn't update correctly in column G. F seems to update correctly, but not G......how can I accomplish adding the template row?
Thanks!!
Alexis
I think you will need to post an example of this, as I'm not clear what you are doing.
Pete
I think I figured it out - I just have to copy and paste the cells themselves and not the entire row and it seems to work fine.
I will keep testing and get back to you with any questions.
Thank you so much!!!
Alexis
Okay, come back to this thread anytime.
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 (you can still add further posts if you wish).
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 any 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). You can also leave a (private) message if you wish.
Pete
Thanks so much for the thread etiquette tips, I will do those! Still testing the spreadsheet live with my team and it seems to be working perfectly - just amazing!
Thanks,
Alexis
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks