• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Required_Gantt Charts - Reservations Dashboard

anishms

Member
Hi All,
Attached is a resource reservation dash board template prepared by me. I need a help in highlighting the dates in sheet 'Availability' against the resource based on the bookings in 'reservation' sheet. I have presently highlighted the cells manually.
I also need the 'task' name to come in the start date / highlighted
 

Attachments

  • Reservations.xlsx
    24.2 KB · Views: 4
anishms
Why those ... Availability-sheets cells are different colors?
Why You do not use Chart for showing data?
Thanks for your quick response
I thought of defining different colors based on the booking vertical in 'Reservations' sheet. Same color is also fine.
I need to use this as a running dash board for the future, currently I have listed only 7 weeks in the 'Availability' sheet which will go up to 52 weeks / a year.

70353
 

Attachments

  • Reservations.xlsx
    24.4 KB · Views: 5
Yes, its working perfectly. Thank a ton!
can you tell me how is it done
If i change the sheet name "Members" to "Base", the names in the availability sheet is not displaying
Also can i have the progress ( a new column added in Reservations ) in availability like below in blue (R 20 G 77 B 146) and green (R 0 G 172 B 141) colors
And what is the system of display week changing based on the date selection
can we also place the align the text center across the dates?

70393
 

Attachments

  • Reservations.xlsb
    32.9 KB · Views: 7
anishms
Without those hundreds of formulas...
If i change the sheet name "Members" to "Base", the names in the availability sheet is not displaying
This uses Your sample files names - a sample file should be as Your needed file.
As this is solved without formulas, then changes have to do other way.
Some kind of progress, would be possible to add.
A Week as the whole seven weeks information would change per 'Start Date'.
The text was aligned as You wanted
( I also need the 'task' name to come in the start date / highlighted )
... it is possible modify ... but how about Your progress bar?
Your snapshot shows something different than Your latest writings.
I need to know - what really needs? - before start to modify that file.
 
anishms
Without those hundreds of formulas...
If i change the sheet name "Members" to "Base", the names in the availability sheet is not displaying
This uses Your sample files names - a sample file should be as Your needed file.
As this is solved without formulas, then changes have to do other way.
Some kind of progress, would be possible to add.
A Week as the whole seven weeks information would change per 'Start Date'.
The text was aligned as You wanted
( I also need the 'task' name to come in the start date / highlighted )
... it is possible modify ... but how about Your progress bar?
Your snapshot shows something different than Your latest writings.
I need to know - what really needs? - before start to modify that file.
Thanks for your quick response.
The snapshot was from one of my other workings where the member name and tasks are displayed in one sheet. (I have attached same for your reference). I wanted to change this format to the one you did

Cold you please change the sheet name "Members" to "Base for final
Please also add the progress if possible
And fix the calendar to 1 year from 01-Apr 2020 to 31-Mar-2020. I tried to do it, but the dates are not changing and the reservations are not highlighted in the added dates
About the text, if the task name is long / date range is short, then text if going out of the color
Thanks in advance
 

Attachments

  • IA Team Scheduler 2020-21 V1.xlsx
    66.4 KB · Views: 5
  • Reservations.xlsb
    32.9 KB · Views: 3
anishms
Your I wanted to change this format to the one you did
... means that all task with one line - or how?
Your fix the calendar to 1 year from 01-Apr 2020 to 31-Mar-2020.
... hmm? That's a challenge ... minus one day?
... what would You really mean?
... I would use eg from current week and after that six further weeks.
Your
About the text, if the task name is long / date range is short, then text if going out of the color
... one more time ... what would that mean?
 
Yes, one row, the one you worked.
Sorry it's a typo error, I mean April 2020 to Mar 2021.
Can you make it a quarter view (13 weeks) instead of 7 weeks now, so that i can change the date to beginning of each quarter and see the availability
With respect to the text, I mean if the task text is lengthy, it is going beyond the color. It's ok for now as the next task text will come above that (no changes required)
70397
 
anishms
You can 'play' with this calendar controls
... You can step Qrt, Month, Week - back & forward as well as get back to 'today'
You can modify tasks colors as well as 'today's color as You would like see from base-sheet
 

Attachments

  • Reservations.xlsb
    40.7 KB · Views: 3
Awesome work.
Thank you very much
Could you please share the codes, so that I can learn and try to make modifications if any required
 
@ vletm - a small change required
today, colured in yellow is not visible if all the members have booking for today as it is going behind the bg1 and bg 2 colors
Can you make today to be highlighted only on the date column as shown below
70455

I tried to change it my own by replacing 7 to 5 but that alone is not working :)

Thanks in advance
 

Attachments

  • IA Team Scheduler V2.xlsb
    43.5 KB · Views: 5
anishms
Truths - There are no any small changes!
There could be possible changes or
changes which would mean a lot of extra coding.

Your 'try' - won't work ... or at least, it would have 'side effect'.
I did modification to my file - it would be something like You have tried to do.

Do You really would like to use white color for those fonts? ... That would work if ... all other cells are dark .. otherwise ... it's Your choice!
 

Attachments

  • Reservations.xlsb
    41.3 KB · Views: 17
Thanks again, I have changed the colors to dark based on the organization's started colors. So, white font looks good
 
Yes, one row, the one you worked.
Sorry it's a typo error, I mean April 2020 to Mar 2021.
Can you make it a quarter view (13 weeks) instead of 7 weeks now, so that i can change the date to beginning of each quarter and see the availability
With respect to the text, I mean if the task text is lengthy, it is going beyond the color. It's ok for now as the next task text will come above that (no changes required)
View attachment 70397
Hey which formula you are using to create this
 
anishms
Yes, white fonts looks good with white background...
Happy New Year vletm!
Can you help with following changes

(1) Change the font size of a part of the value to a smaller size (Text after ":")
bc = .Cells(b, "B") & " - " & .Cells(b, "C") & " : " & _ per & " - (" & .Cells(b, "J") & "/" & .Cells(b, "I") & ")" & " [" & LM & "|" & QM & "]"
Required Result Example : GCC Units - Assignment - 34 : 80% - (35/35) [PS|HIA]

(2) It would be great if you can extend your help in the following hardcoded one to automatically choose from sh3 = "Base"
LM = .Cells(b, "F") Select Case LM Case "Manager 1" LM = "KD" Case "Manager 2" LM = "GM" Case "Manager 3" LM = "KB" Case "Manager 4" LM = "PS" Case "Manager 5" LM = "AM" Case "Manager 6" LM = "MS" Case "Manager 7" LM = "AB" Case Else LM = LM End Select QM = .Cells(b, "G") Select Case QM Case "Quality 1" QM = "HIA" Case "Quality 2" QM = "KB" Case "Quality 3" QM = "PS" Case Else QM = QM End Select

Thanks in Advance!
 

Attachments

  • Manpower Scheduler.xlsb
    51.2 KB · Views: 13
Back
Top