Table of Contents
- Populate cells dynamically in a weekly schedule
- Populate multiple cell values in a single cell in a weekly schedule (vba)
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:
Formula in D4:
Copy cell C5 and paste it into cells E4:I4
Array formula in C6:
Copy cell C6 and paste it into cell range C6:I29.
I filled populated cells using conditional formatting.
Download excel template
(Excel 2007 Workbook *.xlsx)
Functions in this article:
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
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
Returns a number from 1 to 7 identifing the day of the week of a date
Katerina Georgiadou asks:
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