validating numbers n text in same cell

[kevatt]

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

 

[Tom Ogilvy]

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 here:

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 Format>Conditional 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.

 

[Tom Ogilvy]

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

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 here:

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 Format>Conditional 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.

 

[Guest]

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.