If value in column A of Sheet1 appears twice in column B of Sheet2, highlight value in Sheet1

1
votes

Conditional Formatting - If a value in column A of sheet 1 appears twice in column b of sheet 2, highlight the cell in sheet 1

I've been messing with the indirect modifier to see if I can get this to work. 

=COUNT(INDIRECT("'Order Confirmation'!B:B"),A1)

But this has currently just highlighted all the cells on sheet 1 column a. I have tried

=COUNT(INDIRECT("'Order Confirmation'!B:B"),A1>2)

=COUNT(INDIRECT("'Order Confirmation'!B:B"),A1=2)

and this has changed nothing. I would appreciate some assistance

1
filtergoogle-sheetscountifgoogle-sheets-formulags-conditional-formatting

1 Answers

0
votes

if Sheet2 is like:

0

then Sheet1 will be like:

=FILTER(A1, COUNTIF(FILTER(INDIRECT("Sheet2!B:B"), 
 COUNTIF(INDIRECT("Sheet2!B:B"), INDIRECT("Sheet2!B:B"))>1), A1))

0

UPDATE: 

=FILTER(A1, COUNTIF(FILTER(INDIRECT("'Order Confirmation'!B:B"), 
 COUNTIF(INDIRECT("'Order Confirmation'!B:B"), 
 INDIRECT("'Order Confirmation'!B:B"))>1), A1))

...as a custom formula because you have numbers in dataset not text