Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Color Conditional Formating
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 Last edited by Mathew Wiltzius : January 23rd 12 at 06:43 PM Reason: New information |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Conditional Formating
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 C1:D1; 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Conditional Formating
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; 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS | Excel Discussion (Misc queries) | |||
autoshape color conditional formating | Excel Discussion (Misc queries) | |||
Change color without conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating Background Color | Excel Discussion (Misc queries) | |||
Conditional Formating - Different row color also when using filter | Excel Discussion (Misc queries) |