+ Reply to Thread
Results 1 to 2 of 2

Reference Gantt Chart from Multiple Tabs to Create Master Schedule for Specified DateRange

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

    Reference Gantt Chart from Multiple Tabs to Create Master Schedule for Specified DateRange

    Hi,

    I'm trying to reference data from multiple tabs ("cities" tabs) to create a "summary" tab. This summary needs to list which employees are available within a given date range (as defined in column H yellow cells on the summary tab).


    Cities Tabs
    - Employees are listed in their respective city tabs, along with a gantt chart whether they are booked or available each week (blank squares indicate availability).


    Summary Tab
    - This should pull info from the cities tabs, and list the blank weeks for each employee between the date range in column H (yellow cells).

    - The red text in this tab is that expected values -- how would I automate this?


    Any insight would be greatly appreciated!!!

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Reference Gantt Chart from Multiple Tabs to Create Master Schedule for Specified DateR

    On each of the city sheets M1 is populated using: =SUMMARY!H1 and N1 using: =SUMMARY!H2
    Column M is populated using: =COUNTIFS(D2:L2,"",D$1:L$1,">="&M$1,D$1:L$1,"<="&N$1)
    Columns N:W are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    On the summary sheet columns A:B are populated using: ='Los Angeles'!A2
    Column C is populated using: =IF(E3=0,"",'Los Angeles'!N2)
    Column D is populated using: =IF(E3=0,"",LOOKUP(2,1/('Los Angeles'!N2:Z2<>""),'Los Angeles'!N2:Z2)+6)
    Column E is populated using: ='Los Angeles'!M2
    Note that in each section 'Los Angeles' is replaced with the city name.
    Note that two cells in column D which I filled in red as the person has some dates within the range when they are not available. You'll need to decide how you would like to handle that, at which point we may then be able to adjust the formula.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Employee daily schedule Gantt chart
    By ryesner in forum Excel General
    Replies: 1
    Last Post: 11-12-2017, 07:46 PM
  2. [SOLVED] Create a master list of names from multiple tabs
    By rob.callaghan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-31-2017, 10:51 AM
  3. collating multiple tabs within same workbook to create a master/summary tab
    By xotrujillo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-02-2016, 05:15 PM
  4. create daily schedule using master task schedule
    By DCO in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 01:08 PM
  5. vacation schedule gantt chart
    By bugoy in forum Excel General
    Replies: 2
    Last Post: 06-08-2010, 11:19 AM
  6. Payment Schedule Using Gantt Chart
    By mohagany19 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-06-2008, 05:36 AM
  7. [SOLVED] Create patient schedule based on master therapist schedule
    By PapaBear in forum Excel General
    Replies: 8
    Last Post: 10-12-2005, 12:05 AM

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