Prev: Reference to values disappear after closing file
Next: Showing the Differences
From: Pam on 25 Feb 2010 12:13
Russell,

Here's what I need to happen:
ColB ColC
Dept Class
5 GRR Highlight
5 GRP
3 GRR
5 GRU Highlight
1 GRU

If Dept 5 has code ending in anything other than "P" - highlight it.

Thanks again for your help.
Pam

"Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in message
news:8F42C7C8-42E9-46DC-8420-FC3CAD9E9752(a)microsoft.com...
> Hi Pam
>
> If I understand correctly then, if the cells contain 5 & 22a then it is
> highlighted - if 5 & 22p no highlight
> - if 6 & 22a - no highlight.
>
> You only described your "3 digit class code". What does it look like
> exactly.
>
> My example works fine but we are obviously missing something. It will
> work,
> just a bit of fine tuning I'm sure.
>
> Regards
> --
> Russell Dawson
> Excel Student
>
> Please hit "Yes" if this post was helpful.
>
>
> "Pam" wrote:
>
>> Russell,
>>
>> I need to search the three digit code for anything ending other than "P".
>> I
>> tried your solution to see if I could make it work and then try to modify
>> as
>> needed, but could not get it to work.
>>
>> Thanks,
>> Pam
>>
>> "Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in
>> message
>> news:E90D5D7F-8FCF-40CD-B96A-EDD38223D910(a)microsoft.com...
>> > In my example the headers are in G10 (Dept) and H10 (Class)
>> > In 2007
>> > Select g11
>> > Cond formatting - manage rules - new rule - use formula to determine .
>> > -
>> > enter following in rule description
>> > =ISNUMBER(SEARCH("P",H11))+G11=5
>> > Format as required - fill cell blue or whatever
>> > Ok
>> > Obviously you will need to change the object cells to suit and use
>> > format
>> > painter to rest of range.
>> >
>> > Russell Dawson
>> > Excel Student
>> >
>> > Please hit "Yes" if this post was helpful.
>> >
>> >
>> > "Pam" wrote:
>> >
>> >> Hi,
>> >>
>> >> I have a spreadsheet with Department Column and Class Column. I need
>> >> to
>> >> highlight the department cell when it is a 5 and three-digit class
>> >> code
>> >> ends
>> >> in anything other than "P". Can this be done in code? I know I can't
>> >> use
>> >> wildcards for "**P" in conditional formatting.
>> >>
>> >> I would appreciate any help.
>> >> Thanks,
>> >> Pam
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: Pam on 25 Feb 2010 12:15
Hubisan,

This looks like it will work, but it give error message "The formula you
typed contains an error." and highlights "P" in the formula.

Thanks for your help.
Pam


"Hubisan" <hubisan(a)gmail.com> wrote in message
news:dc2c6467-307a-4455-9db8-9178ef7249fd(a)d2g2000yqa.googlegroups.com...
> Use this formula as conditional formatting:
>
> =AND(RIGHT(H11,1)<>"P";G11=5)
>
> And btw, u can use wildcards whith some formulas like
> "=Search("*P",H11)"
>
> Hubisan
>
>
>


From: Pam on 25 Feb 2010 12:36
Hubisan,

I changed the semi-colon to a comma and it works. Can you tell me what the
"1" is for in (H11,1)?

Thank you.
Pam

"Hubisan" <hubisan(a)gmail.com> wrote in message
news:dc2c6467-307a-4455-9db8-9178ef7249fd(a)d2g2000yqa.googlegroups.com...
> Use this formula as conditional formatting:
>
> =AND(RIGHT(H11,1)<>"P";G11=5)
>
> And btw, u can use wildcards whith some formulas like
> "=Search("*P",H11)"
>
> Hubisan
>
>
>


From: Russell Dawson on 26 Feb 2010 00:43
I see now where we had our wires crossed. You'd said 3 digit whereas it was
three characters. I then assumed the last char was the P or whatever.

Don't forget to give Hubisan a "Yes" for solving the problem.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

> Russell,
>
> Here's what I need to happen:
> ColB ColC
> Dept Class
> 5 GRR Highlight
> 5 GRP
> 3 GRR
> 5 GRU Highlight
> 1 GRU
>
> If Dept 5 has code ending in anything other than "P" - highlight it.
>
> Thanks again for your help.
> Pam
>
> "Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in message
> news:8F42C7C8-42E9-46DC-8420-FC3CAD9E9752(a)microsoft.com...
> > Hi Pam
> >
> > If I understand correctly then, if the cells contain 5 & 22a then it is
> > highlighted - if 5 & 22p no highlight
> > - if 6 & 22a - no highlight.
> >
> > You only described your "3 digit class code". What does it look like
> > exactly.
> >
> > My example works fine but we are obviously missing something. It will
> > work,
> > just a bit of fine tuning I'm sure.
> >
> > Regards
> > --
> > Russell Dawson
> > Excel Student
> >
> > Please hit "Yes" if this post was helpful.
> >
> >
> > "Pam" wrote:
> >
> >> Russell,
> >>
> >> I need to search the three digit code for anything ending other than "P".
> >> I
> >> tried your solution to see if I could make it work and then try to modify
> >> as
> >> needed, but could not get it to work.
> >>
> >> Thanks,
> >> Pam
> >>
> >> "Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in
> >> message
> >> news:E90D5D7F-8FCF-40CD-B96A-EDD38223D910(a)microsoft.com...
> >> > In my example the headers are in G10 (Dept) and H10 (Class)
> >> > In 2007
> >> > Select g11
> >> > Cond formatting - manage rules - new rule - use formula to determine .
> >> > -
> >> > enter following in rule description
> >> > =ISNUMBER(SEARCH("P",H11))+G11=5
> >> > Format as required - fill cell blue or whatever
> >> > Ok
> >> > Obviously you will need to change the object cells to suit and use
> >> > format
> >> > painter to rest of range.
> >> >
> >> > Russell Dawson
> >> > Excel Student
> >> >
> >> > Please hit "Yes" if this post was helpful.
> >> >
> >> >
> >> > "Pam" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> I have a spreadsheet with Department Column and Class Column. I need
> >> >> to
> >> >> highlight the department cell when it is a 5 and three-digit class
> >> >> code
> >> >> ends
> >> >> in anything other than "P". Can this be done in code? I know I can't
> >> >> use
> >> >> wildcards for "**P" in conditional formatting.
> >> >>
> >> >> I would appreciate any help.
> >> >> Thanks,
> >> >> Pam
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>
From: Hubisan on 26 Feb 2010 03:55
Ah, sorry for the mistake, using swiss german excel and we use ";"
instead of ","

Right( text, number_of_characters )

> text is the string that you wish to extract from.

> number_of_characters indicates the number of characters that you wish to extract starting from the right-most character.

and Russells formula would work as well, just add wildcards:
=ISNUMBER(SEARCH("??P",H11))+G11=5


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Reference to values disappear after closing file
Next: Showing the Differences