Conditional Format cell if it's value is found

问题:

I thought this was going to be Very simple:

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

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.

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.

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

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

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.

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

回答1:

Try

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

回答2:

Maybe (I've not tested this) it would work with a named range. Say name Foo[Bar] as CheckList and use something like =MATCH($A1,CheckList,0)>0 in CF. This works for an existing table that is extended with entries under [Bar] but I am not sure what would happen for you on table update, though pasting other data over the top of my table does work.

回答3:

Try:

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

or if your in this table,

=COUNTIF(Foo[[#Data],[Bar]],Foo[[#This Row],[A Header]])>0

Also a good resource is to use the auto complete feature Excel give you. This will ensure your references are accurate.