INDEX MATCH inside Conditional Format

New Contributor

Hello. I have looked all over and have seen various formulas that seem to work for the demonstration provided with those formulas, but I cant seem to get them to work with what I am doing.


In the attached workbook, on the "Assign Stations" sheet, I need cells in column C (Badge) to be filled with black when the station in column A (Station) is marked with an "x" on the "Non-Functioning" sheet.


I need this done via INDEX MATCH as the order of the station numbers on "Assign Stations" sheet is randomized throughout the day and the blackout needs to follow the station.


I can easily get the station to blackout but I am unable to combine a formula that allows that blackout conditional format to follow the station as it is moved to a new row.


Example. Station M1 is on row 2 at start of shift. After lunch, station M1 may be on row 6. If I blackout station M1's Badge cell at SoS and it is not repaired before lunch, when the stations randomize I need that blackout to follow to the new row station M1 is on (row 6 in this example), instead of staying on row 2 with whatever station is there after lunch.


Thank you for any thought and help on this matter!


Assign Station Sheet.PNGNon-Functioning Sheet.PNG





2 Replies
best response confirmed by Hans Vogelaar (MVP)

@TreThacker The attached file contains a working example.

@Riny_van_EekelenThank you for the help! This worked really well.