Is it possible to create a data validation list that auto populates after selecting a specific item in a separate data validation list? Let's say I want to select fruit or vegetable in the first list, then I want the second list to auto populates saying either apple or potatoe.

Hi Jacob, You can create two separate ranges listing fruits and vegetables, and assign range names to them exactly matching the values in you higher level list (i.e. Fruit & Vegetable). If you have you first validation cell in C3 to select either fruit or vegetable, then in the second validation cell you would need to place the formula '=INDIRECT($C$3)'. The result of the formula would be the range name to refer to depending on current selecion in C3. Please see attached for an example. Hope this helps Yury

Data validation | Microsoft Community Hub

6 Replies

Yury Tokarev
Steel Contributor
Aug 11, 2017
Hi Jacob,

You can create two separate ranges listing fruits and vegetables, and assign range names to them exactly matching the values in you higher level list (i.e. Fruit & Vegetable). If you have you first validation cell in C3 to select either fruit or vegetable, then in the second validation cell you would need to place the formula '=INDIRECT($C$3)'. The result of the formula would be the range name to refer to depending on current selecion in C3. Please see attached for an example.

Hope this helps

Yury
DataValidation.xlsx11 KB
- Jacob Haarstad
  Copper Contributor
  Aug 11, 2017
  Thanks for the help! I see what you did to get the result that my question pertained to. However, my question was maybe a little to.... misleading. I attached a spreadsheet of what I am actually working on. In the List tab, I have several tables each are there own category of chemical types along with the prices of each. What I would like to do is in the first sheet, first select the type of chemical and then with the second list select a specific chemical based of the first selection. With that I would like to have all the price per acre and total cost auto populate like it does. The main reason for this is, when everything is grouped together in one table, it gets very long when putting it in a data validation list. Thanks again for any help.
  Eric's pricing spreadsheet.xlsx49 KB
  - Yury Tokarev
    Steel Contributor
    Aug 11, 2017
    Hi Jacob,
    
    could you please clarify which columns you wish to populate with which numbers once you select values in the Type and Product columns? I would just like to get a better understanding of your requirement.
    
    Many thanks
    Yury

Forum Discussion

Data validation

6 Replies

Resources