Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
Three week ago I visited the Newton Excel Bach web site where I spotted the Dynamically Defined Dancing Pendulums NewtonExcelBach.
Having noticed that Doug had done a nice animation in Strand7 (a Finite Element Analysis program) Strand7, I thought “I can do that in Excel” and so I did.
This post will not go through the logic of constructing and animating the pendulums in Excel as I have described that over at Excelhero.com and readers who are interested are encouraged to visit there and explore the techniques used.
During the Pendulum project I came across two sub-projects which I felt are worthy of a post here at Chandoo.org as they are a great demonstration of some basic VBA techniques as well as demonstrating the ability of VBA to quickly simplify repetitive tasks.
Sample files are attached for Excel 97-2003 and Excel 2007/10 users to follow through the examples.
PENDULUM SIDE PROJECTS
The Pendulum project consists of 16 Pendulums. Each Pendulum requires 4 Named Formulas, meaning that the projects needs 64 Named Formulas for the 16 Pendulum, as well as adding 16 Series to the chart.
(The above animated GIF is a very poor representation of the smooth scrolling achieved in the Excel animation)
I thought it would be a great idea to see if these jobs could be automated and hopefully save some time during the process.
The result was 2 simple VBA routines which will be described below:
NAMED FORMULAS
Each Pendulum in the project was based around 4 Named Formulas
Named Formula for each Pendulum
p1Len =’1′!$B$9 The Length is stored on the worksheet.
p1o =OMax*SIN(SQRT(g/p1Len)*t) Current angular position of Pendulum 1 at time t
p1x =p1Len*SIN(p1o)*{0;1} Current orthogonal X position of Pendulum 1 at time t
p1y =-p1Len*COS(p1o)*{0;1} Current orthogonal Y position of Pendulum 1 at time t
The only difference between the formulas for Pendulum 1 and Pendulum 2 etc is the replacement of the names of p1 with p2 in the various Named Formulas and of the associated formulas.
On a worksheet Named Formulas a number of formulas were written which display the Named Formulas as required above. Then a small VBA routine was written which loads the Named Formulas.
How
On the Named Formulas worksheet, I have added two columns of formulas for the various Named Formulas required.
For the Pendulum Length Named Formulas
For the Pendulum Angular Position Named Formulas
For the Pendulum X Position Named Formulas
For the Pendulum Y Position Named Formulas
When the formula above are copied down they adjust for the various pendulums numbered 1 to 16 based on the row numbers.
I then setup a VBA routine, Load_Named_Ranges, shown below which loads the Named Formulas.
To Use
Select some or all of the required Named Formulas from the Name Column. That is the code will only load the Selected Named Formulas, allowing the user to load 1 or 2 Named Formulas, for testing purposes, or all the Named Formulas if you choose.
Then Execute the Load_Named_Ranges subroutine either using the Big Red Button or directly within the VBA Editor.
The following will load Named Formulas p3Len to p7Len.
The Load_Named_Ranges subroutine is shown below:
Sub Load_Named_Ranges() Dim c As Range For Each c In Selection ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text Next End SubWhat does the code do?
The code:
1. Defines the start and name of the subroutine,
Sub Load_Named_Ranges()
2. Defines a variable c as a Range object,
Dim c As Range
3. It then loops through each cell in the selection and assigns it to the variable ‘c’;
For Each c In Selection
4. It then adds a new Named Formula,extracting the Name from the Text Value of ‘c’ and extracts the formula from the cell directly to the right of cell ‘c’;
ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text
The Name and Formula (RefersTo) both use the Text of the cell, which is what is displayed.
5. It then loops through each cell in the selection until it has done them all;
Next
6. Defines the end of the subroutine;
End Sub
Lets Test It
To test the subroutine we will first delete all the Named Formulas beginning with “p”
Goto the Formula Ribbon Bar and select Name Manager
Select all the Named Formulas that begin with “P” and press the delete button
Accept any warnings
Try and Run the Pendulum’s
Nothing happens as there are no formulas
Ensure the Pendulum are turned off, as the code is still running behind the scenes.
Now Goto the Named Formulas Page
Select all the Named Formula Names in Name Column; B3:B66
Click the Load Named Formulas, button
Go back to Page 1 and try and run the Pendulums now.
ADD CHART SERIES
The second sub-project was the addition of 16 Chart series to the Chart, 1 for each Pendulum.
Using the logic of the Named Formulas VBA code above, the 16 Chart Series Names, X Values and Y values were developed using formulas on the Add Cht Series worksheet and then loading into a chart using a simple VBA routine.
The Add_Cht_Series subroutine is in the Add Cht Series sheet object in the VBA editor.
How
On the Add Cht Series worksheet, I have added three columns of formulas for the various Named Formulas required.
For the Pendulum Name, X Range and Y Range.
When these formulas are copied down they adjust for the various pendulums numbered 1 to 16.
I have then setup a VBA routine, Add_Chart_Series, shown below which loads the Named Formulas.
To use Select some or all of the required Chart Series from the Pendulum Name column.
Then Execute the Add_Chart_Series subroutine using the big red button.
The Add_Chart_Series subroutine is shown below:
Sub Add_Cht_Series() Dim sNumb As Integer Dim c As Range Worksheets(“1”).ChartObjects(“Chart 5”).Activate For Each c In Worksheets(“Add Cht Series”).Range(“B19:b20”) sNumb = ActiveChart.SeriesCollection.Count + 1 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(sNumb).Name = c.Text ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text Next End Sub
What does the code do?
The code:
1. Defines the start and name of the subroutine,
Sub Add_Cht_Series()
2. Defines a variable sNumb as an integer, and a variable c as a Range object
Dim sNumb As Integer Dim c As Range3. It then activates the Chart containing the pendulum
Worksheets(“1”).ChartObjects(“Chart 5”).Activate
4. It then loops through each cell in the Range defined by the Range, in this case B19:B20 and assigns it to the variable ‘c’; You can adjust the Range to suit.
For Each c In Worksheets(“Add Cht Series”).Range(“B19:B20”)
5. It then counts how many existing series are in the chart and sets the next Series Number sNumb to that value + 1.
sNumb = ActiveChart.SeriesCollection.Count + 1
6. The next 4 lines add a new series to the chart and setup the new series Name, X Value and Y Values. The Name, X Value and Y Values are retrieved from the Text of the cell c and the adjacent two cells using a Range Offset modifier
ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(sNumb).Name = c.Text ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text7. It then loops through each cell in the selection until it has done them all;
Next
8. Define the end of the subroutine;
End Sub
Lets Test It
To test the subroutine we will first delete a few of the Chart Series
Select the Chart
Select two Pendulums noting the series Number of the Bobs eg: 9 & 10
Goto the Add Chart Series Worksheet
Note the Range Corresponding to the 2 missing Pendulum B11:B12
Goto the VBA Editor
Adjust the Line
For Each c In Worksheets(“Add Cht Series”).Range(“B11:B12”)
With your cursor in the Subroutine press F5 once only
Go back to Page 1 and you should now have 2 New Pendulum
Run the Pendulums now.
You will have to manually set the shape of the Bobs to a Circle and size 15 and re-arrange the order of the series to ensure they are in order, but you can practice that manually.
SUMMARY
The post has shown how using some very simple VBA and a bit of lateral thinking to put together some simple tools to simplify 2 common and repetitive tasks.
In the Named Formulas case, the code took less than 2 minutes for me to write and then another 5 minutes to do the formulas for the Named Formulas. I didn’t try but I am sure it would have taken a good 20+ minutes to enter 64 Named Formulas.
Writing this post took much longer than doing the whole Pendulum Project.
Two examples during my working career, where VBA code has been used to save massive amounts of time and money:
In the first case I wrote some code to combine data from several hundred workbooks with varying numbers of sheets up to 30 and differing quantities of data on each sheet, a task that could have taken weeks manually with the included opportunity for errors to be introduced, into a subroutine which took 30 minutes to run and gave a printout of the results including what files, sheets and rows of data were included in the import.
In a second case a Number of Workbooks, a Word template and some VBA code was used to replace a person whose sole job was to manage that data. This job saved the company $50k+ per annum and the task was given to a clerical person who could now do the task in their spare time.
LINKS
Huis Excel Hero Pendulum: http://www.excelhero.com/blog/
Pendulum Physics: http://hyperphysics.phy-astr.gsu.edu/hbase/pend.html
Newton Excel Bach: http://newtonexcelbach.wordpress.com/2011/05/25/dancing-pendulums-2/
What could your simplify by using automation within Excel ?
What could you simplify or speedup using Excel automation?
Let us know in the comments below:
25 Responses to “Shift Calendar Template – FREE Download”
Hi Chandoo,
your recent postings include only Excel 2007 templates. Unfortunately the company I work at still runs Excel 2003. Is it possible to get your awesome files in other excel version as well?
Thanks so much for your great excel stuff!
Is it possible to do this for shifts with hours instead of days? To organise a three shift day?
Thanks in advance,
Stelios
In my organization there are 45 employees i need split then into three shifts ex:A shift:14,B shift:14,C shift:14 and week off:3 kindly help me on this.
@Masthan
You need to understand what rules your company has for the various shifts / roster combinations
Chandoo, I once did a shift control spreadsheet for my team. I put one person in each line, the columns were the days. I put a shift code in each cell indicating in which shift that person should work, or if the person were out that day. I have two codes for being out. One is for vacations and one is to compensate days worked in weekends. This way I was able to count how many persons I have in each shift, how many were on vacations and how many were out compensating (that's the term we use here) weekend worked hours.
Later I included the possibility of a person be in two lines one for normal hours other for overtime. This is mainly used for planning purposes. If you would like I can send you an example. The only problem of this spreadsheet is that we don't have a person view, only this consolidated view.
Hi George, I would like to have a copy of your spreadsheet if you can share it.
Thanks in advance, Chuck
Hi Chandoo,
Where is the code located ? is it VBA ? If so , how do you hide it ? Or it is .NET ?
Thx
@Idan
.
No VBA or code, it is all done with Mirrors.
Only Joking,
.
But there is no VBA or code,
It is all done with Named Formulas and Lookups.
Have alook at the cells in the calander area and Named Formulas in the Formulas, Name Manager Tab.
How can i calculate between two or more different workbooks? Please, reply me as early as possible.
@Anand
Open the workbooks you want to link to
Start a formula = and click and change between workbooks as required.
You can use the View, Switch window menu to change workbooks mid formula
The format for using workbooks is
=[Workbook.xlsm]Sheet1!$A$1
or
=SUM('[Book2.xls]Sheet1'!$A$1:$D$10)
etc
Hi Chandoo,
I am working with a call centre wherein i ned to update at the month end 20 to 30 employees login hours which are defict to track it at the month end is very difficult is there any template which can be made to track that why on a particular day a guy who needs to be on calls was why not on calls.
Thank you so much Chandoo. This is really helping me. As usual, you rock.
What's FortyTwoDays and Calendar in Name manager?
Both are unused and FortyTwoDays doesn't make any sense.
I have a SQL db that contains records of events scheduled/completed on a particular date. Can this method ous building a calendar be used to display those events on the respective day?
Positively awesome!
I'm attempting to help a friend create a schedule for adult classes - and of course its not"paid help". Here is the scenario:
20 classes, instructor, room#, student class size, start date, number of class days (need to subtract weekends)
class
instructor
room
students
start
#days
PATH
karen
201
21
01/01/13
11
BILLING
jane
401
15
01/12/13
13
MEDISOFT
mike
301
11
01/25/13
9
he'd like to see these classes show up in different colors within the same month's calendar chart. He can draw it, but I'd like to see it done automatically through data, and I just can't visualize it, but I KNOW this will work - can you help?
Jan 🙂
Dear chandoo,
Try many way to download still can't access. Any way we want to try out 3 shifts with 3 guys in a group .eg Group A Morn, Group B Night and Group C Rest. And every each group must work on sunday to take turns. In fact we are security teams so that's why sunday is required to work. Pls guide and show how to put in the working calendar. Thank you in advance.
I've been trying to copy and/or recreate this to use in a workbook I'm doing for the transportation department I'm working for. I need to have the calendar on the first sheet in my document (it has graph's from data on another sheet). I'm trying to use it to track (with the conditional formatting) accidents and injuries. I've redone the conditional formatting to do 4 different accident types (no injury, near miss, OSHA recordable injury and work loss injury), but when I enter the formula's you have in the calendar portion where it says "DateOfFirst-FirstWeekDay" I can't figure out how you did that. Are you able to help?
I would like to use Excel to solve the following problem for a community work. I want to create a Driver schedule for a given month from a pool of volunteers for a community service. Each of these volunteers can drive only on specific days in a week. I would like to populate the driving schedule for each weekday with primary, secondary and tertiary drivers in a random fashion so that I do not overburden one person. I would greatly any help you can provide.
Hi chandoo,
Thanks for your valuable effort for create this template and let me know how to add multiple employees in the the Roaster.
Hi Chandoo,
This article on shift roaster is very helpful. Could you please let me know how i can use the same for n number of resources who work 24/7, considering their leaves and holidays?
Thanks,
Savitha
Hi Chandoo,
This article on shift roaster is very helpful to all. Could you please let me know how i can use the same if I want to add for some more shifts, since the color is not getting change if I add more shifts like 4,5 etc.,
Thanks,
Murali
nice post
How can I change the date to 2017 under Shift Data worksheet.
solution 1:
mydata=B2:C16
stoplist=E2:E8
=LET(RNG,A2:A16,SMR,C2:C16, F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),SUM(SMR)-SUM(SMR*F))
=LET(RNG,A2:A16,SMR,C2:C16,RH,N(B2:B16=B2), F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),TOT,SUM(SMR)-SUM(SMR*RH*F),SUM(SMR*RH)-SUM(SMR* RH*F))
ALTERNATE SOLUTION
=SUM(C2:C16)-SUM(FILTER(C2:C16,ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))))
=SUM((B2:B16=B2)*(C2:C16))-SUM((ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))*(B2:B16=B2)*(C2:C16)))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,null,";",Replacer.ReplaceValue,{"Column1"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Added Prefix" = Table.TransformColumns(#"Split Column by Delimiter", {{"Merged", each "|" & _, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Added Prefix","||","|",Replacer.ReplaceText,{"Merged"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Merged.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"