validating numbers n text in same cell
 

having asked this question on many occasions and tried members
suggestions it always seems to kick up problems

i need to validate postcodes, once a postcode is entered manually or
selected from a listbox i need to validate it from a refrence list on
the same worksheet and have it flag up true or false when a match is
found

if there is a better way to ask this question could someone please
advise

thanx


--
kevatt
------------------------------------------------------------------------
kevatt's Profile: http://www.excelforum.com/member.php...o&userid=30245
View this thread: http://www.excelforum.com/showthread...hreadid=519156

validating numbers n text in same cell
 

JE Suggestions was Excellet - don't understand it? - Can't implement it?

Also

Did you Try Debra's suggestion:
First, name the list of postal codes on worksheet 2. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, you could use data validation to prevent invalid entries, by using
data validation lists for the postal code entry cells on worksheet 1.

http://www.contextures.com/xlDataVal01.html

Or, use conditional formatting to highlight cells with an invalid entry:

http://www.contextures.com/xlCondFormat01.html

For example, if the list on worksheet 2 is named PostalCodes:

Select the cells on sheet 1 where postal codes will be entered
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, enter:
=AND(C4<"",COUNTIF(PostalCodes,C4)=0)
(where C4 is the address of the active cell)
Click the Format button, and choose a colour on the Patterns tab
Click OK, click OK.

--
Regards,
Tom Ogilvy


"kevatt" wrote in
message ...

having asked this question on many occasions and tried members
suggestions it always seems to kick up problems

i need to validate postcodes, once a postcode is entered manually or
selected from a listbox i need to validate it from a refrence list on
the same worksheet and have it flag up true or false when a match is
found

if there is a better way to ask this question could someone please
advise

thanx


--
kevatt
------------------------------------------------------------------------
kevatt's Profile:
http://www.excelforum.com/member.php...o&userid=30245
View this thread: http://www.excelforum.com/showthread...hreadid=519156

validating numbers n text in same cell
 

Just to Add, Countif, as suggested by JE, is very robust in terms of
matching Alpha, AlphaNumeric and Numeric with no modifications.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...


JE Suggestions was Excellet - don't understand it? - Can't implement it?

Also

Did you Try Debra's suggestion:
First, name the list of postal codes on worksheet 2. There are
instructions he

http://www.contextures.com/xlNames01.html

Then, you could use data validation to prevent invalid entries, by using
data validation lists for the postal code entry cells on worksheet 1.

http://www.contextures.com/xlDataVal01.html

Or, use conditional formatting to highlight cells with an invalid entry:

http://www.contextures.com/xlCondFormat01.html

For example, if the list on worksheet 2 is named PostalCodes:

Select the cells on sheet 1 where postal codes will be entered
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, enter:
=AND(C4<"",COUNTIF(PostalCodes,C4)=0)
(where C4 is the address of the active cell)
Click the Format button, and choose a colour on the Patterns tab
Click OK, click OK.

--
Regards,
Tom Ogilvy


"kevatt" wrote in
message ...

having asked this question on many occasions and tried members
suggestions it always seems to kick up problems

i need to validate postcodes, once a postcode is entered manually or
selected from a listbox i need to validate it from a refrence list on
the same worksheet and have it flag up true or false when a match is
found

if there is a better way to ask this question could someone please
advise

thanx


--
kevatt
------------------------------------------------------------------------
kevatt's Profile:
http://www.excelforum.com/member.php...o&userid=30245
View this thread:
http://www.excelforum.com/showthread...hreadid=519156

validating numbers n text in same cell
 

if you have all the legitimate post code on another sheet, then why aren't
you simply using the speradsheet MATCH() function? if the code isn't there,
you'll raise an error.

"kevatt" wrote:


having asked this question on many occasions and tried members
suggestions it always seems to kick up problems

i need to validate postcodes, once a postcode is entered manually or
selected from a listbox i need to validate it from a refrence list on
the same worksheet and have it flag up true or false when a match is
found

if there is a better way to ask this question could someone please
advise

thanx


--
kevatt
------------------------------------------------------------------------
kevatt's Profile: http://www.excelforum.com/member.php...o&userid=30245
View this thread: http://www.excelforum.com/showthread...hreadid=519156