Results 1 to 6 of 6

20090228, 16:32 #1
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hi
I have a list of alphabetically last names, first names and they are sorted.
I' like to apply the Bold or not Bold conditional format as follow :
All last names startin with letter A = BOLD
Going down, names start with B = not bold
Then last name starting with letter C may not be in the list :
If last names start with C are present then =BOLD
If not, then D=BOLD.
And so on till Z
I've been trying more than a dozen formula in the CF... but nothing works.
TIAHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090228, 16:51 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Francis
There is a way of doing this using only one Conditional Formatting rule.
Assume your list of names is in column A, highlight the column and then in
 Format
 Conditional Formatting
Change the drop down to Formula Is
and paste this formula into the box:
=MOD(CODE(LEFT(A1,1)),2)
Change the format to bold.
I have attached a workbook to showJerry

20090228, 17:48 #3
 Join Date
 Feb 2003
 Location
 Runcorn, Cheshire, United Kingdom
 Posts
 372
 Thanks
 0
 Thanked 2 Times in 2 Posts
[quote name='Jezza' post='762502' date='28Feb09 : 21:51']=MOD(CODE(LEFT(A1,1)),2)
Change the format to bold.[/quote]
That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are 'C's, make Cs bold and Ds normal, but if there are no 'C's, make Ds bold and Es normal etc.
I would do it by having a hidden column B, with code like this from cell B2 down.
=IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
and use that column to control the conditional formatting
What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.
Amended file attached.
I'm not sure I actually like it much, because it's I'm not sure how well it'll work if you're adding and removing entries on the list

20090301, 01:34 #4
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Gfamily' post='762519' date='01Mar2009 06:48']That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are 'C's, make Cs bold and Ds normal, but if there are no 'C's, make Ds bold and Es normal etc.
I would do it by having a hidden column B, with code like this from cell B2 down.
=IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
and use that column to control the conditional formatting
What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.
Amended file attached.
I'm not sure I actually like it much, because it's I'm not sure how well it'll work if you're adding and removing entries on the list[/quote]
Hi
Thanks for your effort. I don't know much about the MOD function...guess I need to read and play around
with this function more to understand it.
Agreed with you on the last point, I 'll highlight this to the users to manage expectation.Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090301, 02:08 #5
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Jezza' post='762502' date='01Mar2009 05:51']Francis
There is a way of doing this using only one Conditional Formatting rule.
Assume your list of names is in column A, highlight the column and then in
 Format
 Conditional Formatting
Change the drop down to Formula Is
and paste this formula into the box:
=MOD(CODE(LEFT(A1,1)),2)
Change the format to bold.
I have attached a workbook to show[/quote]
Hi Jerry
Thank you very much on this, although not exactly producing the result but your solution
providing or facilitates to the correct solution by Gfamily
Like I have mentioned, I am unfamiliar with the MOD function and need to play around with this more
to understand it better.Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090301, 14:26 #6
 Join Date
 Feb 2003
 Location
 Runcorn, Cheshire, United Kingdom
 Posts
 372
 Thanks
 0
 Thanked 2 Times in 2 Posts
[quote name='franciz' post='762565' date='01Mar2009 06:34']Thanks for your effort. I don't know much about the MOD function...guess I need to read and play around[/quote]
MOD is just a mathematical function that returns the remainder when one number is divided by another. I'm just using as a way of getting two alternating values using addition only.
If the previous hidden value is zero, adding 1 gives 1, and the remainder when divided by 2 is 1.
If the previous hidden value is 1, adding 1 gives 2, so the remainder when divided by 2 is 0.