Search code examples
google-sheetsgoogle-sheets-formula

Get Values From a Specific Column Into a Dropdown

Want to ask if there's a quick way, an automated or using formulas for this scenario.

So I have "config" sheet, and each columns is a list used for a specific dropdown. config!A:A = clientA config!B:B = clientB

In a "summary" sheet, I need to add a dropdown in column C depending on the column A

For example summary!A2 contains "client A" so the dropdown in summary!C2 will show the list of clientA And summary!A3 contains "client B" so the dropdown in summary!C3 will show the list of clientB

What I currently do is named the range each in the "config" then in "summary" I put the Data Validation for the specific name.

I was wondering if there's a custom formula that I can put in the Data Validation for Column C that depends on the value in column A. The only challenge is there are spaces so in the Named Range I remove the space. And since it depends on the column, the row number is moving.

Looking for a formula since I am avoiding App Script for this specific file. Thanks

Hopefully someone could help me on this.

Thanks much.

You are all awesome!

Solution

  • What you can do is set an Auxiliary sheet (or extra columns far in "Summary"). You can set Summary!C2 the next Data Validation:

    =Auxiliary!A1:1

    Open the settings of that data validation and make sure there are no anchors (no $, for example A$1). If there is some, delete them

    Close it and then copy and paste special - Data Validation only to the rest of the cells enter image description here

    This way C2 will be associated with row 2 from Auxiliary, C3 with row 3 and so on

    Then, you can go to Auxiliary and set a formula in each row to filter the values according to B2, B3 (or however you identify the client... (a Query, or Filter) --> You'll probably need to transpose the information, so the list becomes a row

    With that done, each data validation will depend now on the value of that row

    Re-reading your example, you can do the same but instead of filter you can transpose the entire Config sheet and you'll have a row per client ......

    You have an example here: https://docs.google.com/spreadsheets/d/1jF5XoBkQll5tHEjADg508NMznmbuB43tyWv5R2S1mM8/edit?usp=sharing