Conditional formatting - multi-item text list

[String]

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 |
+-------------------------------------------------------------------+

 

[Dave Peterson]

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]

Dave,

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

 

[String]

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
hard time implementing the code you suggested.

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

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

I'd really appreciate it!

Sincerely,
Strin

+-------------------------------------------------------------------
|Filename: Sample Prob.zip
|Download: http://www.excelforum.com/attachment.php?postid=5147
+-------------------------------------------------------------------

 

[Dave Peterson]

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.