Digital CanalOmeter mark II beta test version

PLEASE NOTE: THIS POST HAS BEEN SUPERSEDED.
I'm leaving it here just for the record.
A NEW SIMPLER VERSION HAS BEEN CREATED.

See this post for details


Thanks to the interest in my digital CanalOmeter spreadsheets and help from CanalPlan, we move on to the next phase for someone to test.  If you don’t know what I’m talking about, then read my earlier post  CanalOmeters for Tablets.
I’ve made very minor tweaks to the previous version and have been trying to find the simplest, most fool proof method of extracting the data from CanalPlan and pasting it into a spreadsheet.  If you fancy a go, please see if you can follow these instructions and report back any problems.  It’s really ever so easy, but like all  these things you do have to get it just right, so take care.
1. The first thing is to download a copy of my Digital CanalOmeter Template spreadsheet.  If I were you I would save two copies, one with a backup name in case you inadvertently mess up the original.  I have attempted to saved it as an Excel 93 onwards compatible format so most other versions ought to be able to read it, but holding it in Google docs does funny things so I’m not very sure what format you get it in.
  On my iPad I have been using Sheet²HD which cost me the princely sum of £3.99.  Some free ones might work just as well, I’ll leave it up to you.
2. Load up Canalplan in your browser and go to the Options page.  You need to click the All places button circled in red here, then the Apply button.  No other option settings are required.

3. Now return to CanalPlan’s home page and follow the Plan a journey link to enter your chosen route then click on Calculate route.  When the route shows up, scroll to the foot of the page and click on the button Export this route.
4. Here’s what you should see next

Should you see something different, make sure the Select output format box says CanalPlanAC CSV.  That should put you straight.
5. Now you have to use those arrow buttons I have circled to clear out the Used in output box and refill it by selecting items from the Available columns box so you end up with this

It is important that the right hand box ends up with the four items in the right order, top to bottom as shown.  (At some time in the future CanalPlan have offered to include a ready made output format for CanalOmeters, but of course they are busy with many other tweaks so for now we’ll do it this way.) When you’ve done that, click on Export this route.  You should then see a message Your export file has been generated, click here to open or download it.  Well what are you waiting for?  Click it and depending on what kind of computer/tablet you are using you should notice that a file called something like canalplanAC blah blah .csv is now in your possession.
6. Nearly there now.  Assuming you have some sort of compatible spreadsheet (if you haven’t, you shouldn’t have started all this) you ought to be able to click on the downloaded file and it should display itself in your spreadsheet program /app.  What you have to do now is to carefully Select and Copy (in one big piece) the table of data you see.
7. Last step.  Hang in there.
Into your spreadsheet app, load my CanalOmeter template.

click on cell E:11 and then do a Paste.  Hey presto, it’s done and you are now the proud owner of a digital CanalOmeter for your chosen route.  Before you start messing about with it, quickly save it under a route name e.g Braunston to Brum so it leaves empty template file is till available for future use.  It seems that the template downloads in read only form, for some reason I know not, so you may first need to change it to an editable file.  How you do this will depend on what operating system and software you are using but it should be easy.
BIG WARNING NOTE

• Because of the cheapo (well free with Windows 7 actually)  Excel version I have used to develop this, I have been unable to lock or protect any of the formula cells, so you could over write them and destroy the functionality.  That’s why I told you to make a backup copy.  DO NOT type anything in the green boxes.  The only place you type anything in is the pink column, where you type a F (or f) for the From point and a T (or t) for the To point.  The green boxes will fill automatically.

Once you have your Digital CanalOmeter complete, you can enter Fs and Ts to your heart’s content to find out the times, distances and locks between any two points.  Just remember to delete the previous Fs and Ts before you enter new ones.
Once you have mastered the above procedure you can make spreadsheets for all your favourite journeys.  They only take a few minutes each to do.
Well there it is.  I fully expect I have missed out a vital step or made a mistake somewhere so please regard this as a beta test version.  If anyone out there is kind enough to give it a go, please let me know if it worked and pass on any problems or suggestions.