+ Reply to Thread
Results 1 to 7 of 7

Scheduling

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Scheduling

    There seems to be lots of 'scheduling' templates around but none seems to be able to schedule from a list. If someone can point me in the right direction or suggest a solution (preferably formulaic) to the attached spreadsheet's solution sought...
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Scheduling

    Try array-entering this formula in I6 and fill down and across to M14.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Then copy that range and paste into cells P6, W6, AD6 .... IT6
    Attached Files Attached Files
    Dave

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: Scheduling

    That, my friend, is very, very smart. No, make that very, very spooky. Thank you. I was trying =sumproduct and =index/match/match and getting nowhere.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Scheduling

    Thanks for the feedback and rep.

    RE: your PM for explaining how it works. Will do, but I need to hold off for a few days. It's that time of year (getting ready for winter).

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Scheduling

    Was able to catch a break.

    The heart of this formula

    =IFERROR(INDEX($C$18:$C$29,SMALL(IF((I$5>=$E$18:$E$29)*(I$5<=$F$18:$F$29)*($D6=$D$18:$D$29),ROW($B$18:$B$29)-MIN(ROW($B$18:$B$29))+1),1)),"")

    Is this part.
    (I$5>=$E$18:$E$29)*(I$5<=$F$18:$F$29)*($D6=$D$18:$D$29)

    each factor evaluates to an array of TRUE and FALSE. For example select one of them in the formula bar and hit the F9 function key. You will see something like this:

    {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

    The effect of multiplying TRUE and FALSE is to coerce T/F to their underlying numeric values of 1s and 0s.

    If you were to select that section above in the formula bar and press the F9 function key you might see something like this.

    {1;0;0;0;0;0;0;0;0;0;0;0}

    That is the effect of multiplying all the 1s and 0s.

    This is passed to the IF function first argument which assigns index numbers by the part

    ROW($B$18:$B$29)-MIN(ROW($B$18:$B$29))+1

    conditional upon each element in the array equaling 1.

    For example

    {1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

    The numbers correspond with the row location(s) of the 1s.

    This is passed to SMALL stipulating the first smallest value in the array. [ SMALL(formula,1) ]

    That is passed to the row argument of INDEX instructing INDEX to return the item from that row of $C$18:$C$29. In this case 1/16N.

    IFERROR cleans up any remaining errors returning empty strings.

    There is a feature in Excel called ‘Evaluate formula’ (Fx). Click on the cell in question, click Formulas > Evaluate formula. A dialogue box appears. With each click of the Evaluate button Excel reveals step by step how the formula is evaluated.

    I find F9 and ‘Evaluate formula’ helpful for self-instruction and formula dissection. I recommend them whenever I can.

    I would also suggest another formula simpler and I believe more efficient. It is array-entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My apologies for not posting that before.

    Try applying Fx to that.

    Has this helped?
    Last edited by FlameRetired; 09-14-2016 at 09:15 PM.

  6. #6
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: Scheduling

    Thank you for taking the time to explain - the array stuff I am more or less knowledgeable about - it's the (obvious) use of SMALL that I wasn't interpreting correctly.

    Again, thanks for your help

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Scheduling

    You're welcome.

    Thanks for the feedback and the rep.

+ 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 Help!
    By MissSia in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-04-2014, 11:06 AM
  2. Scheduling help
    By balfate73 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2013, 02:39 PM
  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 Help
    By MastahFez in forum Excel General
    Replies: 3
    Last Post: 12-12-2011, 11:24 PM
  5. scheduling
    By vikkam in forum Excel General
    Replies: 0
    Last Post: 08-25-2006, 11:30 AM
  6. Scheduling
    By freddie mac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2006, 11:46 AM
  7. Scheduling
    By Adam R via OfficeKB.com in forum Excel General
    Replies: 1
    Last Post: 07-15-2005, 06:05 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