Was asked the other day to create a spreadsheet for staff to record the leave on. Sounded simple but as with most IT project scope creep made an appearance and the simple specification became a bit more complex.
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:
This formula allows me to retrieve the contents of another cell based on a cell reference.
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.
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.
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.
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.
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.