0

I have created a rule for a sheet in Excel to color the two columns unless it matches on another sheet. We are using this for records management of users.

Basically we want to paste a list of names on sheet2 and it tell us if they're on sheet1. We are using Excel 2010 and I currently have this for my formula (ColumnA= LastName, ColumnB= FirstName):

=COUNTIFS(Sheet1!$A$11:$A$200, A1, Sheet1!$B$11:$B$200, B1)=0

The kicker is that it works for the last result when I paste the list of names in, but it will work correctly when I manually type the name in that should be un-highlighted.

Any idea's to how I can change the formula so this doesn't happen?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Steven B
  • 1
  • 1

1 Answers1

2

Please try: 

=COUNTIFS(Sheet1!$A$11:$A$200, $A1, Sheet1!$B$11:$B$200, $B1)=0

(ie with anchoring of the column references in Sheet2) and, when pasting, selecting Paste Special... with All merging conditional formats checked.

pnuts
  • 58,317
  • 11
  • 87
  • 139