Excel: Conditional formatting, simplifying retrieving data from a range of rows

Question

I am trying to create a schedule for a limited number of trainings (around 50) that are distributed between a limited number of venues (around 15), and I am looking for a smarter approach than the one I am using currently.

The schedule consists of two spreadsheets:

Spreadsheet 1 contains the data for the trainings. One row per training. With colums for name of the training venue, start date and end date.

Spreadsheet 2 is supposed to be a visual representation of the data in spreadsheet 1 that adapts to changes in the original data. So far it contains one column for each day of the year and one row for each training venue. So each cell represents a specific date at a specific training venue.

The objective is that if sheet 1 says a training is happening at venue AZ from 1 April 2017 until 27 April 2017, the cells representing the dates from 1 April until 27 April in the row for training venue AZ in sheet 2 be marked through conditional formatting.

The conditional formatting function I am currently using for the row that represents training venue AZ would be:

=IF(OR(AND(Sheet1!$C$5="AZ";Sheet1!$F$5>=B$6;Sheet1!$E$5<=B$6);AND(Sheet1!$C$6="AZ";Sheet1!$F$6>=B$6;Sheet1!$E$6<=B$6); ...... AND(Sheet1!$C$50="AZ";Sheet1!$F$50>=B$6;Sheet1!$E$50<=B$6));TRUE;FALSE)

So the repetitive AND part checks for each cell whether the date for its column (contained in row 6) falls within the start and end date of any of the trainings in sheet 1 and whether this training takes place at training venue AZ. If this is true for any of the trainings in sheet 1, the IF function is true and the cell is marked.

Surprisingly enough, this is working so far. However, it is a very time consuming and inelegant solution. So now I am looking for a way to stop having to repeat the AND part of the function 50 times for each of the 15 training venues.

Extensive googling suggested, that array formulas might be a way to go, and that conditional formatting is per default treated as an array formula, so I tried:

=IF(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54));TRUE;FALSE)

and

=IF(OR(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54)));TRUE;FALSE)

But so far without any luck. I think I might not have fully grasped how array formulas work. So if you have spotted any obvious errors in my function, have any thoughts on what I could try, or can suggest any alternative approaches to what I am trying to do, please let me know.

Otherwise I might lose my mind copying and adapting this bloody AND section ad infinitum.