条件格式化单元格，如果它的值在表的列中找到 [英] Conditional Format cell if it's value is found in a Column of a Table

问题描述

我以为这很简单：

=COUNTIF(Foo[Bar],$A1)>0

将其应用于整个列，我认为会突出显示列栏中找到的任何单元格表Foo。但是，excel告诉我我的公式包含一个错误，即使我把它粘贴到一个单元格，它给我一个正确的值True / False。

applying this to the entire column I thought would highlight any cell that is found in the Column Bar of table Foo. But excel is telling me my formula contains an error, even though when I paste it into a cell it does give me a correct value of True/False.

我以为也许由于某种原因，我需要将其复杂化为excel，所以我试过：

I thought maybe for some reason I needed to complicate it for excel so i tried:

    =COUNTIF(Foo[Bar],$A1) + CountIf(A1:A10000, $A1>1

不知道为什么我试了，无论如何，没有办法。

Not sure why i tried it but I figured why not. Regardless is did not work.

然后我继续责怪范围（Foo [Bar]），尝试：

I then went on to blame the Range (Foo[Bar]) and tried:

=COUNTIF($T$2:$T$1048576,$A1)>0

它的工作，这里的问题是，该表的行数可以从1变为任何，取决于天数进度。我宁可不要使用50000作为一个数字，因为它可能不总是足够好，绝大多数时间都会被杀死，并且当A列在某些日子关闭了100万条记录，表格为100,000时，会导致刷新。

It Worked, the issue here is that that table's row count can change from 1 to anything depending on out days progress. I'd prefer not to just use 50000 as a number because it might not always be good enough and most of the time over kill. and causes refreshed to take for ever when Column A is closing in on 1 million records some days and the table is at 100,000.

所以，怎么c我动态地有条件地格式化我的列以反映表？

So, how can I dynamically conditionally format my column to reflect table?

推荐答案

尝试

=COUNTIF(INDIRECT("Foo[Bar]"),$A1)>0

这篇关于条件格式化单元格，如果它的值在表的列中找到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！