Hi,

I have a list in worksheet "Clients"

1) Shell
2) ABC
3) Chevron
etc.etc.

(Actually over 100 Clients)

I have a separate sheet "Summary",

if an individual enters a Client in cell A1 which is one of thes
Clients,
a message box prompts "You have entered a key client"

Slight catch is that if the client entered is a derivative of one o
the list
e.g. ABC Co, ABC Ltd
Then the message box still appears.

Thank

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56060

Not a message box, but perhaps this will help.

First define the list on Clients as a named range (InsertNameDefine...) of
ClientList, and then select A1 and goto Conditional formatting
(FormatConditional Formatting), change Condition 1 to Formula Is, add the
formula

=ISNA(MATCH(LEFT(A1,LEN(ClientList)),ClientList,0) )

select the pattern tab and choose a colour.

This will highlight a bad name.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T De Villiers"
wrote in message
news:T.De.Villiers.2atth1_1152697506.5888@excelfor um-nospam.com...

Hi,

I have a list in worksheet "Clients"

1) Shell
2) ABC
3) Chevron
etc.etc.

(Actually over 100 Clients)

I have a separate sheet "Summary",

if an individual enters a Client in cell A1 which is one of these
Clients,
a message box prompts "You have entered a key client"

Slight catch is that if the client entered is a derivative of one on
the list
e.g. ABC Co, ABC Ltd
Then the message box still appears.

Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=560606

Actually, we can do better, using Data Validation.

Name the range as mentioned before.

Select A1.

Goto Data Validation, DataValidation

Select an allow type of Custom

Use the same formula

Be sure to uncheck the Ignore Blank box

Select the Error tab and add a message.

Now, entering an invalid message gets you the message box.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Not a message box, but perhaps this will help.

First define the list on Clients as a named range (InsertNameDefine...)
of
ClientList, and then select A1 and goto Conditional formatting
(FormatConditional Formatting), change Condition 1 to Formula Is, add the
formula

=ISNA(MATCH(LEFT(A1,LEN(ClientList)),ClientList,0) )

select the pattern tab and choose a colour.

This will highlight a bad name.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T De Villiers"

wrote in message
news:T.De.Villiers.2atth1_1152697506.5888@excelfor um-nospam.com...

Hi,

I have a list in worksheet "Clients"

1) Shell
2) ABC
3) Chevron
etc.etc.

(Actually over 100 Clients)

I have a separate sheet "Summary",

if an individual enters a Client in cell A1 which is one of these
Clients,
a message box prompts "You have entered a key client"

Slight catch is that if the client entered is a derivative of one on
the list
e.g. ABC Co, ABC Ltd
Then the message box still appears.

Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread:
http://www.excelforum.com/showthread...hreadid=560606