Capacity calendar using a lookup and conditional formatting?

I have a simple capacity table which houses contact names down one side and days of the week across the top.

I'd like the cells to go red if that contact is busy on a specific day, or go green if they are free... easy enough.

However, this is an outlook for 2-3 weeks from the current week. The data of which is stored in a separate sheet, not within a set range of cells (these cells will change depending on the increase/decrease of demand).

Is it possible to lookup IF date is 2 or 3 weeks from current week, match with the contact name(s) in the table and display the free/busy output in green/red?

A solution seems possible, but it's difficult to conceptualize what your data looks like. Would it be possible to post a small sample workbook (use: Go Advanced --> Manage Attachments) with all private data altered or removed? This would enable us to see your setup and experiment with solutions.

Glad I could help! As to your questions:

1. When writing a CF formula for a large range of cells, the formula should be written for the top-left cell. Excel then adapts and applies it to the others, similar to doing a fill-right and fill-down. The formula is written with $B7 and C$5 as references because those are the relevant references for cell C7, the top-left cell of our range.

2. The formula:

=TODAY()-WEEKDAY(TODAY(),3)

Returns the date for Monday of the current weekday. WEEKDAY(TODAY(),3) returns a 0 on a Monday, 1 on a Tuesday, 2 on a Wed., etc. We're basically taking today's date and subtracting however many days it's been since Monday. Today()+14 simply bumps things up two weeks (14 days).

3. It's certainly possible, though you can probably avoid unnecessary VBA by using conditional formatting again. Since one booking blocks out a contact for the day, you can use a basic CF rule of:

=COUNTIFS($J$2:$J$100,$J2,$L$2:$L$100,$L2)>1

...applied to J2:L100 (or however far you need it) on your Raw Data sheet. Set the fill to red. The effect will be to highlight a contact and date in red ANY time an entry attempts to double book that contact. This would catch a user who tried to assign a meeting to a contact booked for the week, but it would also raise the alarm if the contact were even booked for just the day in question. Take a look at the attachment to see if it does the trick (I added some fake data to row 32):

Can't thank you enough for your help on this - works brilliantly.

I have one further request off the back of the above - it may be way too complex/unfeasible but worth a shot (even if only some of it is possible):

1) 'select' a row in Raw Data
2) move to capacity sheet
3) double click on a "green" slot within the capacity sheet
4) which then returns you to raw data having filled out the meeting date and contact columns for the row that was selected?

Thanks

This is possible with a few worksheet events. I would recommend being a little more cautious with your step 1, though. It is possible to select a row in "Raw Data", secretly store that information, then change that row based on a double-click on a different sheet, but that seems needlessly vulnerable to user-error. Keep in mind that changes made with VBA events can't be undone with a simple Ctrl + Z. I would recommend making the last selected row in Raw Data clear to whomever might be making the concurrent change on your capacity sheet. In the attachment, I've created a basic selection change even on Raw Data:

Please Login or Register  to view this content.

This will input the last selected row on Raw Data into F1 on the Capacity Calendar so the user can see what row will be changed before double-clicking. From there, I added a double-click event on the Capacity sheet to carry over the selection:

Please Login or Register  to view this content.

This event will carry the date and contact information for the selected box over to the row shown in F1 on Raw Data. It will also select the appropriate row/cell so the user can enter the meeting time. The Capacity Calendar cell will automatically turn red to indicate that it is no longer available. Play around a bit with the attachment to see if it works as desired.


*Both of my efforts above presume that you won't go beyond row 500 on Raw Data. If you need more space, just change the "500"s in each procedure to something bigger.

Thanks CAntosh, this is fantastic

One last request after a discussion with the user today:

Occasionally 2 visits may occur on the same day, by the same contact...
Would it be possible to amend the conditional formatting/vba so that in the Capacity table, if a contact is selected for a visit, the cell turns orange, rather than red... and that this cell can be selected again whilst orange, and only goes red/unable to be selected when the capacity of 2 visits per day, per contact is reached?

Hope this makes sense. I've attached an updated workbook without confidential info, although I will be copying any solutions into my master file. Many thanks.

I think that's doable. I changed the double-click event to the following to allow for a second entry:

Please Login or Register  to view this content.

I then modified my original CF rule to:

=AND(COUNTIFS('Raw Data'!$J$2:$J$85,C$5,'Raw Data'!$L$2:$L$85,$B7)=1,C$5<>"")

...and set the fill to orange. This should turn a single entry to orange on the capacity calendar. I then made a nearly identical rule with red fill for the cells with two corresponding entries:

=AND(COUNTIFS('Raw Data'!$J$2:$J$85,C$5,'Raw Data'!$L$2:$L$85,$B7)>1,C$5<>"")

I wasn't sure what changes you wanted to the Raw Data CF rules, so I did basically the same. If a contact has exactly two entries, the relevant cells will turn orange:

=COUNTIFS($J$2:$J$90,$J2,$L$2:$L$90,$L2)=2

If someone tries to enter a third matching date/contact booking on Raw Data (by hand, since the double-click calendar event won't allow it), the cells will turn red:

=COUNTIFS($J$2:$J$90,$J2,$L$2:$L$90,$L2)>2

Experiment a bit with the attachment, see if I've got it...