Selecting an array from 1006 alternatives via 4 dropdown boxes

I ought to start by defining what I am calling 'an array': I have 1006 separate arrays each comprising values in 52 columns x 12 rows. The majority of the values are conditionally formatted (i.e. coloured through red to green).

Four drop down boxes would define the right array: 1. Which route; 2. north or south; 3. springs or neap tides; 4. boat speed. The result could effectively generate a code viz: 'R12nrthspr5' (Route No 12, northwards on spring tides at 5 knots).

What I am thinking is: is it possible to code each array rather like a Named Cell, AND then display the selected array in either the worksheet under the drop down boxes or like a chart (I would assume to have the 1006 'arrays' in a second worksheet which would need to be protected. The conditional formatting needs to be retained)

Is this possible please?

I am sure you are right it is possible. Describing what I am trying to create though is not easy!

What I am calling the 'arrays' is the product of a very large Excel file; indeed two large Excel sheets which are too large as attachments.

The Excel file attached is one example: this is for Route 1 (there are 61 other routes currently in separate Excel files). In each worksheet there are 6 arrays, each is for 3, 4, 5, 6, 7 or 8 knots boat speed but in the final product I would only use 4 to 7 knot tables; each of the four worksheets are the direction (north or south) or Spring and Neap Tides. Each of those arrays has been created as I have said by the separate (and large) workbook. As the master file is too large to attach I am showing an image that part of the important worksheet which I hope illustrates in the top part the drop down boxes and in the lower part how the 'red and green' arrays are created.

A user would want to select the route, the direction, spring or neap tide and the boat speed. By selecting those variables by drop down boxes, I want to call up the appropriate array.

I hope that assists. Please let me know anything else.

In reverse order: it is using 2013. Sorry I'll update my profile. I may upgrade to 365 shortly but I am in the process of re-building my machine to resolve Win 10 update issues first - don't let's go there!

The first image is a sample 'array'. That would be a finished product about timing and as a product that data would not manipulated or analysed - it would be analysed visually by a reader. I had been trying to avoid a long winded background explanation but a little bit might help. The Thames Estuary is a unique navigational challenge. For a yachtsman or woman there are 9 locations from where you would start or end. Depending on the start and destination there may be only one practical route or there might be five. Each route is defined by navigation challenges - sand banks, narrow swatchways (channels), shallows, busy shipping routes, wind farms, dangerous wrecks, obstacles AND two tidal influences that are not timed in harmony. Sailing is effectively a slow means of travel so benefitting from the tide is critical. So the red and green table (or 'array') gives a leisure sailor a lot of information: the green shows where the tide is helping, the red shows the opposite. So naturally we would look for a column of greens (the column provides the start time every 15 minutes during the day) ... but note in this example there is no such thing as a full column of greens in that particular route at that particular speed on that tide range. But the red and green table instantly provides the reader the best time to start to get the quickest way (the blue arrow). But it isn't just 'the quickest way' because there are some navigational issues such as timing that would clash with Low Water at the shallowest part of the route or practical issues such as needing to carry with the flood tide up a river (e.g. a reader might need to start earlier which would take longer but the red and green would allow the reader to have the right start time to end up at the right time at the end of the passage). So each red and green table (the array - I probably should be calling it the range?) would be called up by the reader - by selecting the route, travelling e.g. northwards, on the day in question will be a spring tide or neap tide (let's just avoid in-between out of it for the moment) who will average at say 5 knots boat speed.

The second image above is my workbook that does all the manipulation and analysis. It comprises over 33 MB data and provides the way to give over 1.2m accurate answers instantly. It gives a great deal of information; for example the plain section in the middle provides the timing of the tide cycle which can be very important. There are some other products I have developed and also in future will be developed but that would take this thread off to a tangent.


I am spectacularly unsuccessful with VBA so I would prefer a traditional approach. Providing I can build the structure I would trim out the vacant rows - each route can have a different number of sectors to make it look neater. I would put each array in one worksheet or perhaps a limit number of worksheets. I have been working on the idea that the drop down boxes would effectively generate a code. Are you suggesting INDIRECT() would then display the array?

Sorry for the long winded response but I hope it helps.

Hmm quite! I can put all separate 1006 named range on one worksheet. I was hoping having done all the hard work in generating each named range it could be quite simple to call up in, say, a second worksheet any one required named range. The value is each individual named range. It provides so many answer for the yachtsman/woman. To include every red and green table (i.e. each named range) as a supplement to the book is impossible - over 250 pages. I had been hoping it was possible to have controlled access via my web site or on the cover of the book there is a DVD with the Excel file. Of course a DVD of red and green tables in pdf form would be easy but couldn't include additional information specific to each route. And a DVD wouldn't be popular with smart phone users.

I have read a fair bit about BI but it is heavily skewed to traditional wholesale and retail business, not readily digested for time and distance which is what tides are all about. Perhaps I should be looking at an Access application.

Thanks for your assistance, and indeed anymore comment you might have. I confess to not entirely understanding your last sentence though. It is slightly frustrating that Excel has successfully interlaced a massive amount of data and extracted a range of different products but what seems (to me) to be a rather simple function is impossible.