具有自定义公式的嵌套引用，用于条件格式 [英] Nested reference with custom formula for conditional formatting

问题描述

我正在尝试根据日期设置条件格式设置规则，以便将在Google表格中一个标签中输入的日期与另一个标签中的日期进行比较，然后再根据另一个标签中的值进行选择.此处的应用是使日期落在根据循环号选择的日期范围之外时变成红色.例如，如果周期005从05/08/17到05/21/17且在单元格区域(有从"和到"列)中输入的日期是05/07/17(在周期中最早的日期)或05/20/18(周期中的最晚日期之后)，该单元格将变为红色.

I am trying to set conditional formatting rules based on dates such that a date entered into one tab in the Google Sheets is compared with a date in another tab, which is in turn selected based on a value in another tab. The application here is to have dates turn red when they fall outside a date range that is chosen based on a cycle number. For example, if cycle 005 runs from 05/08/17 to 05/21/17 and the date entered in a range of cells (there is a "from" and "to" column) is 05/07/17 (before the earliest date in the cycle) or 05/20/18 (after the latest date in the cycle) then the cell will turn red.

我有一个选项卡(称为 lookup_table )，该选项卡具有周期编号及其对应的开始"和至"日期，另一选项卡(称为 review )位于周期输入给定数据集的编号(进入单元格B2)，并在第三个 data 标签中输入开始"和结束"日期.该公式需要根据 review 选项卡中的循环号在 lookup_table 选项卡中引用开始"和至"日期，以有条件地格式化在中输入的日期>数据标签.

I have one tab (called lookup_table) that has cycle numbers and their corresponding "from" and "to" dates, another tab (called review) where the cycle number for a given data set is entered (into cell B2), and a third data tab where "from" and "to" dates are entered. The formula needs to reference "from" and "to" dates in the lookup_table tab based on the cycle number in the review tab to conditionally format the dates entered into the data tab.

我发现了一些与条件格式类似的问题，但没有涉及在基于另一个值引用日期之后将嵌套"函数进行比较以比较日期的问题.

I have found some similar questions about conditional formatting but nothing involving putting together a "nested" function comparing dates after referencing them based on another value.

推荐答案

使用公式的条件格式可以使用嵌套逻辑，因为公式可以嵌套， = if(... if(... vlookup(...)))等.当使用条件格式公式对另一张图纸进行引用时，应将其包装为 indirect ，即， review！B2 应替换为 indirect(评论！B2").

Conditional formatting with a formula can use nested logic, because the formula can be nested, =if(...if(...vlookup(...))) and so on. When a reference to another sheet is made in a conditional formatting formula, it should be wrapped as indirect, i.e., review!B2 should be replaced by indirect("review!B2").

第1步:获取循环号， review！B2

Step 1: get cycle number, review!B2

第2步:从日期"开始查找， = vlookup(评论！B2，lookup_table！A:C，2，否).这里的假设是，在查询表中，循环号在A列中，而起始日期"在B列中.(范围的第二列)

Step 2: Look up "from" date, =vlookup(review!B2, lookup_table!A:C, 2, False). The assumption here is that in the lookup table, cycle numbers are in column A and From dates are in column B. (2nd column of the range)

第3步:添加条件格式设置规则，日期在...之前" ...确切日期"，然后在确切日期"中输入第2步中的公式，将跨工作表引用包裹在如前所述的间接"中.

Step 3: Add conditional formatting rule, "Date is before"... "exact date" and enter the formula from step 2 in "exact date", wrapping cross-sheet references in `indirect as mentioned earlier.

=vlookup(indirect("review!B2"), indirect("lookup_table!A:C"), 2, False)`

第4步:查找至"数据， = vlookup(评论！B2，lookup_table！A:C，3，否).此处的假设是，在查找表中，循环号位于A列中，而起始日期"位于C列中(范围的第三列)

Step 4: Look up "to" data, =vlookup(review!B2, lookup_table!A:C, 3, False). The assumption here is that in the lookup table, cycle numbers are in column A and From dates are in column C. (3rd column of the range)

第5步:添加条件格式设置规则，"Date is after" ..."exact date"，并在"exact date"中输入第4步中的公式，如前所述，将跨工作表引用包裹在间接"中.

Step 5: Add conditional formatting rule, "Date is after"... "exact date" and enter the formula from step 4 in "exact date", wrapping cross-sheet references in `indirect as mentioned earlier.

=vlookup(indirect("review!B2"), indirect("lookup_table!A:C"), 3, False)`

这篇关于具有自定义公式的嵌套引用，用于条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！