0

I have two sheets:

The first is the Master Sheet of responses to a survey.

The second will be an Update Sheet auto-generated from the new responses. New responses can be either entirely new rows or edits/updates to previous entries.

I am trying to create a Conditional Formatting rule to auto-highlight any rows in the Update Sheet which do not match the corresponding rows in the Master sheet. (A row "does not match" when it shares the same Respondent ID number as the Master sheet row but contains different information in the following cells.) I'm also trying to make it ignore any empty cells.

I created named ranges: "Master" and "Update" on each sheet respectively.

Here's an image of what I'm trying to accomplish: Highlight Demo

I'd like to accomplish this without scripts or using a helper column if possible.

Any help from you genuine experts out there would be greatly appreciated!

And here's the Demo Sheet for reference.


So far I have tried to use INDIRECT to accomplish the cross-sheet highlighting (below). However, that didn't work so I'm obviously doing something wrong.

CONDITIONAL FORMATTING:
- Apply to Range: A3:A200,F5:F200
- Custom Formula: =ISNA(match(A3,INDIRECT("Master!F5:AS"),0))

Update #2:


With help from @I'-'I I've been able to get closer with:

CONDITIONAL FORMATTING: 
Apply to range: A3:F200
Custom Formula: =and(isna(match($A3,INDIRECT("Master!F5:F"),0)),not(isblank($a3)))

Highlight Demo v3

However, while it now highlights the "New Data" rows, it ignores the "Updated Data" row which has the same Respondent ID but different data in the following cells.

Any suggestions on how to rectify this would be appreciated!

1
=AND(NOT(ISBLANK($A3)),OR(IFERROR(ARRAYFORMULA(VLOOKUP($A3,INDIRECT("MASTER!F5:J"),COLUMN($A$1:$E$1),0)<>$A3:$E3),1)))
  • VLOOKUP to lookup ID(A3) in the master sheet and return the corresponding 5 columns(5 cells).
  • VLOOKUP results are compared against current row's 5 columns(<>A3:E3).
  • IFERROR to return TRUE if lookup fails(suggesting NewData).
  • ISBLANK to ignore blank rows.
  • AND/OR/NOT Self explanatory. Used to combine TRUE/FALSE in a specific way to achieve intended result.
  • Please explain your formula – Rubén Jun 5 '18 at 23:09
  • @I'-'I! That did the trick. Also, if you would be willing to briefly explain your solution, I'm sure that would be helpful to the rest of us who are still learning as well. Thank you either way! – Geoff Jun 5 '18 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.