Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
valadating a data match
i have a list of post codes (no other data) on worksheet 2 when i use a drop down list to select the postcode from worksheet 1 i need to cross refrence this data with data in worksheet 2 then if a match is found show as true ( in a designated cell) and if a match is not found false (in a designated cell) or the cell showing the post code could change colour IE: blue for match and red for mismatch thanx -- kevatt ------------------------------------------------------------------------ kevatt's Profile: http://www.excelforum.com/member.php...o&userid=30245 View this thread: http://www.excelforum.com/showthread...hreadid=519107 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
valadating a data match
Could you make two helper columns and place into them the column ,You can then do a simple lookup formula, =lookup(A1,A2:B10) for this example: A1 has the criteria B1 has the formula A2 to B10 has the data the formula searches column A for whatever you have in Cell A1, and returns the value from Column B You can customize this to fit your requirements -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519107 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
valadating a data match
is there a way that i can enter a short formular into a cell and ask it to match data that would have been manully entered iE: cells t1 to t112 has the postcodes in it e11 is where the data is manuly entered a data match would flag a match in some way or visa versa a mismatch could flag thanx -- kevatt ------------------------------------------------------------------------ kevatt's Profile: http://www.excelforum.com/member.php...o&userid=30245 View this thread: http://www.excelforum.com/showthread...hreadid=519107 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
valadating a data match
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. kevatt wrote: i have a list of post codes (no other data) on worksheet 2 when i use a drop down list to select the postcode from worksheet 1 i need to cross refrence this data with data in worksheet 2 then if a match is found show as true ( in a designated cell) and if a match is not found false (in a designated cell) or the cell showing the post code could change colour IE: blue for match and red for mismatch -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match data, count data and report to diffrent sheet. | Excel Discussion (Misc queries) | |||
Cross match data in Col A v/s Col B and display match in Col 3 | Excel Discussion (Misc queries) | |||
Match data in 2 columns and return data from 3rd column | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |