## Populate cells dynamically in a weekly schedule in excel

**Table of Contents**

- Populate cells dynamically in a weekly schedule
- 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:**

**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.

**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:

**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**

### 30 Responses to “Populate cells dynamically in a weekly schedule in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.

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 http://codex.wordpress.org/Writing_Code_in_Your_Posts

John,

You could create dynamic named ranges:

http://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"

Download example 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:

http://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.

http://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