help with conditional formatting

  • Thread starter Thread starter zero
  • Start date Start date
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?
 
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
 
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
 
Does the other worksheet have to be in the same workbook for this to work?

Bill
 
Back
Top