Hi,
I am trying to build a set of tables that does the following:
Table 1 is a list of names.
Table 2 is another list. If a cell of table 2 contains a name that already is in table 1, I want this cell to be colored red. Anyone knows how I could do this?
Numbers 09-OTHER, Mac OS X (10.7.5)
BTF,
Here's a way to do that...
The expression in the Hidden column (Column B) is:
=IF(COUNTIF(Second Table::A, A)>0, A, ".")
The Conditional Format for the Name column is:
Regards,
Jerry
BTF,
Here's a way to do that...
The expression in the Hidden column (Column B) is:
=IF(COUNTIF(Second Table::A, A)>0, A, ".")
The Conditional Format for the Name column is:
Regards,
Jerry
Thank you for your post. I think I got the idea, but still have a problem:
I can enter "B2" as condition for cell A2, but when I copy the format to the other cells in column A, they all refer to "B2". How can I manage that A3 refers to B3, A4 to B4, ...?
I cannot click the option "Preserve Column" as I cannot find the little triangle you have in your second screen shot. As soon as I know to post pictures, I will add a screenshot.
Here it is: <img src="http://bringsthefire.bplaced.net/ohne.tiff">
Jerry is working with Numbers 3 and the cell reference will change as you copy. In Numbers '09, you have to edit the condition in each cell. It is impractical to do this except for very short lists.
If you are not dead set on using color to flag duplicates, forget about the conditional formatting and just look at the formula results in column B (which you can change to say "DUPLICATE" instead of repeating the name).
BTF,
I'm sorry, I didn't notice that you are using Numbers 2. You don't have that option in Numbers 2. This is one of the significant improvements in Nujmbers 3, relative addressing in conditional format comparison cells.
Your best alternative is to conditionally format the auxiliary column to raise the red background when the value in the aux column cell counts one or more duplicates. You could achieve the same effect as in my screen shot if you formatted one cell at a time, but that would be more tedious than most morals can endure. The expression in column B would be:
=COUNTIF(Second Table::A, A)+B2
Conditionally format for Greater than 0.
Jerry
Ok,
thanks for your help!
Conditional Formatting based on content of other table
