Apparently the sheet didn’t allow the patterns of leave, etc to be easily viewed across the year.   This was due to having the date as the header and also having to have sufficient space for staff to enter their leave info.     Seemed simple enough to solve just by adding a second sheet with colour coding, referencing the first sheet.   This would allow for each column to be thinner thereby allowing for a larger date range to be viewable.   Job done?

Apparently not, the next addition to the spec was the need to be able to pick out a week and view just that week.   Now this was going to put my Excel skills, and my google searching skills to the test.

The answer lay in a couple of formula which I hadn’t used before:

Indirect

This formula allows me to retrieve the contents of another cell based on a cell reference.

=INDIRECT(Sheet1&P7)

The above retrieves the contents of a cell from Sheet 1 of my workbook, with cell P7 containing a reference.  E.g. P7 might contain !A1 leading to INDIRECT(Sheet!A1) would would therefore retrieve the contents of cell A1 on Sheet 1.

So using this I would make my View Week sheet retrieve the appropriate weeks data from the Master sheet by basically building the appropriate cell reference.   So if Monday of Week one was in column B, we know week two would be 7 letters further on.

Substitute

For the above to work I need to be able to work out the row letter based on a week number.    The Substitute function allows me to convert a number to an equivalent letter.

=SUBSTITUTE(ADDRESS(1,($E$2-1)*7+1,4),”1″,””)

The above takes a week number in cell E2 and from this works out which column the Monday for that week can be found in.   Note: the above dosents work for Week1 as part of the week was in 2018 rather than 2019.  This could be easily fixed.

Text

The final function I needed to use was the Text function.   Reason I needed this is I wanted to create a pull down list showing the week number and week commencing date.   The issue is I had both pieces of data in different fields and concatenating a date doesn’t work; it shows you the number equivalent of the date.      The Text function allowed me to convert the date into a string which could then be included in a concatenate function.

TEXT(B6,”dd-mm-yyyy”)

The above takes the date value in cell B6 and converts to a string of the date.   The above was then placed inside a concatenate function to combine with the week number as below:

=CONCATENATE(“Wk”,A6, ” – “, TEXT(B6,”dd-mm-yyyy”))

The above takes the week number in cell A6 and the date in B6 and combines together ready for display in a pull down list.

I must admit it took a little bit of thinking and a little but of work to get this spreadsheet working however I did enjoy trying to hack together a solution to this problem.     You can access the final calendar here.  I have unlocked all sheets for your editing, plus have made the working cells visible where in the final version I set the font colour as white to make it invisible.

Consider Facebook, I would suggest that new parents starting using Facebook some years ago failed to fully understand the implications of posting every milestone of the children to the world.      Recent articles from the BBC and The Guardian seem to confirm this.

An article shared by a colleague got me to take a different perspective on things.    Considering the Facebook issue my initial thinking had put the error on the end users.   These end users had started using the site without understanding the long term implications.    Looking at google, my discussion with teachers focused on the teacher and their students considering the implications as end users.    But what if the blame, if blame might exist, falls somewhere else?

In an article in The Business Insider it is suggested that programmers need to receive ethical training.   It is the programmers which make the sites and services and define the specific functionality and operation.      If programmers at Facebook had considered the ethics of posting and sharing of an individuals life maybe the security and privacy options would have been more mature at the outset or maybe some warnings may have been displayed in relation to posting photos of your children.

Maybe a better illustration of the issue can be found looking at autonomous cars, which as we know, Google and a number of other companies are working on developing.     Lets assume an autonomous car gets into an accident resulting in damage to someone else’s vehicle and to injury.   Who is at fault?      Would it be the owner of the vehicle who may not even have been in the vehicle?   Would it be the passenger in the vehicle despite the fact they aren’t driving; it is an autonomous car.    Would it be the manufacturer of the car?     Or might it be the programmer who wrote the subsystem which failed to avoid the crash?

In future I will be more aware of the limits of a one sided viewpoint focused on the users as the decision makers; either using the service appropriately, ethically and morally or not.   The fact that a system could be used in an inappropriate or unethical way may indicate a failure of the programmers to appreciate the implications of their code, either now or in the future, or worse that the capability was programmed in, in the first place.

I also wonder about, whether with all the focus on coding in our schools, we also need to spend at least some time discussing the ethical issues surrounding programming.