3

What I'd like to do is get a dropdown list of options from a VLOOKUP type function where I can select the value for the next lookup.

Example

Here the data I'm using for reference. Notice "AAA" appears twice.

REF SHEET:
-------------------
ACC | UNQ# |    AMT
--- | ---- | ------
AAA | 1000 | 100.00
BBB | 1001 | 100.00
AAA | 1002 | 200.00
CCC | 1003 | 100.00

Now when I type "AAA" in the ACC column I want the NUM# column to get a dropdown list of the array data (1000, 1002) returned where for example a VLOOKUP would return only one value. Once the NUM# value is selected by the user I can use a standard VLOOKUP function to populate the final AMT column.

USER SHEET:
----------------------------
ACC | DATA | NUM#   |    AMT
--- | ---- | ------ | ------
AAA | wxyz | 1000 ▼ |
    |      | 1002   |
  • 2
    That's not possible with Google Spreadsheet, unless you use Google Apps Script (either in Google Spreadsheet or as a stand-a-lone script). But that will require you to make a user interface, create logic, knowing Google Apps Script......and more – Jacob Jan Tuinstra Jan 25 '13 at 20:43
1

Why not have a column that concatenates ACC and UNQ#, like:-

AAA-1000
AAA-1002

Then when AAA was typed in you would get the two options, then you can use SPLIT to get the UNQ# to do the second vlookup.

| improve this answer | |
0

Why not use data validation with a dropdown? Make the user select from a range, your UNQ# column. If you want it to display only if something is entered in the previous column, use an empty check.

| improve this answer | |
0

It may not be the most elegant, but one approach could be to set a Data Validation sheet that will filter based on what you enter into that row. Once something is entered, the filter pulls related ID #s, and the data validation references that. This only works if you are entering this for a single cell reference though, as you might for a quick lookup.

Use validation from a list (probably on a separate sheet). That list is generated by this formula: =Filter(B2:B, A1:A=A1)


If it's over a broader range, you'll need a much larger set up which has been detailed here: https://infoinspired.com/google-docs/spreadsheet/dynamic-dependent-drop-down-list-in-google-sheets/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.