I am trying to get a cell to highlight, given 2 criteria. The cell must be blank, and today's date must be after a predetermined date that is listed in another cell(R8C3). I'm also using R1C1 notation, but understand A1 notation as well. The part I don't understand is that each criteria works independently, but when I use the AND function it no longer works. The relative references are used since this formatting will span multiple columns.
考虑到2个标准,我试图让细胞突出显示。单元格必须为空白,并且今天的日期必须在另一个单元格(R8C3)中列出的预定日期之后。我也使用R1C1表示法,但也理解A1表示法。我不明白的部分是每个标准独立工作,但是当我使用AND功能时,它不再起作用。使用相对引用,因为此格式将跨越多个列。
=ISBLANK(RC) evaluates to true and highlights correctly.
= ISBLANK(RC)计算结果为true并正确突出显示。
=TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC))) evaluates to true and highlights appropriately.
= TODAY()> = INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC)))计算结果为true并适当地突出显示。
=AND(ISBLANK(RC),TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC)))) Does not highlight.
= AND(ISBLANK(RC),TODAY()> =间接(地址(行(R8C3),列(RC))))不突出显示。
A slightly modified version pasted in a cell ( to check that the formula works) evaluates appropriately to TRUE or FALSE. =AND(ISBLANK(RC3),TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC3))))
粘贴在单元格中的稍微修改后的版本(检查公式是否有效)会适当地评估为TRUE或FALSE。 = AND(ISBLANK(RC3),TODAY()> = INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC3))))
I'm using Excel 2010 (version 14) on a Windows 7 device.
我在Windows 7设备上使用Excel 2010(版本14)。
2 个解决方案
#1
1
Try this:
=(J$1>K$1)*(ISBLANK(A1))
where J1 has the current date and K1 the threshold date.
其中J1具有当前日期,K1具有阈值日期。
#2
0
The indirect statement seems unnecessarily convoluted... why not just use a mixed reference?
间接陈述似乎不必要地错综复杂......为什么不使用混合参考?
=AND(ISBLANK(RC), TODAY()>=R8C)
#1
1
Try this:
=(J$1>K$1)*(ISBLANK(A1))
where J1 has the current date and K1 the threshold date.
其中J1具有当前日期,K1具有阈值日期。
#2
0
The indirect statement seems unnecessarily convoluted... why not just use a mixed reference?
间接陈述似乎不必要地错综复杂......为什么不使用混合参考?
=AND(ISBLANK(RC), TODAY()>=R8C)