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

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

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

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