Conditional formatting - multi-item text list

Hello! I'm working on a spreadsheet with phone numbers for employees and departments. I've been asked to have the department info list in bold.

For example, say I've got entries for:
Accounting
Allen
Carla
Controller

It's easy to use conditional formatting to find and bold one department at a time: Cell value equal to ="Accounting" (I attached a Word file with a screenshot, for your reference.)

How would I adjust the formula to include additional items? We have 20+ departments!

Since this file may be widely distributed, and I'm not sure what security settings the users will have, I'm trying to refrain from using macros.

All help and suggesstions are sincerely appreciated!

Put that list of 20 departments in A1:A20 of a different worksheet (say Sheet2)
(don't put anything below those departments)

Then select that column A of sheet2
Insert|Name|Define|MyDepts

Then back to your cell (say A1 of sheet1)
format|conditional formatting
formula is:
=COUNTIF(MyDepts,A1)>0

Give it a nice bold format.


String wrote:
>
> Hello! I'm working on a spreadsheet with phone numbers for employees
> and departments. I've been asked to have the department info list in
> bold.
>
> For example, say I've got entries for:
> Accounting
> Allen
> Carla
> Controller
>
> It's easy to use conditional formatting to find and bold one department
> at a time: Cell value equal to ="Accounting" (I attached a Word file
> with a screenshot, for your reference.)
>
> How would I adjust the formula to include additional items? We have
> 20+ departments!
>
> Since this file may be widely distributed, and I'm not sure what
> security settings the users will have, I'm trying to refrain from using
> macros.
>
> All help and suggesstions are sincerely appreciated!
>
> +-------------------------------------------------------------------+
> |Filename: Excel Screen.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=5139 |
> +-------------------------------------------------------------------+
>
> --
> String
> ------------------------------------------------------------------------
> String's Profile: http://www.excelforum.com/member.php...o&userid=37042
> View this thread: http://www.excelforum.com/showthread...hreadid=567697

--

Dave Peterson

Dave,

Thanks for the tip, I'll give that a shot. I appreciate your help!

Dave, I tried your suggestion and haven't been able to get it to work - through no problem with your suggestion, I'm sure, I'm just having a hard time implementing the code you suggested.

Here's a recap of what I'm looking to do: the spreadsheet contains phone extensions and names. I want to have extensions and names of departments in bold. I attached a sample spreadsheet to this post with 4 entries, 2 names and 2 departments.

Would someone please implement a fix on that sample spreadsheet and then re-post the updated file? I've been working on it on my own for a few days, and scouring the web for a similar example, but haven't had any luck.

I'd really appreciate it!

Sincerely,
String

I don't connect through excelforum, so I don't see the attachment.

Maybe someone using excelforum will chime in--or you can repost your question in
plain text.

String wrote:
>
> Dave, I tried your suggestion and haven't been able to get it to work -
> through no problem with your suggestion, I'm sure, I'm just having a
> hard time implementing the code you suggested.
>
> Here's a recap of what I'm looking to do: the spreadsheet contains
> phone extensions and names. I want to have extensions and names of
> departments in bold. I attached a sample spreadsheet to this post with
> 4 entries, 2 names and 2 departments.
>
> Would someone please implement a fix on that sample spreadsheet and
> then re-post the updated file? I've been working on it on my own for a
> few days, and scouring the web for a similar example, but haven't had
> any luck.
>
> I'd really appreciate it!
>
> Sincerely,
> String
>
> +-------------------------------------------------------------------+
> |Filename: Sample Prob.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5147 |
> +-------------------------------------------------------------------+
>
> --
> String
> ------------------------------------------------------------------------
> String's Profile: http://www.excelforum.com/member.php...o&userid=37042
> View this thread: http://www.excelforum.com/showthread...hreadid=567697

--

Dave Peterson