CODEWITHSUNDEEP
google-sheetsconditional-formatting
Matthew Newton
Matthew Newton

Reputation: 7

How to use MINIFS in conditional formatting referencing another sheet?

I am trying to format a cell in one sheet based on the minimum value corresponding with certain criteria in a range of cells in another sheet.

In the sheet pictured here Sheet "Week 1" titled "Week 1", I color the cells in the column titled "Driver" gold if that driver had the best finishing position (shown in the column titled "Race Finish Position") in their class (represented by the values "Hypercar", "LMP2", and "GT3" in the column "Class" using this MINIFS formula:

=G7=MINIFS(G$7:G$21,C$7:C$21,"Hypercar")

I would like to do the same for a column in another sheet pictured here Sheet "Driver Standings"titled "Driver Standings". I looked at options for this and it seems I'll have to use the INDIRECT function. I tried this in the conditional formatting

=G7=MINIFS(INDIRECT("Week 1!G$7:G$21,C$7:C$21,"Hypercar""))

but it didn't work.

Upvotes: 0

Views: 193

Answers (1)

z..
z..

Reputation: 13156

You have to call INDIRECT twice, once for each range:

=G7=MINIFS(INDIRECT("Week 1!G7:G21"),INDIRECT("Week 1!C7:C21"),"Hypercar")

Upvotes: 0

Related Questions