Check Range for Minimum and Repopulate the Minimum

Hi Woutie

In the attached, revised file you can change the capacities in C4:C6 and the constraint machine(s) will be highlighted in orange. 

D9 will show the constraint capacity since its formula is simply:

=MIN(C4:C6)

i.e it  shows the lowest value in those three cells.

To the left, in C9, the (first) machine with that constraint will appear since the formula is:

=INDEX(B4:C6,MATCH(MIN(C4:C6),C4:C6,0),1)

where the bit in bold matches the minimum in C and the INDEX arguments either side refer to B4:B6 and its first column (so the formula gives the value to the left of the Minimum value, say the second row in that range). The combination of INDEX/ MATCH is like a more sophisticated version of VLOOKUP- please look in the Tutorials section to learn about using them.

The orange Conditional Formatting is done by clicking in B4, going ribbon Home/ Styles/ Conditional Formatting  (down) New Rule then picking Use a formula to determine which cells to format.

For B4, the formula is:

=If($C4=Min($C$4:$C$6), 1, 0)

where the bold bit means ONLY the column (C) is fixed. This means if C4 is the minimum, a  value of 1 (TRUE) is delivered by the formula and the chosen format is set. I then set the format to a Fill of orange(y) and pressed okay.

If C4 happens to be the minimum, then B4 is orange. 

To extend that (and show which machine constrains), select B4,  go ribbon Home/ Styles/ Conditional Formatting  (down) Manage Rules... then carefully change the part  Applies to  from:

=$B$4

(i.e. just B4) to:

$B$4:$B$6

then press Ok.

Now the rule means if C5 is the minimum say, B5 will be orange. Note that you might have more than one orange cell if the capacities are equal lowest.

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.