条件格式设置公式以突出显示范围内的适当日期

I need a formula for conditional formatting that will highlight a date between A2:Z2 which matches a number that I enter into a “Committed Sessions Cell” (A1). In row 2 there are a series of numbers that appear above each date column (1,2.3, etc). For example, if I enter a “3” in cell A1, the date in J3 should match the number 3 above it and be highlighted. The idea here is to provide a quick visual prompt for how many sessions are in a client’s contract.

我需要一个条件格式的公式,它会突出显示A2:Z2之间的日期,该日期与我输入“提交的会话单元格”(A1)中的数字相匹配。在第2行中,每个日期列(1,2.3等)上方都有一系列数字。例如,如果我在单元格A1中输入“3”,则J3中的日期应与其上方的数字3匹配并突出显示。这里的想法是提供客户合同中会话数量的快速视觉提示。

Note: the sequenced numbers 1,2,3 etc in row 2 appear every 5th column (with nothing in between) but there IS other data in between the dates in row 3. Only the appropriate date should be highlighted.

注意:第2行中的顺序数字1,2,3等出现在每第5列(中间没有任何内容)但在第3行的日期之间还有其他数据。只应突出显示相应的日期。

       A  B  C  D   E  F  G   H  I  J  K  L   M     

1      3
2      1            2               3                                           
3.     1/2/14       2/3/14          2/15/14

With grateful thanks,

感激不尽,

~ Jay

1 个解决方案

#1

Your example is not consistent. If you have the date every fifth column, the dates should be in columns A,F,K,P, etc. with 4 columns between 2 points.

你的例子不一致。如果每隔五列都有一个日期,则日期应该在A,F,K,P等列中,两列之间有4列。

I came up with the following formula: =AND(A2=$A$1,MOD(COLUMN(A3)-1,5)=0) which is applicable to the entire 3rd row. Create it as follow:

我想出了以下公式:= AND(A2 = $ A $ 1,MOD(COLUMN(A3)-1,5)= 0),它适用于整个第3行。创建如下:

IMPORTANT: Select cell A3 (the reference point for the formula)

重要信息:选择单元格A3(公式的参考点)

Without selecting another cell, highlight the entire row 3

如果不选择其他单元格,请突出显示整行3

Go to Conditional Formatting -> New rule

转到条件格式 - >新规则

Choose "Use a formula to determine which cells to format"

选择“使用公式确定要格式化的单元格”

In "Format values where this formula is true", put =AND(A2=$A$1,MOD(COLUMN(A3)-1,5)=0)

在“此公式为真的格式值”中,put = AND(A2 = $ A $ 1,MOD(COLUMN(A3)-1,5)= 0)

Choose the formatting that you want (example: Fill with yellow)

选择所需的格式(例如:填充黄色)

Click OK all the way

一直单击“确定”

NOTES:

To change the location of your Committed Sessions, change $A$1 to another cell. Important to keep the dollar signs

要更改“已提交的会话”的位置,请将$ A $ 1更改为另一个单元格。保持美元符号很重要

The MOD function is the one that controls every fifth column. If you want the highlight every 4th column (i.e. 3 cells between each point such as A, E, I, etc.), replace the number 5 with the number 4

MOD功能是控制每第五列的功能。如果您希望每隔4列突出显示一次(即每个点之间的3个单元格,如A,E,I等),请将数字5替换为数字4

#1