Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format if not on list
I would like to apply a conditional format into a cell, so that when I type a
name into that cell, if it doesn't exist on a separate list of names, the condition will apply. Is it possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format if not on list
Yes, possible.
As condition use a formula: =ISERROR(MATCH(A1,listrange,0)) Above example assumes that you have a named range 'listrange' that holds your separate list of names (of course you could also use something like $B1$:$B$10) and that your cell containing the conditional format is A1. This will format all cell entries in A1 that are NOT in the specified list. Cheers, Joerg Mochikun "pkeegs" wrote in message ... I would like to apply a conditional format into a cell, so that when I type a name into that cell, if it doesn't exist on a separate list of names, the condition will apply. Is it possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format if not on list
Yes.
Select cell where u want apply conditional format and select Formula from conditional format box and type below mentioned formula =ISERROR(VLOOKUP(B1,$A$1:$A$100,1,0)) and set the format which format u want. in this formula u need to change cell range in that formula, Cell B1 is which cell u selected, and $A$1:$A$100 range is list of names ( You can use Name list also). Please do rate. Regards "pkeegs" wrote: I would like to apply a conditional format into a cell, so that when I type a name into that cell, if it doesn't exist on a separate list of names, the condition will apply. Is it possible? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format if not on list
Thanks guys (Joerg & Muhammed) both conditional formulas work well
Regards - pkeegs "Joerg Mochikun" wrote: Yes, possible. As condition use a formula: =ISERROR(MATCH(A1,listrange,0)) Above example assumes that you have a named range 'listrange' that holds your separate list of names (of course you could also use something like $B1$:$B$10) and that your cell containing the conditional format is A1. This will format all cell entries in A1 that are NOT in the specified list. Cheers, Joerg Mochikun "pkeegs" wrote in message ... I would like to apply a conditional format into a cell, so that when I type a name into that cell, if it doesn't exist on a separate list of names, the condition will apply. Is it possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format in a List | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Data Validation - List - keeping the format of the list - shading | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
Conditional format from a list of numbers | Excel Discussion (Misc queries) |