Apache OpenOffice (AOO) Bugzilla – Issue 58375
vlookup fails when the table name is not hardcoded into the formula - INDIRECT fails
Last modified: 2008-02-06 13:26:32 UTC
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.
Created attachment 31754 [details] Spreadsheet which demonstrates failure.
I think there may be two issues here: 1. INDIRECT() doesn't support named cells (#4695) and 2. the name is a range, and ranges may need to be explicit (#4904). Suggest closing as dupe of #4695.
Closing as duplicate of 4695. *** This issue has been marked as a duplicate of 4695 ***
closed because duplicate