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.

--
Regards,
Tom Ogilvy


"kevatt" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
>
>
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "kevatt" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

[Patrick Molloy]

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