+ Reply to Thread
Results 1 to 4 of 4

Calendar/Scheduling Sheet Summary

  1. #1
    Registered User
    Join Date
    05-01-2018
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    2

    Calendar/Scheduling Sheet Summary

    I manage a small business and have been asked to summarize a calendar we have in Excel. Basically we clean buildings every quarter - 1x a year is a full cleaning, 2x are just outside, and 1x sweep/vacuum but not full cleaning --> full and sweep can't be right after each other. We schedule out about 2 years but things change regularly. Each person has like 20-25 buildings. Attached is a short example of two years.

    F = full, O = outside, S = sweep

    I'm not an Excel pro by any means but took some courses in college years ago and remember a little. Is there a way to summarize these by month and employee?

    I have a new sheet where I made a drop down for Employee and Month but I can't seem to find a way to summarize them nicely.

    I've tried a bunch of things but without success - pivot tables, combinations of giant if statements using index/match functions, arrays

    Basically if Cleaner = A and Month = January and any cell within those parameters = F, then list the names of those locations.

    Can anyone help here? Or is it too difficult for a more novice user like me to do in Excel? Any help would be appreciated - thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,111

    Re: Calendar/Scheduling Sheet Summary

    in H6

    =IFERROR(INDEX(Schedule!$A$2:$A$100,SMALL(IF((Schedule!$B$2:$B$100=Summary!$B$2)*(INDEX(Schedule!$C$2:$Z$100,,MATCH(Summary!$B$1,Schedule!$C$1:$Z$1,0))="F"),ROW(Schedule!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Then copy down

    copy to other columns and change F to O and S respectively
    Last edited by JohnTopley; 05-01-2018 at 03:32 AM.

  3. #3
    Registered User
    Join Date
    05-01-2018
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Calendar/Scheduling Sheet Summary

    Thank you John! I spent like 2 hours and got nowhere lol.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,111

    Re: Calendar/Scheduling Sheet Summary

    See the attached ..
    Attached Files Attached Files

+ 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. Scheduling any year calendar
    By jstoffle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 05:02 PM
  2. [SOLVED] Scheduling Calendar/Graph
    By lorber123 in forum Excel General
    Replies: 15
    Last Post: 08-14-2015, 10:31 AM
  3. Scheduling: Calendar style scheduling based on scheduling list
    By denkatprieto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-19-2012, 11:10 AM
  4. Scheduling Calendar
    By redkilner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 02:51 PM
  5. Custom scheduling calendar
    By silverstreak_z28 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-30-2009, 03:05 AM
  6. HOw to create EMP scheduling calendar
    By LOgle5318 in forum Excel General
    Replies: 4
    Last Post: 07-27-2007, 11:21 AM
  7. scheduling calendar
    By Disorganized receiving in forum Excel General
    Replies: 1
    Last Post: 05-03-2005, 11:06 PM

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