Let Me Count the Ways in Lotus 1-2-3

Those of you who follow me on Twitter know I have been frustrated about my working on a Lotus 1-2-3 SmartSuite Millenium Edition Version 9.0 Fix Pack 6.1 spreadsheet with 3.5 MILLION cells..

This is Windows 7 compatible.
SmartSuite is my backup suite, IBM Lotus Symphony has been my primary for almost 3 years.
Evidently it does not like it went I run Google+, causes problems. But other than that is fine.
One thing you may want to get when working on 1-2-3 is a program to enable mouse scrolling, it is on my slides page.

So the big problem was how to create a simple drop down or select option for a very long list of items.
In retrospect it was so simple but it's been a while since I had to code for 1-2-3.

If you have a better way or any enhancements by all means let me know.

I will work in progression from simpler to harder parts.

What I did was use the Macro language control called CHOOSE-ITEM.

You get to this by either typing {CHOOSE-ITEM or the { and press F3 and type or scroll down to CHOOSE-ITEM. It will prepopulate with this:

{CHOOSE-ITEM list_range;results_range;[prompt];[title];[x];[y]}

I kept this simple and ignored the x and y parts so mine looks like this:

{CHOOSE-ITEM list_range;results_range;[prompt];[title]}

Prompt will place any text you want in the dialogue box that pops up, I used Please Select a Name.
Title is the title for the Dialogue window that pops up. I called it Names.
So so far we have:

{CHOOSE-ITEM list_range;results_range;[Please Select a Name];[Names]}

The list_range should be substituted with a named range.
In this case I used a name range of LastNames. Because I was pulling data from other spreadsheets I made a secondary tab B and put all the names in the B column starting at B2. B1 had a title of Names.
In Column A I made a fill from 0-2000 running in parallel.
Highlight A1 to B2000 and right click on Range properties go to the Basics tab then Range name and called it LastNames.

Why do we need the numbers? You see when you do the CHOOSE-ITEM lookup it returns the number of the item in the list. Not very helpful as I need the name, but we will get to that shortly.

Now our command is shaping up. It now looks like this

{CHOOSE-ITEM LastNames;results_range;[Please Select a Name];[Names]}

Results Range can be ANY cell. Let us use D1 as the cell. So our formula now looks like:

{CHOOSE-ITEM LastNames;D1;[Please Select a Name];[Names]}

Not done yet.

Since this will be an automatic Macro on opening the spreadsheet, we need to place it in the A1 cell and name the cell (after we typed in our formula) with a \0 that is slash and a zero for a Range name as above when we made the LastNames range. But with a \0 this will run automatically.

We can also name the range with a secondary name like \J and now if we need to refresh the list we have the lookup again.

But when you run it you get a number in D1 and we need the name. Remember the A column of numbers? Now we will write a VLOOKUP formula to match the number to a name and then the rest of the spreadsheet feeds off that one.

In E1 or wherever you want the name to appear put in a formula like this:

@VLOOKUP(D1, A1..B2000,1)

What this formula means is a Vertical Lookup using Cell D1 as the code key.
The next option is the cell  range by name or numbers where we should be looking up the data.
The 1 at the end is the number of columns to the RIGHT of the 1st column. So if I had 4 columns of info to grab, this would be a1..d2000 and a 3 at the end.

If you need the list in CAPS just add @UPPER like this:

@UPPER(@VLOOKUP(D1, A1..B2000,1))

Now you run the macro using CTRL-J, select a name which puts the number in D1 and in E1 it now shows the Name.

If you want to hide D1 and your Macro from prying eyes then right click on the cell go to Range Properties, Picture of a Key tab and click on the hide cell contents.

Hope this helps someone else out there and if you need help with 1-2-3 just ask.

Many thanks to everyone that gave me ideas, especially Theo and Mat.