Calc sheet LOOKUP if correct choice

[richramik1]

Hey Gang:

I have attached a calc sheet (Invoice/Proposal). I know what I want to do, but I am not sure on the correct approach or the results I will get.

I want to search the “CITIES” page for the appropriate zip code and return the complete cell for inclusion in a cell for the “CITY”. Currently, I am using a drop down. However, the company has acquired more territory and the list will be much longer. It is a bit of a pain looking through the list, even with the Alphabetic breaks I put in the list. FYI - there are three entries for 07006, but the are different towns. Ah, the wisdom of the US Post Office.

Thanks,
Rich Ramik

Z MODEL PROPOSAL or INVOICE - V09.01 - CURRENT 2024-01-08 - Copy (2).xlsx (320.3 KB)

[Wanderer]

Can’t check your file now, but maybe try to read about data validity. It could show you only the codes matching your entry. Obviously you have to select the correct one yourself, if one zip code can have more than one city…

[dscheikey]

Hi Rich,

Please find attached the document with my proposal. (updated)

At first glance, your drop-down works normally.
However, if you enter a value that does not match your list, it acts as a filter. However, you must confirm the filter value with Enter and the warning with OK. Simply typing does not filter the list in CALC.
For example, if you enter 07006, the list will be filtered by the three possible entries. You can then select as normal.

If the cell is empty, the full list is displayed. If you enter an exact value or select from the list, the full list is also displayed.

Compared to my first entry from yesterday, I have corrected errors and simplified.
For filtering, you now have a column B in the CITIES worksheet which shows you the filtered entries. These are shown in the DopDown. Unfortunately, after entering a filter value, you have to confirm it with OK. It is not a value from your list. Therefore CALC will not accept it without confirmation. In my first version I tried to work around this by calling a macro (without content). I did not consider that you are using the document as xlsx. This does not allow macros. That’s why it didn’t work. My apologies for that.

But it should work now. Also in EXCEL.

Z MODEL PROPOSAL or INVOICE - V09.01 - CURRENT 2024-01-08 - _new DropDown.xlsx (21,2 KB)

[richramik1]

I tried messing a bit with validity after I had published my question, but to no avail. Your solution is just what the doctor ordered!!! Thanks so much.