LinkBack URL
About LinkBacks
This may take a little time to explain. Please bear with me.
I have successfully used Excel to produce a series of tables to solve navigational difficulties for yachtsmen and women. The tables are included in a book which is now in its second edition. The navigational difficulties are to planning for the best use of the tides when crossing the Thames Estuary (UK). I will not elaborate over the tidal difficulties for planning but to summarise the solution, a user will select a route; the route will list a number of consecutive sectors (generally 7 or 8 sectors but may be less or up to 10 sectors depending on the route). Having picked the route, the user will select the right table for the average boat speed and whether the tides are Spring or Neap (these vary in their speed). The tables provide the time a sector will take depending on the state of the tide when it is started. I would suggest you see my web site: www.crossingthethamesestuary.com. I have a video which explains how the tables work and my readers appear to like the product. The tables are entirely by Excel using trigonometry formulae. Of course the printed tables just show the values: no one see the work of the calculations behind the tables.
I have tried to ‘automate’ the tables or rather make a start. I took one route and using IF and AND and VLOOKUP I was able to make the automation work. Here is an image of the ‘start’
\1
There are three dropdown menus: The intended boat speed, The Tide and the Proposed Start Time (I better explain the ‘designation’. Time is all referenced to a single datum – the time of high water at the port of Sheerness. Using the single datum any of the tables can be used forever provided the user knows the time of high water at Sheerness)
The ‘automation’ copes with the three dropdown menus. There are two major issues. The first I will mention but ‘park’. That is checking accuracy of the formulae! The formulae may work but have I constructed it ‘right’ for the purpose. My intention was to try to construct the core of the VLOOKUP (and the structure of the data) so after I have thoroughly tested the sample I am just replicating the core of the VLOOKUP. But leave that. The issue I have come up against a mental block.
My trial is just one route. My book and tables have 63 routes; indeed they could be argued as 126 routes because they can be used either direction – north or south. There are a total of 87 sectors so each route selects defined sectors e.g. Route 1 will select sectors A1, A2, A3, A4, A5, A6, A7 and A8 north and the reverse order for south. Route 2 will have different sectors.
So I was trying to construct further. If I have a dropdown box of each of the 63 routes, how can the selected route automatically display the sector numbers row by row in the next column and the description row by row in the following column. This image has a dropdown menu for the main route ‘banner’. In this example the Sector No and Sector Description work using IF and AND but if built to select from each of the 63 choices in the dropdown menu, the formula will be over complicated. Is there a simple way?
\1
The problem will then take me further. Assuming the Sector No and Sector Description is automated from the Route dropdown list my (so called) core of VLOOKUP will need to select the Sector number. I am thinking that provided I have a separate worksheet structure for each route then all I have to do is change the right worksheet reference in the VLOOKUP formula. It’s not difficult to build those worksheets but I suspect this is just going to clog up.
1. Have I been clearer?
2. I am trying to be too clever?
3. Should I really be using Access (I have no knowledge at all)
am using Excel 2013
Register To Reply
Bookmarks