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 using formlar =a1=d1 is there a way that i can add to this formlar to search t1 to t112
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.

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