Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous article, an array formula allows you to populate cells with information from a schedule sheet.
The first part in this article demonstrates an array formula that contains the TEXTJOIN function, the second part in this post shows you how to show multiple events in a cell with the help of some vba code in case your Excel version doesn't contain the TEXTJOIN function.
You can easily adjust the height of the cells if you want to populate more than 2 events in a single cell.
Table of Contents
- Populate cells dynamically in a weekly schedule
- Populate multiple cell values in a single cell in a weekly schedule (vba)
Here is a picture of the schedule sheet:
From the above picture we see that:
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 9:00 AM to 10:00 AM the word "Procurement" will populate one cell on weekly schedule, shared with "Meeting"
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.
Now let us see what happens if we change the date in cell F2 to 9-Aug-2010.
Formula in C4:
Explaining formula in cell C4
Step 1 - Return weekday number
The WEEKDAY function converts a date to a weekday number from 1 to 7 based on when week starts.
WEEKDAY($F$2,1)
becomes
WEEKDAY(40391,1)
and returns 1.
Step 2 - Calculate first date in week
$F$2-WEEKDAY($F$2,1)+1
becomes
40391-1+1
and returns 40391.
If your week starts on a Monday then change the formula to $F$2-WEEKDAY($F$2,1)+2
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.
Explaining formula in cell C6
The array formula uses cell references that points
Step 1 - Identify events based on date and time
Cell reference C$4 is locked to row 4, the column reference changes only when the cell is copied to another column, this makes sure that the formula only gets values from row 4 (dates).
Cell reference $B6 is locked to column B, the row reference changes only when the cell is copied to another row, this makes sure that the formula only gets values from column B (time value).
The formula checks if the current cell is inside one or more event ranges.
(C$4+$B6)>=Table1[Start time])*((C$4+$B6)<Table1[End time])
becomes
(40391)>=Table1[Start time])*((40391)<Table1[End time])
becomes
(40391)>={40391.3333333333; 40391.5416666667; 40393.5; 40391.375})*((40391)<{40391.4166666667; 40391.625; 40393.6666666667; 40391.4166666667})
and returns
{0;0;0;0}. This means that 8/1/2010 12:00 AM has no events scheduled.
Step 2 - Convert array to event name if equal to 1
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE (1) then one thing happens (argument 2) and if FALSE (0) another thing happens (argument 3).
IF(((C$4+$B6)>=Table1[Start time])*((C$4+$B6)<Table1[End time]),Table1[Title],"")
becomes
IF({0;0;0;0},Table1[Title],"")
becomes
IF({0;0;0;0},{"Meeting";"Design";"Strategies";"Procurement"},"")
and returns
{"";"";"";""}.
Step 3 - Concatenate values
The TEXTJOIN function uses CHAR(10) as a delimiting character, this will display each event name on a new row in one cell.
TEXTJOIN(CHAR(10),TRUE,IF(((C$4+$B6)>=Table1[Start time])*((C$4+$B6)<Table1[End time]),Table1[Title],""))
becomes
TEXTJOIN(CHAR(10),TRUE,{"";"";"";""})
and returns "" (nothing) in cell C6.
Conditional formatting
I highlighted populated cells using a conditional formatting formula:
Populate multiple cell values in a single cell in a weekly schedule (vba)
Katerina Georgiadou asks:
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
- Press with left mouse button on Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
Schedule category
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]
Excel categories
47 Responses to “Populate cells dynamically in a weekly schedule”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hello Oscar,
I think this is a great schedule template! Thank you! I have been playing around with it and here is my question. On the schedule sheet I want to be able to continue adding different titles and times i.e. continuing the list. How can I do that (bit of an excel novice here) so that it continues to populate as the first three lines do?
Best,
John
John,
Let's say you wish to add 3 more titles, so your range becomes from row3 to row8:
COMPLICATED WAY:
Replace formula as follows:
=IFERROR(INDEX(Schedule!$B$3:$B$8,SMALL(IF(((C$4+$B6)>=Schedule!$C$3:$C$8)*((C$4+$B6)=Start)*((C$4+$B6)<End), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER. or COMMAND + RETURN on a mac.
and just modify the named ranges (INSERT/NAME/DEFINE) then change the ranges from row 5 to row 8 for each of the names (End, Start and Title).
Cheers.
Cyril.
Hi Oscar (Create-a-weekly-schedule)
I am trying to use the weekly planner where it has to summarise in the Monthly plans in excel. Some what i received templates from various sources. But not exactly.
When i select first week i should be able to enter the details(description) of it and for 2nd, 3rd, 4th week the dates should change at the same time the data which i updated for the first week should not appear there. so that i can plan/update for every week.
In the Monthly sheet when i select particular month then the summary of all 4 weeks should appear there.
Thanks you
Babu
weird my reply got mixed up:
=IFERROR(INDEX(Schedule!B3:B8,SMALL(IF(((C$4+$B6)>=Schedule!C3:C8)*((C$4+$B6)<Schedule!D3:D8),ROW(Schedule!C3:C8)-MIN(ROW(Schedule!C3:C8))+1,""),1)),"") should be the complicated formula changes
the easiest is just to change the ranged names and modifying the range for each category from 5 to 8 assuming you wish to add 3 more titles (from row 3 to 8 therefore)
Still wondering why my reply got truncated and mixed up...
Cyril
Cheers Cyril!! I took the easy route and modified the ranges!
Best,
John
Cyril,
Thanks for commenting! WordPress removes html characters https://codex.wordpress.org/Writing_Code_in_Your_Posts
John,
You could create dynamic named ranges:
https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/
Hey.
I'm testing this function but putting the calendar transmuted, put the dates vertically and horizontally put the hours. I do not think it works and the matrix. Do not know how to send an example.
Thanks, I learn to like it a lot to your page.
(translated with google)
Hi,
I have a question, please help me! 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?
Katerina Georgiadou,
you can´t unless you use vba:
Excel udf: Lookup and return multiple values concatenated into one cell
Katerina Georgiadou,
See attached file:
Populate-time-ranges-in-a-weekly-schedule-version2.xlsm
I teach and would like to show the schedule for a 3 month period. For instance, I teach CLASS 1 Every Monday and Wednesday at 1000 from 1 April 2013 until 14 April 2013. I teach CLASS 2 Every Monday and Wednesday at 1400 from 1 April 2013 until 14 April 2013. I teach CLASS 3 Every Tuesday and Thursday at 1000 from 1 April 2013 until 14 April 2013
Hi,
I have a year maintenance schedule. There are some reccuring rule, 1 month, 3 month and so on. I want to automatically populate my schedule into weekly term. can anyone help me for the formula expression in excel,
Zul,
Can you explain in greater detail?
Hi OScar is it ok to send you a sample file I need looking at, it is a course schedule calendar that uses spin boxes to see each time slot occupied, I ma having trouble designating the time slots.
I implemented your instructions above and it has solved a series of problems for me, but also created some new ones.
Yours sincerely
John Dalton
Hi I have used the VBA to bring multiple values into one cell is great and works very well. However what do I need to do to have the multiple values to be separated by a new row in the cell rather than by the "|"? I'm a novice at using VBA's but managed to copy across the VBA script, but do not know how to change it to do what I'm looking for. Any help available would be much appreciated.
The spreadsheet also seems to be limited by the three data entry rows in the sample spreadsheet. Is there a way to dynamically change the range based on rows with values?
Per-Olof,
I need to do to have the multiple values to be separated by a new row in the cell rather than by the "|"?
The spreadsheet also seems to be limited by the three data entry rows in the sample spreadsheet. Is there a way to dynamically change the range based on rows with values?
This custom function allows you to enter multiple ranges. It also also separates values with a new row. Remember to format cells and "Wrap text"
Function Lookup_concat(SearchDate As String, _ StartDate As Range, EndDate As Range, ParamArray Return_val_col() As Variant) 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 For Each Rng In Return_val_col result = result & Rng.Cells(i) & Chr(10) Next Rng End If End If Next i result = Left(result, Len(result) - 1) Lookup_concat = Trim(result) End FunctionGet the Excel file
Populate-time-ranges-in-a-weekly-schedule-version3.xlsm
Thanks this is brilliant!
I've been playing around with it now and the macro works really great.
Regards,
Per-Olof
This is brilliant. is it possible to use with one date rage rather than start & end and multiple titles on different calendar rows. i did attempt an update to the UDF must unsuccessful.
ie: i have one date multiple times with different titles. A calendar box for one date that has 5 rows, so when there are multiple titles i do a offset+1 row.
Hope you can assist.
Much appreciated. Tammy
Hi
As a excel novice I have been working through an issue trying to use the above. I am trying to populate a calendar for the year with service intervals that change based on run hours. The below formula kind of works but not if the service starts and ends on the same day e.g. start 23rd ends 23rd.
=IFERROR(INDEX(Schedule!$B$3:$B$7, SMALL(IF((C$4>=Schedule!$C$3:$C$7)*(C$4=Schedule!$D$3:$D$7), ROW(Schedule!$C$3:$C$7)-MIN(ROW(Schedule!$C$3:$C$7))+1, ""), 1)), "")
I am trying to get the dates to populate with the service interval description e.g 1500, over the dates it would last. Example start and end dates are shown below.
Service Start date End Date
1500 23 Apr 15 23 Apr 15
48000 22 Sept 15 27 Sept 15
Any help would be greatly appreciated as I have reached my limit (a while back).
Thanks
Thanks for the help.
If I wanted to make this a schedule that lasted a week and instead of times it was more focused on dates. Would that work?
What I mean, I have someone scheduled to work from June 3 - June 7. and I want that to populate a schedule. It would have there job role displayed on each day(like how on this one is says meeting). What would I need to change?
Thanks
Hi,
This is brilliant, thanks!
I was wondering whether it's possible to postulate multiple cells values, but in different columns? e.g. using the same answer you gave to Katerina Georgiadou, but instead of the result coming in the same column with a separator, it comes in a different column?
Thanks
Need some help please. I need the calendar and schedule on the same sheet and start times but no end times with multiple event entries to the calendar. I'll need to drop these onto existing worksheets for individual clients. When I try to alter the formulas to adapt them to each sheet I get all kinds of hung up. Help????
Hi David
When I try to alter the formulas to adapt them to each sheet I get all kinds of hung up.
Can you provide the sheet names?
Hello, this article is fantastic and it has helped me tremendously, thank you! I am trying to adapt this to use in a schedule that shows daily activity for multiple trainers at my company by month (Dates down column A, Trainer Names across Row 3). The source data I have is a list of dates (Start and End), with the name of the trainer and what they are scheduled to do along side each in separate columns.
I have tried changing the formula to incorporate an AND statement so that it will only populate the cell if the dates match up and if the Trainer also matches, but I cannot get it to pull results. How would I go about doing this?
This is the adapted formula that isn't working (Full_Detail is the activity I need it to show): =IFERROR(INDEX(Full_Detail, SMALL(IF(AND(((A4)>=Start_Date)*((A4)<End_Date),(Trainer)=$O$3), ROW(Start_Date)-MIN(ROW(Start_Date))+1, ""), 1)), "")
Any help is greatly appreciated, thank you.
Jon G
I think you need to use relative and absolute cell references combined.
This is the formula you need:
This formula changes cell refs as you copy and paste it to new cells.
You can read more here:
https://www.get-digital-help.com/2010/06/10/absolute-and-relative-references-in-excel/
Hi Oscar, thanks ever so much for getting back to me on this. I still can't seem to get it working with your suggestions though. Am I doing something wrong?
This is the source data (linked to a SharePoint 2010 list): https://s22.postimg.org/xfxrky07l/Source_Data.png.
This is the schedule I am trying to populate using the formula: https://s13.postimg.org/u1sgdgkjr/Destination_Data.png.
I need the Schedule to populate with the correct information (Full_Detail) depending on Trainer and Start_Date. Staff Member 13 is the one I have been trying to get working but all the cells still appear empty. I feel I am close on this but not quite there, are you able to help please?
Jon G
I have been trying to get working but all the cells still appear empty.
If you use "Evaluate formula" on tab "Formulas" on your cell, which part of the formula results in an error?
Hi Oscar, this is great!!! you are a genius, How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe show a monthly view? Times are less important than just showing what is due on what day. I would be very interested in something like that. Please contact me directly if needed.
Tesh,
I made a calendar (monthly view) for you.
https://www.get-digital-help.com/2016/11/10/calendar-monthly-view/
[…] Tesh asks: […]
Mr. Oscar,
Sir your obviously a talented and generous guy. I am hoping you have a solution in my case. I have need to create a rotation schedule. Some of the code above looks like it might work but I'm having trouble getting started assembling it. I want to create a list with names for a crew of up to 200 guys in rows in column A, a start date in column B, and end Date in column C, a schedule ON shift in days in column D, and a schedule OFF time in days in Column F then a group of cells in columns to the right beginning from a sheets rotation schedule reference date that is manually updated for a given day in the year. Of course all of the start and end dates would be near and should be after the rotation schedule reference date by design so they would show up on the schedule. I want enough cells to the right to populate for a years worth of days from the rotation schedule reference date. I want to be able to take all of the cells between the start and end date and shade them in each row then when the last cell or end date cell is complete I want to use the column F schedule OFF time and skip that many days and then begin shading the next cell for the number of ON days from column D and then keep going until those days are complete and then begin the schedule OFF again until done then repeat ON and OFF then ON and OFF until complete to the end of the years worth of cells. Does this make sense? I am taking a persons first schedule which might not be an exact match of ON days and then projecting what it would be once ended based on a number of days cycle entered. Probably a drop list maybe. For example 28 days ON and 14 days OFF is most typical in my environment. It doesn't appear to be too complicated except I can't wrap my head around how to do it but I have trouble with simple things. I was not sure if could be done without VBA which I'm not savy with at all. I can use a lot of formulas but nesting all the functional requirements to satisfy this has me unable to break it down. I would greatly appreciate your help. Grateful in Advance, Keith
Is it possible to have this calendar not read a specific date but read something along the lines of 4th Wednesday, or 3rd Tuesday?
Also trying to get it populate more information than just the meeting name - such as location.
Thank you!
Hello, thanks for this! I have data that I have pulled from Outlook. I have the date, start time, end time, and end date each in it's own field. I noticed you have your date and time combined into one field. If I want to populate the calendar (mine is in increments of 15 minutes, how would I format the formula to look for each piece of criteria (in its own cell) and populate properly on the calendar. I have created a calendar that is a revolving 91 day calendar beginning with =TODAY()-7. I want the user to be able to look one week backward if need be.
Kathy,
I think you are looking for this:
D_Start : Start Date
T_Start : Start Time
D_End : End Date
T_End : End Time
Hi Oscar,
I love your work here, but I was wondering if it could be duplicated for Google Sheets. It looks like Google Sheets does not recognize "lookup_concat" function.
The formula I used was:
=IFERROR(VLOOKUP(C$3&B5,Schedule!$A$4:$F$100,6,0),"")
I'm trying to create a better way to do a VLOOKUP function and populate calendar events to a dynamic weekly schedule. If you could help, I would be super grateful!
The problem I'm struggling with is to populate multiple cells in a column based on the start and end time of an event. For example, if I have a meeting starting at 7:00 AM and ending at 9:00 AM, I should see a span of cells moving from 7:00 AM to 9:00 AM with the cell populating with "Meeting".
Bonus problem: Also, have you figured out a way to assign a time value to an event or task? I want to create a snowball effect that all task cells will be assigned a time value and will populate empty cells on the calendar, based on priority. If you have any thoughts on this, I would be grateful!
Here's my spreadsheet: https://docs.google.com/spreadsheets/d/1agI2hbyoEEQKTXGpOTHE812Lp9nKMv-ayzyi7WuFdrs/edit?usp=sharing
Let me know your thoughts. Thanks!
Brad
Hi Brad
It looks like Google Sheets does not recognize "lookup_concat" function.
Yes, it is a custom Excel Function I made. It won't work in Google Spreadsheets.
I'm trying to create a better way to do a VLOOKUP function and populate calendar events to a dynamic weekly schedule
I am using this formula in Excel to populate cells in the calendar:
=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "")
The named ranges Title, Start and End are cell references to the events.
If you convert those to cell references I believe the formula should work in Google Spreadsheets.
Thanks for commenting.
To update what I am looking to do from my last post, this is exactly what I am looking to do but need to show a few more columns from the schedule on the monthly view. I am not able to figure out how to add more columns to view. Can you advise how I can do that?
can you help me with Populate cells dynamically in a weekly schedule, what if its just time nothing else just time when the time is entered on a specific cell it would highlight the cells that it covers.. is that possible
Hello, Thank you for this tool. How do I fix the problem of an event that ends at 10:00 shows on the schedule as occurring at 10:00?
Amanda,
thank you for telling me, that is an error that I made.
I have uploaded a new file and changed the VBA macro shown in the article.
May I know why once I change the date in Cell F2, all the other cells in the schedule become "#NAME?" ? Thanks Thank you
Kube,
May I know why once I change the date in Cell F2, all the other cells in the schedule become "#NAME?" ? Thanks Thank you
Is macros disabled? Which workbook did you try out?
Used this to create a meeting schedule table and works fine with my Excel 2019 but my beta testers were running lower versions. can you recommend a fix for say like index-match, if array as an alternative to textjoin function?
=TEXTJOIN(CHAR(10),TRUE,IF((($D$2+M$5)>=Main1[Start_1])*(($D$2+M$5)<=Main1[End_1])*($C$12=Main1[Room])*($D13=Main1[Title]),1,""))
You are amazing!
Is there a way to adapt this formula?
I have a list of 400-800 classes, each with day, time, and teacher name.
I have an individual sheet for each of my teachers (100 teachers)
I would like each individual teacher sheet to sort through the whole class list, and only populate the classes for the teacher.
I feel like this formula just needs an extra "if the sheet name (teacher name) is the same as the teacher name on the class list, then populate"
Is there anyway to do this?
Your help would be so appreciated.
I'm currently doing a Meeting room scheduler that you might want to look into as a model. The teacher name can be one of the variables of your database and use the small formula to extract your schedule. I can send you my working file for reference.
hello how can I add 3 more weeks onto this so I have the whole month infront of me?
Thankyou
Hi Oscar,
Is it possible to only populate the cells that fit within the time of the start and end date? I have a meeting that will occur M-F from 8am-12pm. When the dates and time are inputted all time slots in between 8am on Monday and 12pm on Friday are populated (I.E. all time slots of Tuesday are highlighted). Is it possible to only populate 8am-12pm for M-F without having to input each meeting separately? I appreciate any help.