Using Conditional Formatting to highlight data in new table that matches data in separate table

by Melly Lilly   Last Updated January 03, 2018 02:03 AM

Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and add them to the new table with an alternative cell to fill the missing data.

Example:

The sheet '5 Star Gear Sets' starts to fill in column E with data that may or may not be present in column F of another sheet, if the data is not present, it fills in column E with data existing in column G of the other sheet instead.

Trying to Accomplish:

I'm trying to use Conditional Formatting to Highlight all cells in the E column who's data was filled in by the data of column G from the other sheet.

Current Formula:

Apply to Range: E6:E1001

Format Cells If...: Custom Formula

=MATCH(
E6
,indirect(
"'5 Star Gear Sets (Hidden)'!E6:E"
)
,0
) 
=MATCH
(B6
,indirect(
"'5 Star Gear Sets (Hidden)'!A6:A"
)
,0
)

The Problem: It works until there is a repeat number in column E. It fails to highlight any cell with a number that has already been highlighted in column E.

Link:

Below is an updated copy of the spreadsheet in question

https://docs.google.com/spreadsheets/d/1HzmDJtYun_vqVvV1HKys3UNxIfyrsA4imcjw8PcoSGQ/edit?usp=drivesdk



Related Questions





Copying values between google sheets

Updated July 26, 2018 21:03 PM