I thought it would be helpful to list some of the things I felt helpful to me. It took me a while and help from Bob and Craig to figure this out, so you may find this gets you going quicker than it did for me.
First of all, I am most impressed with the work Bob and Craig have done, especially Bob. I never could have done anything with them.
In order to make things better, for me at least, I had three goals in mind:
- Enter a number only once, if possible;
- Make things as easy to read as possible, and,
- Use the mouse as little as possible.
To achieve these goals, I needed to write some macros. I could use AutoHotKeys, but there were problems with that approach, and it was natural to wrote macros in the spreadsheet itself.Display
There were two issues here for me. One was that sometimes the green and purple borders defining Event areas and the date cell sometimes obscured the writing underneath them. After using the sheet a couple of times, and realizing that only data entered in those areas (other than the WR data) is saved, I really didn't need such thick coloured lines, so I set my line width from 2.0 to 0.25.
For the area in the green line, select the area by clicking the cell in one corner, and hold down the shift key and click the cell in the opposite corner. (The area should now be coloured a dark blue). For the date cell, just click the cell. Then select Format/Cell/Borders and pick a line width (and/or colour) that is suitable.
The other issue was the size of the log page. At first I tried to have the page sized so that the WR data nearly filled the available window. I did that so I wouldn't have data below the window area, because it is slow tabbing to a cell that is below the window, and then it takes time to move up to the top of the next column (usually above the top of the window).
Thanks to Craig, I discovered that there is a slider at the bottom right corner of the spreadsheet window. You can slide this around until you get a magnification that makes the data on the logpage easily readable. For me this was 150%. If you do not see that slider, click View/Status Bar.
Easy to read, but awkward for navigation.Navigation
Using the work Bob did and a giant hint from Craig, I wrote several macros for moving around the spreadsheet. For these, I use shortcut keys. (More about shortcut keys near the end.
With the zoom at 150%, I can't see the date cell, or the noon location cell etc. Also, keeping in mind I want to enter numbers only once if possible:Alt-d
(for Date). This is not the date cell, but two cells to the left of the date cell. In this cell I enter the two digit day... 03. In the cell immediately to the right of it, I have already entered /mm/yyyy (/05/1914). When a number is entered for the day, an event is triggered (which I learned from Bob) which puts the string 03/15/1914 in the purple lined date cell. When the data are cleared, the contents of the DD and /MM/YYYY cells remain, so when you transcribe the next day, you need only do Alt-d and enter 04. (Why type /05/1914 31 times a month?)Alt-p
for position at noon. This macro goes to the noon position box. It enters the word Noon in the time box and activates the Tag box. (Why type Noon 365 times a year?)Alt-Home
activates the first cell in the WR data entry area (Hour 1 Wind Direction). I may also add Alt-Left Arrow as an additional shortcut key.Alt-'
Entering dittos can be done by a right click on a cell, or by using Alt-' for entering dittos for every hour (Direction, Weather Code and Cloud Code), or Alt-" for entering dittos for even hours only. The the WR data has a mix of reports some on every hour and some every other hour, I pick one and delete the dittos not needed or enter the ones as needed.
With the zoom at 150%, I can't quickly enter the data for those hours below the window. Each time I move to an hour below with window, the screen is re-drawn. However, I wrote a macro to fix this: Alt-Down Arrow
. This macro activates the cell four rows down from the current cell. This causes Calc to re-draw the display so that row is in the centre of the display. The macro then activates the cell which was one row down from the original cell. This re-draw is as quick as the re-draw to move the display up one row, but this time the cell for data entry is nearer the top of the display instead of being at the bottom. By activating a cell four rows down means that you don't have to get to the very bottom to activate it. I find this handy because I can activate it when I am two or three rows from the bottom. I usually use the down arrow to move to the next cell, it is natural to press the Alt key to move down one with a re-draw of the display.Alt-Up Arrow
. When I have finished entering data for one column, I need to move up and over to the top of the next column. Alt-Up Arrow does that. Alt-[
I use these to copy data from the cell above to the current cell. Use Alt-[ when the data are every hour, and use Alt-] when the data are every other hour. If the pressure is 29.97 for Hour 4 and the data are every other hour, you must be in the cell for Hour 6 before you press the Alt-] key. Using this macro copies the data into your cell and then moves to the next cell (Hour 6 for the first case and Hour 8 for the case with data every other hour.) When you have one value for several consecutive hours, just hold down the Alt key and press [ or ] as often as needed. This command is quicker than entering data for even just one duplicate value.Alt-Right Arrow
This activates a cell at the top of the page more or less in the centre of the Event area. It causes the display to be re-drawn so the Event page is nicely displayed. Which is why I may add Alt-Right Arrow to go to the top of the WR data. (Left Arrow left page, Right Arrow right page)Ctrl-s
This saves the data, and is identical to clicking the Write Data button. For Calc, Ctrl-S (Ctrl-Shift-S) is the command to save the spreadsheet.Alt-w
This activates the Event Wizard.Macros and Shortcut Keys
Not all keyboards are the same. Craig's keyboard for French has no [ or ] keys. Your ' and " may not be in a similar relationship on your keyboard as they are on mine. You may prefer other key combinations. So, here is how you do it.
First of all, with the AutoHotKey macros, Alt-k is the same as Alt-K. Alt-1 is different from Alt-NumPad1. With the Calc shortcut keys Alt-k is different from Alt-K but Alt-1 is the same as Alt-NumPad1. Furthermore, in AutoHotKey, the right and left Alt keys are the same. In Calc, the left Alt key works, but not the right. Craig says that Alt Right is not allowed in his home!
One other note, when you are picking a shortcut key in Calc, Alt-k is listed as Alt-K and Alt-K is listed as Alt-Shift_K.
So, assigning a shortcut key. Tools/Customize/Keyboard. In the Category box, near the bottom, select:
Extensions (or OW)
Choose the macro to which you are going to assign a shortcut key. If there is already one there, you can leave it, or delete it, and you can add another of your choosing. You can have more than one shortcut key assigned to the same macro, but obviously you can't have one shortcut key assigned to more than one macro.
When you have the shortcut key you wish, click Modify. When you are done assigning shortcut keys, press OK.Downloading Data
Rather than try to explain this, I have an AutoHotkey script that downloads images from a selection made on the tracking spreadsheet. You have the option of having these images enhanced for making the handwriting more clear, and you have the option of having old images and XML files deleted. You can also have the Event half of the page copied into a separate image for viewing separately, which I find most useful.
If you would like this script send me a PM and I will help you get it set up.