Color Conditional Formating

[Mathew Wiltzius]

Question on conditional formatting, it was sort of resolved in a fe
other posts but not fully to what I.

I have a restaurant workbook, weekly I input prices between 20 or s
vendors.

I would like to be able to put the prices in and have what ever cell i
the lesser value be highlighted yellow, so it would be the ideal vendo
/ product to purchase from that week as its in yellow.

Example:

vender A, Vender B, Vender C all sell carrots. Vender A is at $00.94 pe
pound, Vender B is $00.88 and C is $00.64. After imputing these price
into the charts vender C would automatically be highlighted yellow a
its the cheapest.

any thoughts?

I attempted to do =min between the cells in the conditional formatio
but no luck, but maybe i'm doing it wrong as well.

*update 1.23.2012 currently I am doing conditions for greater the
"x,Y,Z" leaves cell blank, Less then "x,y,Z" leaves cell yellow. Bu
this takes forever, and I have some 600 items. Also when I add items t
this list it will moves cells around to keep things in "ABC" order, i
there a way to lock conditions to cells even if they move?

thank

 

[GS]

Mathew Wiltzius pretended :

Question on conditional formatting, it was sort of resolved in a few
other posts but not fully to what I.

I have a restaurant workbook, weekly I input prices between 20 or so
vendors.

I would like to be able to put the prices in and have what ever cell is
the lesser value be highlighted yellow, so it would be the ideal vendor
/ product to purchase from that week as its in yellow.

Example:

vender A, Vender B, Vender C all sell carrots. Vender A is at $00.94 per
pound, Vender B is $00.88 and C is $00.64. After imputing these prices
into the charts vender C would automatically be highlighted yellow as
its the cheapest.

any thoughts?

I attempted to do =min between the cells in the conditional formation
but no luck, but maybe i'm doing it wrong as well.

*update 1.23.2012 currently I am doing conditions for greater then
"x,Y,Z" leaves cell blank, Less then "x,y,Z" leaves cell yellow. But
this takes forever, and I have some 600 items. Also when I add items to
this list it will moves cells around to keep things in "ABC" order, is
there a way to lock conditions to cells even if they move?

thanks

Take a look at the MIN() function. Name the range where vendor prices
go as 'relative' to the product row. For example, if your products are
listed in ColA and you use ColC to ColE for vendors, then...

A1 = "Product"
C1 = "VendorA"
D1 = "VendorB"
E1 = "VendorC"

Select C11;
Open the Define Name dialog:

In the Name box type 'Sheet1'!VendorPrice
..where the sheetname is wrapped in single quotes followed by the
Exclamation character.
(Use your actual sheetname if it's not "Sheet1")

In the RefersTo box type =$C1:$E1

Click the 'Add' button and close the dialog.

Select the cells in cols C:E where you want the highlighting.
Open the ConditionalFormatting dialog;

Choose 'Cell value is' plus 'equal to' and type =min(vendorprice) in
the value box;

Click the 'Format...' button and choose a fill pattern.

Adjust or edit the range refs to match your actuals.

 

[GS]

Correction:

Take a look at the MIN() function. Name the range where vendor prices go as
'relative' to the product row. For example, if your products are listed in
ColA and you use ColC to ColE for vendors, then...

A1 = "Product"
C1 = "VendorA"
D1 = "VendorB"
E1 = "VendorC"

Select C1:E1;