Lookup criteria....

G

Gunjani

I want to create 2 criterias

1. Locate cells containing the letter 'E' is not followed by a cell with
the Letter 'L'.

2. Same as above but this time colour these cells in red.
--
Many Thanks

Gunjani
"I'm going to Latin-America, and I'll need to use a
translator, because I don't speak Latin"
---Dan Quayle
 
F

Frank Kabel

Hi
to count these cells try the following (if they're located in A1:A100)
=SUMPRODUCT(--(A1:A100="E"),--(A2:A101<>"L"))

to color them:
- highlight cells a1:A100
- goto 'Format - Conditional Format'
- enter the formula
=(A1="E")*(A2<>"L")
- choose your color
 
D

Dave Peterson

Can you use conditional formatting?

If yes and follows means the cell to the right,
then select your range (I used A1:z99)
format|conditional formatting
and with A1 the active cell, use this formula
formula is: =AND(COUNTIF(A1,"*E*")>0,COUNTIF(B1,"*L*")>0)
give it a nice format.

the *E* means that E/e can be anywhere in the cell. Same thing with the *L*.

(Change B1 to A2 if followed means under.)
 
G

Gunjani

Hi
to count these cells try the following (if they're located in A1:A100)
=SUMPRODUCT(--(A1:A100="E"),--(A2:A101<>"L"))

to color them:
- highlight cells a1:A100
- goto 'Format - Conditional Format'
- enter the formula
=(A1="E")*(A2<>"L")
- choose your color
Click to expand...
Thanks but
1. My table range is from D20:AH74 displaying randomly a letter of the
alphabet in each cell and I want to view any cells with Letter L next
to Letter E

2. In the suggested formula above what is the difference between = and
<> and why the double --
 
F

Frank Kabel

Hi
so you want to ighlight cells which containg 'EL' within ONE cell. If
yes select all your cells (D20:AH74) and enter the following formula
within the conditional format dialog
=ISNUMBER(FIND("EL",D20))
 
G

Gunjani

Hi
so you want to ighlight cells which containg 'EL' within ONE cell. If
yes select all your cells (D20:AH74) and enter the following formula
within the conditional format dialog
=ISNUMBER(FIND("EL",D20))
Click to expand...
Sorry, I meant E in one cell and L in the cell next to it and view how
many times it happens.
 
F

Frank Kabel

Hi
what do you mean with 'next cell'
- cell to the left
- cell below the 'E' cell
 
G

Gunjani

Hi
what do you mean with 'next cell'
- cell to the left
- cell below the 'E' cell
Click to expand...
Cell to the left of the Cell containing E i.e the letter E should not
follow L (There is only one letter in each cell)

--
Many Thanks

Gunjani
"A truck backed through my windshield into my
wife's face."
-- An Actual quotes taken from insurance and
accident forms
 
Top