I have two go-downs (lets say G-1 & G-2) and Trucks will be loading from the go-downs. This G-1 and G-2 will be in a drop down list in cell D1. If i am selecting G-1 from the drop down list I have to validate using conditional formatting: stock>=Nominated quantity & truck capacity <= 30 mt & >=10 mt. If the condition satisfies D1 should be highlighted with GREEN and if not it should be be highlighted with red. In the Same cell if I am selecting G-2, I have to validate same condition based on different values.
我有两个下降(比方说G-1和G-2),卡车将从下降中加载。该G-1和G-2将位于单元格D1的下拉列表中。如果我从下拉列表中选择G-1,我必须使用条件格式验证:库存> =指定数量和卡车容量<= 30 mt&> = 10 mt。如果满足D1的条件应以绿色突出显示,否则应以红色突出显示。在同一个单元格中如果我选择G-2,我必须根据不同的值验证相同的条件。
Cell A1 = Stock
单元格A1 =股票
Cell B1 = Nominated quantity
单元格B1 =指定数量
Cell C1 = Truck Capacity
单元C1 =卡车容量
Cell D1 = Drop down list with options "G-1", "G-2".
单元格D1 =下拉列表,选项为“G-1”,“G-2”。
1 个解决方案
#1
First remove any previous conditional formatting rules for D1 that have not worked out. This is not a time to keep previous bad efforts in the hope they can be fixed later.
首先删除尚未解决的任何先前的D1条件格式规则。现在不是保留以前不良努力的时候,希望以后可以修复。
Select D1 and choose Home ► Conditional Formatting >New Rule. Use the Use a formula to determine which cells to format option and supply the following for the Format values where this formula is true: text box.
选择D1,然后选择主页►条件格式>新规则。使用“使用公式”确定要格式化的单元格选项,并为此公式为真的格式值提供以下内容:文本框。
=and($A1>=$B1, $C1>=if($D1="G-1", 10, 20), $C1<=if($D1="G-1", 30, 40))
Click Format and select a green Fill. Click OK to accept the formatting option(s) and then OK again to create the new rule.
单击“格式”并选择绿色“填充”。单击“确定”接受格式化选项,然后再次单击“确定”以创建新规则。
Repeat for red using,
重复红色使用,
=or($A1<$B1, $C1<if($D1="G-1", 10, 20), $C1>if($D1="G-1", 30, 40))
It may be that your Truck Capacity in column C is actually written as text like 25 mt. This is wrong. Use 25 and give it a custom number format of 0\ \m\t so that the value in the cell can be used in mathematical operations.
可能是C列中的卡车容量实际上写为25 mt的文本。这是错的。使用25并为其提供0 \ \ m \ t的自定义数字格式,以便单元格中的值可用于数学运算。
#1
First remove any previous conditional formatting rules for D1 that have not worked out. This is not a time to keep previous bad efforts in the hope they can be fixed later.
首先删除尚未解决的任何先前的D1条件格式规则。现在不是保留以前不良努力的时候,希望以后可以修复。
Select D1 and choose Home ► Conditional Formatting >New Rule. Use the Use a formula to determine which cells to format option and supply the following for the Format values where this formula is true: text box.
选择D1,然后选择主页►条件格式>新规则。使用“使用公式”确定要格式化的单元格选项,并为此公式为真的格式值提供以下内容:文本框。
=and($A1>=$B1, $C1>=if($D1="G-1", 10, 20), $C1<=if($D1="G-1", 30, 40))
Click Format and select a green Fill. Click OK to accept the formatting option(s) and then OK again to create the new rule.
单击“格式”并选择绿色“填充”。单击“确定”接受格式化选项,然后再次单击“确定”以创建新规则。
Repeat for red using,
重复红色使用,
=or($A1<$B1, $C1<if($D1="G-1", 10, 20), $C1>if($D1="G-1", 30, 40))
It may be that your Truck Capacity in column C is actually written as text like 25 mt. This is wrong. Use 25 and give it a custom number format of 0\ \m\t so that the value in the cell can be used in mathematical operations.
可能是C列中的卡车容量实际上写为25 mt的文本。这是错的。使用25并为其提供0 \ \ m \ t的自定义数字格式,以便单元格中的值可用于数学运算。