Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Gantt Chart

  1. #1
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013

    Gantt Chart



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. I have created a dailywise progress report of a project using conditional formatting for estimated and actual progresses. The project is of approx 6 months duration. The project being a lengthy one, it is not possible to print the whole progress in a single page. For that purpose, I have created a dynamic calendar showing 12 months at a time upto the year 2049 in the next worksheet. Now I am unable to use any formula across these two worksheet. My aim was that the calculations in the first sheet (Actual) will be displayed in the calendar of 2nd sheet as colour fill just like the first sheet. The first month and year of the calendar should be automatically selected from the start date of project (from the first sheet), so that the total progress can be be shown in single page itself. Additionally suggest me how can I create a database for such multiple projects?

    Kindly help.
    Test duration - Comparison.xlsx

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Here is the CF formula for Green, value A

    =AND((INT((ROW()-1)/9))*3+INT((COLUMN()-COLUMN($F$1)+1)/8)+1=MONTH(F4),INDEX(Daywise!$6:$27,MATCH("A",Daywise!$A$6:$A$27,0)+1,MATCH(F4,Daywise!$5:$5,0))="A")

  3. #3
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Thank you @BobPhillips. But not getting where to use this formula. It will be highly helpful if you kindly elaborate or upload as an example on my excel worksheet.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Use it as the CF formula in all oif the date cells in your calendar months.

  5. #5
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Thank you so much sir,
    But the same is not working for me.

    Quote Originally Posted by Bob Phillips View Post
    Use it as the CF formula in all oif the date cells in your calendar months.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Then you must have done it incorrectly, it works fine.
    Attached Files Attached Files

  7. #7
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Wow, you are awesome sir, I will try to complete it now. Thank you so much. And will keep asking for your help if you don't mind.

    Quote Originally Posted by Bob Phillips View Post
    Then you must have done it incorrectly, it works fine.

  8. #8
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by lodhnerist View Post
    Wow, you are awesome sir, I will try to complete it now. Thank you so much. And will keep asking for your help if you don't mind.

    I appreciate your kind help, done it as you suggested and it is awesome. Now the only issue I am having is with my first month and the subsequent months of the calendar. If it is possible to select the calendar automatically from the start date of project, and continue for next 11 months automatically, then only the project can be visible. Otherwise, I have to select every month manually and apply conditional formatting for all the months up to 2049. Is it any how possible to do that?

    Test duration 12.05.2020.xlsx

  9. #9
    Seeker lodhnerist's Avatar
    Join Date
    May 2020
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Its working fine when I am using the dates from Jan 2020 to Dec 2020 in the calendar but not working when I am changing the month and year as per my project duration. Unable to find out the fault. Please see the attachment for clarification.
    Attached Files Attached Files

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    My solution was predicated on the first month shown in the calendar is January. It wasn't designed tio work if you change the first displayed month to Feb, Mar, Apr, …

    I could try and come up with the formula, watch this space.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •