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...
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...
Try array-entering this formula in I6 and fill down and across to M14.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.Formula:Please Login or Register to view this content.
Then copy that range and paste into cells P6, W6, AD6 .... IT6
Dave
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.
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).
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.My apologies for not posting that before.Formula:Please Login or Register to view this content.
Try applying Fx to that.
Has this helped?
Last edited by FlameRetired; 09-14-2016 at 09:15 PM.
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
You're welcome.
Thanks for the feedback and the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks