What Excel formula to achieve desired result?

April 21, 2009 5:39 AM

What formulae do I need to cause Excel to highlight a cell on one worksheet if the data is found in a different worksheet? Formulae I have attempted (using named ranges) under the cut.

have two lists on two separate worksheets in the same workbook in Excel 2003. I would like to apply conditional formatting so that if Sheet 1 contains a value in Sheet 2, the relevant cell on Sheet 1 will be highlighted.


I have figured out one way to do this, but it is arduous and requires conditionally formatting each line in Sheet 1. I have created a named range for the relevant cells in Sheet 2 (as a hypothetical example that might make common sense, “Poisons”). Then, for each row in Sheet 1, I apply the following conditional formula:


=COUNTIF(Poisons, A4)<>0


This does indeed apply the formatting if a name on Sheet 2 appears in Sheet 1. (So if any of my ‘foods’ listed on Sheet 1 contain a “poison” listed on Sheet 2, they’re highlighted bright red.) I can use the format painter to extend this format to additional cells, and it seems to work. But I would prefer to have a formula that does not go cell-by-cell. Especially because my data is in different columns and I add to it and change it daily, so I may miss something just because I didn't format-paint the whole dratted sheet. Isn’t there a formula that will search in Sheet 1 and highlight anything in the range on Sheet 2, as a whole?


I had also tried the following, which I thought should work on the sheet as a whole. I created a second named range for the relevant cells in Sheet 1 (“Foods”). Then I applied conditional formatting to the entire sheet as follows:

=COUNTIF(Poisons, Foods)<>0

I copied a few cells from the “Poisons” section into the “Foods” section to confirm the formula would work, and no change in formatting occurred. So this didn’t do it. Any suggestions?

posted by mccn to Computers & Internet (5 answers total)

The approach I would take is to modify the table on the second sheet slightly, where the first column is the alphabetical list of poisons, and the second column is some value that will indicate its a poison, like perhaps using the text "poison". On the first sheet you create a VLOOKUP formula that uses the food value in sheet 1 to look up whether an item is a poison or not. The Look-up formula would look like this:
VLOOKUP(_food item cell_, _2 column poison table_, 2)

The logical test formula would look like this
If(VLOOKUP(_food item cell_, _2 column poison table_, 2) = "Poison"), true, false)

_food item cell_ is the cell on sheet 1 with the food item you want to check
_2 column poison table_ is the alphabetized list of poisons in one column, and the text "poison" in the second column
2 is the second column of the poison table

What's happening is the VLOOKUP will take the first argument (_food item cell_) and look for it in the first column of the table specified in the second argument (_2 column poison table_), the third argument tells which column in the table to return as the result of the formula, in this case, column 2.
So now in the logical test formula, you can just see if the value returned was "Poison" or not (if the value doesn't exist in the table, it will return "N/A").

I don't have excel 2003 anymore, so I can't check and make sure it will work as a conditional formatting formula, but it should work.
posted by forforf at 6:11 AM on April 21, 2009

I would do something like this. We have our food column on sheet 1 and our poison column on sheet 2. On sheet 1 I would add an additional column of formulas: =IF(ISNA(MATCH(A1,poison,0)),0,1) this goes in the topmost row, obviously. This will be a 1 if the exact contents of A1 are in the named range POISON. Ie, if SLUGS is in the poison list it will not match SLUG.

You can then either format this new column to be your flags or set a conditional format in column A. If the new column is in B then your conditional formatting in A1 would look like: IF FORMULA IS =B1>0 but notice that by default the conditional format window will want to produce absolute references ($A$1) not the relative references you need to be able to copy the formatting down.
posted by shothotbot at 6:16 AM on April 21, 2009 [1 favorite]

Format - Conditional Formatting?
posted by JJ86 at 6:21 AM on April 21, 2009

Best answer: You don't need named ranges, you just need another column with a CountIf Formula.
It doesn't matter if the columns are all on different sheets or in different workbooks, this should work.

So foods in A and Poisons in G. Column B has the formula "=COUNTIF(G:G,A1)" in B1 and it's pasted down the entire column (blanks lines too). Now you use Conditional Formatting on cell A1 "Formula is" "=$B1>0". Now you use Format Painter, just once, to paste that format all the way down the column, blank cells and all. You can even hide column B if you want to.

But I would prefer to have a formula that does not go cell-by-cell. Especially because my data is in different columns and I add to it and change it daily

I don't quite understand this. Your formula will need to be cell by cell because each cell has to be evaluated on it's own. If you just use Format Painter once on the whole Foods column, you should not have to do it again. Is it the Foods or Poisons in different columns? If it's the Poisons, you can modify the CountIf to "=COUNTIF(G:G,A1)+COUNTIF(K:K,A1)". If it's the Foods, you'll need one Countif column for each Food column you have.
posted by soelo at 9:13 AM on April 21, 2009

Conditional Formatting can handle this. I could explain it OK, but best that you go to the Help Menu.
posted by rselover at 11:48 AM on April 21, 2009