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.
You have to call INDIRECT twice, once for each range:
=G7=MINIFS(INDIRECT("Week 1!G7:G21"),INDIRECT("Week 1!C7:C21"),"Hypercar")