Forum

Notifications
Clear all

Conditional formatting for duplicate values in different sheets

2 Posts
2 Users
0 Reactions
263 Views
(@sasaki)
Posts: 1
New Member
Topic starter
 

Hey everyone, I have a conditional formatting question between two different sheets.

In summary, I have a high number of people that will be arriving and checking-in to a building. When they arrive, they will scan a QR code and submit a Microsoft Form to declare their arrival. This Form has been linked via Power Automate to submit a response to an online Excel sheet.

This spreadsheet contains two sheets:

Sheet 1: Predefined data of who we're expecting, including their name and unique identifier number (plus a lot of other info). The ID number is in column D, whilst a 'checked in?' column is in Column E.

Sheet 2: The sheet that updates with the raw data of what is submitted via the Microsoft Form, including their name and unique identifier number. The ID number is in Column D too.

My question: is it possible to highlight a particular cell in Column E of Sheet 1 if that ID number in Column D of Sheet 1 also appears in Column D of Sheet 2?

I hope that makes sense - any help much appreciated!

 
Posted : 17/09/2022 10:35 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

Hi Sasaki,

Please have a look at the attached workbook. It contains an example of what you described. The CF rule used in Sheet1 is as follows:

=ISNUMBER(MATCH(E2,Sheet2!D:D,0))

applied to a range of cells in Sheet1 starting at $E$2.

See if you can get it to work on your end.

Riny

 
Posted : 18/09/2022 2:09 am
Share: