Conditional format Google sheet when criteria is matched from another sheet

0
votes

I have a Google sheet that contains two sheets. One ("Actual") has a range of values under different day headings(Sunday, Monday, Tuesday .....), which is updated every day. Therefore the days in the column headers change day-on-day, along with the values under each heading. The columns are E:K in both sheets, the headers being in row 1 of each, the values in row 2. In the other sheet ("Capacity") is a static list of values under the days of the week.

I want to use conditional formatting to highlight the values in "Actual" if they are higher than the values under the corresponding day in "Capacity". I know to use the INDIRECT function to reference another sheet and can write a custom formula to match the day, but I'm struggling to write one that first matches the day of the week, then checks against the value.

Unfortunately I'm unable to share documents outside of my organisation, otherwise, I'd provide a copy. I appreciate this may make it difficult to help me.

1
google-sheetsgs-conditional-formatting
load your scenario: docs.google.com/spreadsheets/d/… – player0
Have added some values to Actual. These, along with the days in the header will update daily though. So tomorrow, E1 will be Friday, with new values underneath etc. – P.Seymour

1 Answers

1
votes
=HLOOKUP(E$1:K$1,INDIRECT("Capacity!E1:K2"),2,0)

0

demo spreadsheet