valadating a data match

[kevatt]

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

 

[davesexcel]

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

 

[kevatt]

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

 

[Debra Dalgleish]

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.