使用自定义公式引用单元格本身的条件格式 [英] Conditional Formatting with Custom Formula referencing the cell itself

问题描述

我有一个Google电子表格，每天都有列（顶部单元格中的日期），每个单元格中还有一个字符串表示状态。

我需要

看起来像是在过去的列的日期标准和细胞内的文本内容的单元格应用条件格式我必须使用自定义公式来处理日期部分，但是我将如何引用单元格本身的内容？我尝试过使用 CELL（contents，ADDRESS（ROW（），COLUMN（））引用单元格，但是这会返回一个 Error：Argument必须是范围。

解决方案

使用条件格式（在Excel和Google文档中） 使用适用于范围左上方单元格的公式 ....并且您可以通过它的单元格引用简单地引用单元格，因此假定日期位于 B1中： J1 并且您的状态数据位于 B2：J100 中，然后选择后一个范围，并且（在条件格式中）应用适用于<$ c

$ p $ AND $（B $ 1

这将适用于整个范围

您必须使用绝对/相对引用，就像您在公式中向下/跨范围复制公式一样，所以您需要在 B $ 1 $ c $ c>因为你想让每一行都引用第1行......虽然穿过你希望它变成 C $ 1 ， D $ 1 等等，因此没有 $ 在列字母之前

I have a google spreadsheet which has columns for each day (with the date in the top cell) and in each cell a string indicating status.

I need to apply conditional formatting to cells based on the criteria of the date of the column being in the past AND the text inside the cell starting with the substring "OK".

It seems that I must use a Custom Formula in order to deal with the date part, but then how would I reference the content of the cell itself? I've tried referencing the cell using CELL("contents",ADDRESS(ROW(),COLUMN()) but this returns an Error: Argument must be a range.

解决方案

With conditional formatting (both in Excel and google docs) you just use the formula that applies to the top left cell of the range....and you can simply refer to the cell by it's cell reference, so assuming dates are in B1:J1 and your status data is in B2:J100 then select that latter range and (in conditional formatting) apply the formula that applies to B2, i.e.

=AND(B$1<TODAY(),LEFT(B2,2)="OK")

This will work for the whole range

You must use absolute/relative references as if you were copying the formula down/across the range, so you need the $ in B$1 because you want every row to refer to row 1....although going across you want it to change to C$1, D$1 etc. hence no $ before the column letter

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