conditional format formula modification

**johncena** · 03-20-2010, 01:44 AM

Code:

=AND(L1="NOT DYED", COUNTIF(DyeQueue,K1&"*")=0)

dear friends above formula I'm using with conditional format & it's work fine..i need little modification in that formula.i need to check K1 cell first 6 characters with "DyeQueue" range,pls help me to do this.

**Marcol** · 03-20-2010, 05:01 AM

Maybe

=LEFT("DyeQueue",6)
or
=LEFT(K1,6)

=AND(L1="NOT DYED", COUNTIF(DyeQueue,LEFT(K1,6))=0)

**johncena** · 03-20-2010, 05:34 AM

thanks for your reply marcol.here i have attached my document.pls check it.i need conditional format formula for first sheet column "L"

**Marcol** · 03-20-2010, 06:20 AM

I cannot verify the results are correct, only you can do this.

Code:

=AND(L1="NOT DYED", COUNTIF(DyeQueue,LEFT(K1,6)&"*")=0)

does what I think you are asking

I have amended a copy of your attachment

**johncena** · 03-20-2010, 07:40 AM

thanks marcol.my requirement is first sheet column "K" first 6 characters second sheet any columns not available & first sheet column "L" value " NOT DYED " then that cell should highlight.my formula doing this work very well,only the thing is my formula matching only cell values.not first 6 characters.

**Marcol** · 03-20-2010, 09:07 AM

Hi johncena

Code:

=AND(L1="NOT DYED", COUNTIF(DyeQueue,LEFT(K1,6)&"*")=0)

this means in words

If L1="NOT DYED" and order number LEFT(K1,6)&"*", (match the first 6 characters only) is not found in named range "DyeQueue"

then highlight L1 (yellow is your choice of colour)

This is what I have done for you in the copy worksheet I posted previously.(Post #4)

**johncena** · 03-20-2010, 09:47 AM

thanks a lot Marcol.it's work....

**Marcol** · 03-20-2010, 10:19 AM

Hi johncena

Glad to have been of some help.

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.