Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validating Text | Excel Discussion (Misc queries) | |||
Validating certain cell | Excel Worksheet Functions | |||
Validating hyperlinks and text for hyperlink | Excel Discussion (Misc queries) | |||
Validating random numbers | Excel Worksheet Functions | |||
Validating Cell | Excel Programming |