Issue 58375

The referenced URL, http://www.insystem.com/rbp/index.html contains a link to a
spreadsheet which one can download so as to get assistance with playing a game
called "Rail Baron". This game has a bunch of lookups and tables, and the
lookups are traditionally done with printed tables and dice. The use of an
automated assistant can cut an hour off of the game time. (A very long time ago
I wrote one in APL, which program has since been lost). My point is that this
spreadsheet is widely used and I am familiar with what the general point of the
code is.

It does not function properly under Openoffice. I have only tested it under
Fedora Core 4 in Linux.

There are many statements similar to:

=VLOOKUP(M9;INDIRECT(N8);IF(L9="Odd";2;3))

Whether one uses the above form, to try and get the region name of the table
from a cell where it has been calculated, or one codes as follows:

=VLOOKUP(M5;VLOOKUP(M4;Regions;IF(L4="Odd";2;3));IF(L5="Odd";2;3))

So as to imbed an additional VLOOKUP to get the table name so as to avoid the
use of "INDIRECT", the lookup fails.  The formula results in an error, typically
a 502 or 504.

I have also verified that stripping the outer VLOOKUP works properly to extract
the table name - that is, starting with 

=VLOOKUP(M5;VLOOKUP(M4;Regions;IF(L4="Odd";2;3));IF(L5="Odd";2;3))

I stripped the beginning and end of the formula such that my formula was:

=VLOOKUP(M4;Regions;IF(L4="Odd";2;3))

and this resulted in the table name that I believe that the author wanted to put
into the above formula. Also, if I replaced that part of the formula with the
region name that was extracted, so that:

=VLOOKUP(M5;VLOOKUP(M4;Regions;IF(L4="Odd";2;3));IF(L5="Odd";2;3))
becomes:
=VLOOKUP(M5;NorthEast;IF(L5="Odd";2;3))

then the lookup works. Of course, this does not work as a permanent fix - the
program uses random numbers to decide which table to select - so when you change
the formula and press enter, generally, you have typed in the wrong table name
because all the random numbers are recalculated and usually change. But as a way
of isolating the problem, this works.

And, of course, when using "INDIRECT", by inspection, the referenced cell
contains a region name that should be as the name of a lookup table - and when
hard coded in the vlookup as a test, works.

So far as I can tell, there is no way to code around this, or to bypass it. I've
only coded a couple of spreadsheets that contained code like this in my life -
I'm certainly not an expert. 

I have determined that if one manually substitutes the name of the table into
the formula, then the lookups work properly.  (I made a new page in my copy for
testing - the formula pages were locked -locked or not, things don't work). But
I copied the "Lookups page" (the second page) to the end of the multi-page
spreadsheet so that I could change it.

Finally, I have a very old copy of Excel on a slow windows machine and this code
works under Excel.