help with conditional formatting

Z

zero

I am trying to set up my excel database so that when a name is entered
into a cell it automatically checks it against a list in another
worksheet/workbook and if it is there it will notify the user in some
way such as a color change in the background. I tried using
conditional formatting but it doesn't allow me to reference another
sheet or workbook. Any ideas?
 
B

Biff

Hi zero,

I don't know for certain that there is not a workaround
for this but I haven't been able to come up with one and
I've tried many,many times. As an alternative you could
use a helper column with a formula to test for the
condition and then base your conditional format on the
helper cell.

Biff
 
D

Debra Dalgleish

You can refer to the names on another worksheet if that list is in a
named range, e.g. NameList. There are instructions here for naming a range:
http://www.contextures.com/xlNames01.html

To apply conditional formatting:
1. Select the cells in the sheet where you want the colour change,
e.g. A2:A10
2. Choose Format>Conditional Formatting
3. From the first dropdown, choose Formula Is
4. In the formula box, type a formula that refers to the active cell
in the selection: =COUNTIF(NameList,A2)
5. Click the Format button, and select a Pattern colour to highlight
the cells
6. Click OK, click OK
 
B

Bill in Verona, WI

Does the other worksheet have to be in the same workbook for this to work?

Bill
 
Top