Daily follow-up
- Aug. 17, 2019
 | Example files with this article: |
Introduction
The average Excel user manages in Excel pretty much only shopping lists and the organisation of parties... So this time I made a file for that average Excel user, who is not looking for a lot of bells and whistles but just the easy things like data entry.
I was thinking about this and so I created the attached workbook. Imagine you are keeping track of day-to-day tasks and duties and goals over a period of say, 3 months. For example, managing weight loss or sports activities, or noting down whether activities were executed by the kids, whom did that, and so on. Indeed, a household situation with several smaller daily activities would be a good example. You print an empty scheme, you pin it to the fridge and fill out the scheme on a regular basis. After a while, you enter the data in Excel and you print a new empty sheet.
Please open up the attached workbook. Note that it contains small macros which aim at simplifying the data input by the user. If you decide to not use it, just save the file without macros and you are done. Below the picture we will explain what these custom macros do.
Needless to say, you can adjust the scheme. Put the start date in the first cel in the upper left corner (cell A1 with a white font), that will most probably be the first day of the month. Formulas will show the successive dates in the rows, for 3 months. You will need to indicate which days are weekends and which are holidays. Conditional formatting will make these rows show up with a green background color.
Nice formulas
Even me I will tell this, the formulas that I applied in the workbook to calculate averages, are worth investigating. You will find averages per month and averages per 3 months. Some of the formulas are array formulas and need to be confirmed with Ctrl + Shift + Enter instead of just Enter. The formula to determine when we usually go to bed, is not at all straightforward.
VBA code
The input for the columns 'activity', 'weight', 'bedtime' happens manually. The cell contents with 'activity' will be made smaller in case the text will spill over to the columns on the right. The input for the columns 'goal 1', 'goal 2', 'goal 3' can happen manually but just as well with VBA by double clicking the cell:
- For 'goal 1' there is a carroussel that cycles through 'empty cell' - I - II - III - again 'empty cell', each time on double click
- For 'goal 2' and 'goal 3' VBA will put a checkmark, or removes it, each time on double click
- For 'joggen' there is a carroussel that cycles through 'bold checkmark' (=jogging) - 'normal checkmark' (=walking) - 'empty cell' (=no activity), each time on double click
I am sure that the functionality of a double click can bring a serious advantage to users of Excel and the PC that are not computer literate.
