Match one cell then compare another

[Ne0lithic]

So I have a master asset list (sheet 2)

asset number, location, type

and I have a site visit list from a colleague (sheet 1)

location, type, asset number

i want to be able to look up the asset number from site list and find the asset in the master list then compare the locations and conditional format the asset number to green if correct or red if the location differs on the site list leaving the master one untouched.

I hope this makes sense been trying to think of ways of doing it but all my ideas have evolved 3or4 steps

many thanks

Gary

 

[bschwartz]

Maybe this will help you. For simplicity, I built this out in the same worksheet:

Excel 2010
	A	B	C	D	E	F	G
1	Asset Number	Location	Type		Location	Type	Asset Number
2	10	a			d		7
3	8	c			b		2
4	6	b			c		1
5	3	b			d		10
6	5	d			d		5
7	7	a			b		3
8	4	c			b		9
9	9	a			c		4
10	1	b			a		8
11	2	b			b		6
Sheet95

=NOT(INDEX($B$2:$B$11,MATCH($G2,$A$2:$A$11,0))=$E2)

Conditional formula highlights
E2:G2
E4:G5
E8:G8
E10:G10

 

[Ne0lithic]

You my friend are brilliant. working a treat, does throw a new question up

as the calculation is based on a pair of cells the conditional formatting is different for every cell in the column so how do you replicate conditional formatting down? or will have to make copy and paste for each cell as i have 874 of them to have checked,

BTW for your help i would normally "buy you a pint" but in this case i donated £5 to BHF

 

[bschwartz]

Thanks for the kind words. You are very welcome.

In reference to your second question, you should be able to highlight the entire range, enter the one conditional formatting rule, and it should be applied to all records. If you have trouble, post the specific ranges of your tables, and the conditional formatting formula you are using, and I'll take a look.

 

[Ne0lithic]

I thought that to but no!, it only reference a single pair of cell for the entire column, then i figured it out its to do with absolute refereces and relative references

=NOT(INDEX('Gsmart 11112013'!$F$1:$F$874,MATCH($F5,'Gsmart 11112013'!$A$1:$A$874,0))=$C5) is what i ended up with

but i needed it to be relative so

=NOT(INDEX('Gsmart 11112013'!$F$1:$F$874,MATCH(F5,'Gsmart 11112013'!$A$1:$A$874,0))=C5) this is correct then in manage contitional formatting you can define the areas it is going to be applied to.

but again thankyou for your help

 

[bschwartz]

I don't really agree with that. If you do it that way, it will only highlight the cells in the column with the criteria, not the whole row.

And besides for that, there is no reason that would enable you to adjust the range any more than the previous formula. It only affects how many columns get highlighted, not how many rows.