Using a Spreadsheet to Create Ciphers

In January a few years ago, I had proposed to our local group that we collaborate in placing a themed set of caches. The Hides of March was born and the theme would be Roman, Latin, Caesar or none of the above. All caches had to be placed during the month of March. Of course a Caesar shift cipher would work perfectly within the theme. I had decided to add a twist however and came up with this cache description:

 U? V?.. The Hides of March

The Roman alphabet as written had no u, so v did double duty. Certain architectural styles, mostly government buildings, have examples of this. Check ovt the DOVGLAS COVNTY COVRT HOVSE, vvhere ivstice is served. Hmm, I gvess there are some other missing letters as vvell. To solve the coordinate pvzzle use the classical Latin alphabet of 23 letters for a trve Ivlivs Caesar shift cipher.

This is a high mvggle area, so exercise cavtion. You are seeking a nano container.

There are plenty of ROT13 and Caesar shift websites out there but I didn't know of any that used the classical alphabet so I set out to make my own cipher sheet.

To make it work, I set up a table in Excel (Open Office Spreadsheet should work as well for this) and wrote lookups to that table. The table was the Roman alphabet which doesn't have J, U or W and it was repeated on the next line but beginning with B instead of A and continued in that manner until there was a complete alphabet both vertical and horizontal.

Adding a number designation to each row, I could write the lookups. Allowing a cell per letter with empty cells between words, I typed in my coordinates, spelling the numbers out.  A few rows below that I added this formula =IF(AA3="","",HLOOKUP(AA3,$A$4:$W$26,$Y$2+1,FALSE)), AA3 is the cell 2 rows above the formula and the dollar signs ensure that the formula always looks to the  alphabet table and rotation value. The other values would change relative to their position, always looking 2 cells above to the text that you want encrypted, when it is copied across as far as needed for both the North coordinates and the West coordinates. By changing the number in the grey (Y2) cell, the letters would change as the alphabet shifted by that amount.  You'll notice that the coordinates don't use U or W for this alternate alphabet. Of course for added difficulty you could forget to mention it is a non standard alphabet.

This same process can be set up to use a symbol substitute for each letter and follow the same process as above. Variations could be a progressive rotation for each word, alphanumerics, or plain text backwards then ciphered. Vowels can be omitted or words garbled but still readable before encrypting.

By catsnfish

A couple of empty-nesters who caught the caching bug not realizing it was incurable. So if we’re found in the woods waltzing with Garmins, lifting lampskirts while tying our shoe or looking for “gum” underneath benches, be sure to stay away... it’s contagious and the only temporary relief can be found in finding bison’s, ammo’s, nano’s, or passing coins and spreading travel bugs!

Publisher's Note: Catsnfish write the periodic column The Adventures of Catsnfish. Subscribe (free) to The Online Geocacher to get an email alert when a new article is published.

Comments(2):

1. GC number

   I would like to look at this puzzle as I wait out the next snow storm. What's the GC number so I can check it out. Thanks.

   Sunday, January 17, 2010 Marvin

2. GC number

   GC19R8P U? V?...Hides of March.

   Monday, January 18, 2010 vic