Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Check Range for Minimum and Repopulate the Minimum

0

Hi all, help needed please.

I have a simple sheet which I need to highlight (automatically) what the minimum capcity (value) is between three pieces of equipment, then repopulate just the minimum equipment and its value in different cells. The example sheet I am attaching shows this, but I had to format it manually to show the GEN as the constraint between the three pieces of equipment.

Thank you.

Answer
Discuss

Answers

0
Selected Answer

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.

Discuss

Discussion

I guess that worked for you. Thanks for selecting my Answer, Woutie. 
John_Ru (rep: 6297) Nov 8, '23 at 6:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login