Table of Contents

  1. Populate cells dynamically in a weekly schedule
  2. Populate multiple cell values in a single cell in a weekly schedule (vba)


Populate cells dynamically in a weekly schedule

In this post we are going add one more function to a weekly schedule. We are going to create an array formula to populate cells with information from a schedule sheet.

Here is a picture of the schedule sheet:

From the above picture we understand:

On the 1 of August from 8:00 AM to 10:00 AM the word "Meeting" will populate two cells on weekly schedule.

On the 1 of August from 1:00 PM to 3:00 PM the word "Design" will populate two cells on weekly schedule.

On the 3 of August from 12:00 PM to 4:00 PM the word "Strategies" will populate four cells on weekly schedule.

Here is a picture of the weekly schedule and the populated cells:

Now let us see what happens if we change the date in cell F2 to 9-Aug-2010.

Cell range C4:I4 have new dates. They are dynamic and change depending on the value in cell F2.

The cells in C6:I29 are all empty now. They are also dynamic and change depending on the dates  on cell range C4:I4. The cells are empty because nothing is scheduled on these dates. See picure of schedule sheet at the top.

Array formula in C4:

=$F$2-WEEKDAY($F$2;1)+1 + Enter

Formula in D4:

=C4+1 + Enter.

Copy cell C5 and paste it into cells E4:I4

Array formula in C6:

=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER.

Copy cell C6 and paste it into cell range C6:I29.

Conditional formatting

I filled populated cells using conditional formatting.

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Download excel template

Populate-time-ranges-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Populate multiple cell values in a single cell in a weekly schedule (vba)

Katerina Georgiadou asks:

What should I change if I have two different which start and end the same date time ? As it is now, only the first one is displayed. How I can divide them in order to see both of them?

Answer:

VBA code

Function Lookup_concat(SearchDate As String, _
StartDate As Range, EndDate As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To StartDate.Count
    If (StartDate(i, 1) * 1) <= SearchDate Then
        If (EndDate(i, 1) * 1) >= SearchDate Then
            result = result & Return_val_col.Cells(i, 1).Value & " | "
        End If
    End If
Next i
result = Left(result, Len(result) - 3)
Lookup_concat = Trim(result)
End Function

Where do I copy/paste vba code?

Press Alt-F11 to open visual basic editor
Select your workbook in project explorer
Click Module on the Insert menu
Copy and paste the above user defined function
Exit visual basic editor

Download excel *.xlsm file

Populate-time-ranges-in-a-weekly-schedule-version2.xlsm

Related posts:

Weekly schedule template in excel

Setting up your work hours in a weekly schedule in excel

Schedule recurring events in a weekly schedule in excel

Highlight specific time ranges in a weekly schedule in excel

Find empty hours in a weekly schedule in excel