+ Reply to Thread
Results 1 to 3 of 3

Reference Multiple tabs to create gantt chart

  1. #1
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Reference Multiple tabs to create gantt chart

    Hi,

    I have a master schedule which lists the start/end dates for each employee's task, along with an accompanying weekly gantt snapshot to the right. I'm trying to find a way to reflect their vacation which exists on another tab on the schedule's gantt chart, but am having a hard time figuring out the best way to do this.

    For example, if someone is booked for the full week, it will reflect as a "1" in the gantt chart. If someone is booked a full week, but has 1 day off as listed in the other tab, it would show as 0.8 in the gantt chart.

    Sample spreadsheet is attached. Any help would be greatly appreciated!!!

    Thank you!!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,047

    Re: Reference Multiple tabs to create gantt chart

    Try this in E4:

    =IF(AND($C4<=E$2,$D4>=E$3),$B4-INDEX('Out Of Office Tracker'!$D$2:$J$4,MATCH($A4,'Out Of Office Tracker'!$A$2:$A$4,0),MATCH(E$2,'Out Of Office Tracker'!$D$1:$J$1,0))/5,"")

    then copy across and down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Reference Multiple tabs to create gantt chart

    Thank you! I'm running into 2 corner cases that's preventing this from working:

    1) When an employee doesn't have vacation booked (i.e. there's nothing to match to in the Out of Office Tracker tab), the gantt doesn't populate, and
    2) If column A is left blank but there are dates (i.e. the task hasn't been assigned yet, but the task is still needed), the gantt doesn't populate.

    Any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create Gantt Chart that can be manipulated to add more steps.
    By anderem12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-17-2020, 04:46 PM
  2. Replies: 1
    Last Post: 12-15-2018, 11:06 PM
  3. How To Create Gantt Type Interval Chart?
    By jcowan35 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-13-2018, 06:58 PM
  4. Create Gantt Chart with custom times..
    By zudecke in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-06-2013, 12:37 PM
  5. [SOLVED] Using an entry form to create a gantt chart?
    By demonfly100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2012, 10:14 AM
  6. Gantt Chart - Create crews
    By yahnivek in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2009, 11:07 AM
  7. How do I create a Gantt Chart in Excel 2003?
    By Bewildered KG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2006, 03:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1