基于标题值的列的条件格式 [英] Conditional formatting for a column based on the value of header

问题描述

我有几列，其中来自第7行的数据根据​​从第6行中的下拉菜单进行的选择而变化。这些选项中的大多数将导致出现基于文本或数字的值，但是其中两个将需要为格式化为货币。

基本上我想要的是一个条件格式化公式，说：如果任何列中的第六行具有隐含单位费率或年度预算的值，则将该列中的第7行及以后的格式作为货币格式。

我可以获得这通过创建两个单独的条件格式化公式并将其应用于整个区域来工作，但似乎应该有一种方法可以在单个公式中进行。

我当前的公式，应用为两个独立的规则：

= INDIRECT（ADDRESS（6，COLUMN（），1））=年度预算

= INDIRECT（ADDRESS（6，COLUMN（），1））=Implied Unit Rate / code>

如果将其应用为一个规则，我认为它应该工作：

= OR（INDIRECT（ADDRESS（6，COLUMN（），1））=年度预算，
INDIRECT（ADDRESS（6，COLUMN（），1））=Implied Unit Rate

当我在工作表中的常规单元格中输入组合公式时，它会按预期返回true，但是当我应用它时，什么也不做作为条件格式规则。有任何想法吗？谢谢提前！

解决方案

现在我可能会错误地解释Scott的意思，所以我要用自己的话说单词

您对有问题的单元格地址的条件检查有点详细。要应用条件格式，请首先选择要应用条件格式的范围。让任意选择范围A7到D42。选择适当的范围后，确保单元格A7是所选范围的活动单元格。这通常意味着A7到D42周围有一个边框，所有的单元格，但是A7都有一些灰色，表示它们被选中，而A7将会有一个更明亮的背景来表明它是活动单元格。所有这些仍然有效，请选择您的条件格式。

在条件格式中，选择公式作为选项（最后一个），以控制如何应用格式设置。在您可以输入公式的地区，输入以下内容：

  = OR（A $ 6 =年度预算，A $ 6 =隐含单位利率）
  

$将保持行和列引用不变，应用于该范围内的每个单元格。它将始终检查第6列中单元格的列。列A引用是相对于活动单元格。

I have several columns where the data from rows 7 onwards changes depending on the selection made from a dropdown menu in row 6. Most of these options will result in textual or number based values appearing, but two of them would need to be formatted as currency.

Essentially what I want is a conditional formatting formula that says; if row six in any column has a value of "Implied Unit Rate" or "Annual budget", format rows 7 and onwards in that column as currency.

I can get this to work by creating two separate conditional formatting formulas and applying them both to the whole area, but it seems like there should be a way to do it in a single formula.

My current formulas, applied as two separate rules:

=INDIRECT(ADDRESS(6,COLUMN(),1))="Annual Budget"

=INDIRECT(ADDRESS(6,COLUMN(),1))="Implied Unit Rate"

How I think it should work when applied as one rule:

=OR(INDIRECT(ADDRESS(6,COLUMN(),1))="Annual Budget", INDIRECT(ADDRESS(6,COLUMN(),1))="Implied Unit Rate")

When I enter the combined formula in a regular cell within the worksheet, it does return true as expected, but does nothing when I apply it as a conditional formatting rule. Any ideas? Thanks in advance!

解决方案

Now I may be mis interpreting what Scott is saying, so I am going to say this in my own words.

Your condition check on the cell address in question is a little "verbose". In order to apply your conditional formatting, select the range you want to apply your condition formatting to first. Lets arbitrarily say select range A7 to D42. After selecting the appropriate range, ensure cell A7 is the active cell of the selected range. This usually means there is a border around A7 to D42 and all cells but A7 have been slightly greyed to indicate that they have been selected, and A7 will have a brighter background to indicate that it is the active cell. With all that still in effect, select your conditional formatting.

In conditional formatting choose formula as the option (last one at the bottom) to control how your formatting will be applied. In the region where you can enter your formula, enter the following:

=OR(A$6="Annual Budget", A$6="Implied Unit Rate")

The $ will keep the row and column reference from changing as it is applied to each cell in the range. It will always be checking row 6 of what ever column the cell is in. The column A reference is relative to the active cell.

这篇关于基于标题值的列的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！