Highlighting Rows when 2 columns don't match from another sheet.

[steveh8204]

Hi, firstly I'm not sure if the title explains very well my issue so I'll explain what the function needs to do.

I have two sheets with numerous columns on, some similar, some not. On my first sheet I need to look up the value in A1 for the similar value in the second sheet but I need to look up the value in Column B for the value as well and it needs to match column b on the second sheet.

The picture below probably better explains it.

https://drive.google.com/file/d/1qGJVvyL6wK0qbmlHk3Sb8t1F0_WD-Nem/view?usp=sharing

Thanks in advance for any help.

 

[steveh8204]

The problem I am having possibly relates to Excel not liking references to a different sheet in the formula. Another issue I had someone suggested using a named range instead so that could be the problem. When I get a chance I'm going to try this one again and hopefully it will work for me.

 

[oldbrewer]

$a$1 is the start point of the offset
=offset(from here, down to here, along to here)

 

[oldbrewer]

you are making this too complicated
if you are in sheet2 and need to refer to a cell in sheet1 you use =sheet1!B3

you are asking if a value associated with a code in sheet2 matches the value next to the SAME code in another sheet, and if not color the code in sheet2

my formula does that

open a new work book and make a pretend example with 5 codes in column B of sheet1 and column G of sheet2, and make just one of the associated values not match, then copy sheet1 on to here, and immediately after copy sheet2 on here with the formula you are using - at this stage do not use conditional formatting just make the formula say "no match"

 

[steveh8204]

OK thanks I'll try tomorrow. I'm convinced it's to do with the named range thing though as a different formula someone else gave me did nothing until I used a named range as the range reference instead of a sheet ref.

Someone else said certain editions of Excel you have to use named ranges as CF doesn't work with references from other sheets unless they are.

When I've put some formulas in I've had some error message about excel thinking it's a formula (er, it is) which doesn't make sense.

Anyway I'll try again. Thanks again.