How to use MINIFS in conditional formatting referencing another sheet?

Question

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.

z.. · Accepted Answer · 2024-02-10 23:55:06Z

0

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

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

answered Feb 10, 2024 at 23:55

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matthew Newton Over a year ago

I copied and pasted that into the conditional formatting and it shows as a valid formula but doesn't change the color of the cells. I tried this =MINIFS(INDIRECT("Week 1!G$7:G$21"),INDIRECT("Week 1!C$7:C$21"),"Hypercar") and it colored the whole row. Adding G7 back to the front removes the color again. Any tips?

Matthew Newton Over a year ago

I tried this: =INDIRECT("Week 1!G$7")=MINIFS(INDIRECT("Week 1!G$7:G$21"),INDIRECT("Week 1!C$7:C$21"),"Hypercar") and it also colors the whole row.

z.. Over a year ago

please share a sample sheet.

Matthew Newton Over a year ago

Not sure if this is what you meant but here is a link to a copy of the spreadsheet. Thank you so much for your help. docs.google.com/spreadsheets/d/…

Matthew Newton Over a year ago

I realized I could simplify things by using a MAXIFS function within the same sheet, but I ran into a problem there as well. I feel like that path is much simpler, so i posted another question based on it. stackoverflow.com/questions/77975831/… thank you for your help. I think I'm going to pursue this option instead.

Collectives™ on Stack Overflow

How to use MINIFS in conditional formatting referencing another sheet?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related