Select from a term list, but input the corresponding key

[Travis Kunnen]

Hello All

So im working on a data sheet for customers, and we want to replace long sample names with a key as a drop down list.
So Cells A86 to A90 are the keys (A, B, C, D, E etc). and the matching / corresponding samples names are in B86 to B90 (lower level plain, upper level plain, upper embarkment, lower embarkment, flood plain flats).
The key will always be A, B, C, D, or 1, 2, 3, 4 etc, but the sample names will always change depending on the source of the sample.

In our work list, starting on E15, I would like a formula / drop down list... where the user can click a list and see the full sample names in B86 to B90.. but once selected, the key is input, and then again for E16, E17, E18... to select many samples for the same test...
And then E16 (which is a different test), and then again a drop down list to select the full samples names in B86 to B90, once selected, show the key only.
This is going to continue from E15 to E81.
We can add in as many columns as we like to accommodate the tests, if multiple samples cannot be selected and included within the same cell.

There are additional keys in D86 to D90, G86 to G90 and J86 to J90, the same as additional sample names, one column to the right of each key name.

With thanks
Travis...

 

[sijpie]

A few things are not completly clear, which I'll come to below. But for the sake of creating a robust workbook, you should have your table with keys on a different sheet from your work list. It will be too easy in the use of such a workbook for users to add some rows or columns, or even delete columns and throw your table with keys in disarray. When the reference table is on its own sheet, then first of all you can have the table at the top of the sheet for easy maintenance, but you can also hide the sheet so that the users can't play havoc with it.

OK. for my questions:

1. I am in cell E15. This cell has been set up with a lookup list with the names from B86:B90
2. I click on the arrow and select an item, let's say 'LongKeyName'.
3. The list closes and cell E15 shows the short key C
4. I can then go down the rows in column E and continue this process.

This is easy to achieve.

But now you say that there are additional keys and full names in D86:E90, G86:H90 and J86:K90. When does the lookup list show one of these tables? Is that what is depending on the source? Is there a reference cell that holds the source, so that the sheet (macro) can know which table to use for the lookup?

Please clarify, and please, pretty please, move your key tables to a new sheet. It will save you endless frustration later on.