Conditional Formatting against formula results

**undecidedkelly** · 06-12-2018, 10:40 AM

Hello,

I've been creating an tool to support my team in planning out conferences (based off a template I found here

). One tab is the data entry, title, date, time etc. while the other tab is the Conference view that pulls all the information in using this formula =IF(ISNA(MATCH($A$5&"_"&W$6,DataEntry!$A:$A,0)),"",INDEX(DataEntry!$C:$C,MATCH($A$5&"_"&W$6,DataEntry!$A:$A,0))). In each room we can have up to 4 classes a day but sometime we have only 1 that last all day. What I would like to see is that is the class is longer than the 2 hr block available, the next block will fill black (with Red text if there is any). I got close but it was filling the cells even when there was no data pulling over.

the word doc shows Class 5A is all day. I want the cells in yellow to format.

Thanks,
Kelly

**dflak** · 06-13-2018, 11:52 AM

Everything you have will be blocked out for the AM according to the rules.

The AM is apparently from 8 AM to 11:30 AM and PM seems to be 12:30 PM to 4:45 PM. Is your objective the following?

I fill in a time for the first class on the AM. I need to know if there is enough time left in the AM for another class? This depends on your definition of "enough time." Do we wait until late AM is filled in and compare its start time with the end time of the previous session and compare its end with 11:30? I notice that many of your classes run 15 minutes into lunchtime.

Or do you have "fixed" class times 8:30 - 10:45 and 10:45 - 11:30?

You have 4 periods a day. Obviously you want to also check if the morning class overruns one or both the afternoon classes.

**undecidedkelly** · 06-25-2018, 05:17 PM

The class times aren't fixed, but they are fairly standard. For example, all Early AM Classes will start between 8 am & 9 am. That class can last either 2, 4, 6 or 8 hours on average. If it's a 2 hour class, nothing needs to happen since you can book the room after. BUT if it's a 4 hour class, we'd like the Late AM to Fill Black w/ Red font. IF it's 6 hours, LATE AM & EARLY PM should fill. And if it's all day 8 hour class, LATE AM, EARLY PM & LATE PM should all fill. Similar logic should following if the class doesn't start until LATE AM and end anytime past that 2 hour check. I got close, where it worked but if the first time block was blank it followed the formatting making it look like the room was book when it wasn't.

**dflak** · 06-26-2018, 11:44 AM

Either way, we are going to have to deal with borders. For example, 9 AM class that runs until 11 AM should occupy both AM blocks.

I can make the following user-definable:
Early AM = 8:00 AM to 9:45 AM (1 hour, 45 mintues)
Late AM = 9:45 AM to 11:30 AM (1 hour, 45 minutes)
Early PM = 12:30 PM to 3:00 PM (2 hours, 30 Minutes)
Late PM = 3:00 PM to 5:30 PM (2 hours, 30 Minutes)

If a class starts in a time period or ends in a time period or spans it entirely, then that period gets blocked out.

Does this sound like what you want to do?

**undecidedkelly** · 06-26-2018, 11:56 AM

Yes, that sounds right

How do I do this with borders??

**dflak** · 06-26-2018, 11:59 AM

Now that I know the requirement, that's what I'll have to figure out

. I think it will wind up being a complicated if statement. I'll have to check all 4 time periods to see where they touch and overlap. I'll probably work it out with helper cells and then copy the formulas to the conditional formatting.

**dflak** · 06-28-2018, 11:00 AM

I think I have this working. I converted the data entry into an Excel table. Normally Excel tables confer a lot of advantages. In this case the main advantage is being able to understand the formulas since they can use column headers.

The overall design uses a Shadow Sheet that mirrors the Calendar. There is a VB program that populates the cells with True, based on the dates and times entered on the Data Entry Sheet. I am not using the calendar itself to decide whether a range should be shaded or not.

The Parameters Sheet contains two important pieces of information:
- The start and end times of the 4 time blocks during the day
- The offset (how many rows to go down) to find the day of the week.
Once these values are filled in, you can hide this sheet and forget about it.

I adopted you conventions and used the Calendar Position extensively.

There are several “helper columns” at the end of the Data Entry Table, A, B, C, D, DOW (Day of week), Offset (looked up by DOW) and Series. Series isn’t really used by the program but it does help me filter the results for testing purposes.

A, B, C and D contain a formula that evaluates to TRUE if the start time and end time overlap that time period. Think of these as “Y values” They tell me if a certain vertical block of cells needs to be shaded. Each block is 12 rows deep. The offset with DOW tells me how far to go down and A, B, C or D tells me how many additional rows to go down to start the shading.

I found one blank row in one of the blocks: I eliminated it so all the blocks are 12 rows deep.

To get the “X position,” I established a number of named ranges in Row 6 of the Shadow Sheet called “Base_1,” “Base_2,” etc. all the way to “Base_15.”

So using something like 4a determines both X and Y “coordinates” for the shading.

The program reads the information in the helper columns and determines whether to put a TRUE in the block of cells.

Conditional formatting is set up on the calendar sheet to shade the cell when the corresponding cell on the Shadow Sheet is true.

There are some errors in your calendar – For example on 7/24 (Tuesday) in Block 4b, you are showing the Early AM times as 12:45 to 4:45. This class should actually be in Block 4c. The Data Entry (and consequently the shading) show this.

I filtered the Data Entry Table on Series (4) and Date to view the data that drives this issue. Remove the filter to see all the data.