conditional format

[smokenack]

I would like to conditionally format a range of cells so that if the value contained in a cell within the range appears in a cell in another defined range prefixed by the letter E the cell containing the value will be conditional formatted.

eg cell A1 = 4
cell B1 = E4
then cell A1 conditionally formatted.

thanks for looking

brain dead today

Nick

 

[RobShaw]

I'm not sure if i've unerstood you correctly but could you have something like the value in A being say 65 and when matched to a value in column B being say E65 you would want a positive match?

if you do, then you can do the left command coupled with an if to check as follows

Lookup_functions( IF(LEFT(A1)="E", RIGHT(A1, LEN(A1)-1), A1) )

I breifly tested it on a blank sheet here, and if you replace the reference to the cell with the line above, it will intrisically strip the letter off of the front.

Hope that makes sense

 

[Dutchy]

Doh!

Sorry Nick just realised what I'd done, put the "e" after the number rather than before!

Try this, should work now

=COUNTIF(B$1:B$100,"E"&A1)>0

 

[smokenack]

thanks Dutchy your solution was just what I needed and thanks to everyone else for your help.

Nick