Data validation

[Jacob Haarstad]

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.

[#2 · Accepted solution]

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

[#3]

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. 

[#4]

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 

[#5]

First, I should've changed the "Chemical" Column in the New Method sheet to "Product", that way it matches the List sheet. The crop and acre column will auto populate after selecting a Farm. I would like the Measured units, Measured units/ acre and cost per acre to auto polulate from the list tab after selecting a product. Total cost should then auto populate by taking the acres and multiplying by the cost/acre. Total purchased units should do the same by taking total acres times measured units/ acre. 

I noticed some of the headers didn't match in the corresponding tables, so I attached a new copy that will probably be a little more helpfull.

I really appreciate you taking the time to help me. I just started learning how to use excel this year and its definately more complex then I ever imagined. I know this isn't the most efficient layout, but it is just an example that I came up with quick. I have several other spreadsheets that I could use this method in, but they are more complex to explain. 

[#6]

Hi Jacob,

I would create an extra 'Combo' column in in the tables on the sheet 'List' to concatenate the fields 'Type' and 'Product'. Then, in the 'New Method' table I would use Index/Match to lookup for the respective value (also being a concatenation of values in the columns 'Type' and 'Product').

Please see attached for an example. Ideally, I would recommend combining the multiple 'Type' tables in the 'List' sheet into one, which would make the Index/Match formula refer to a column in a single table as opposed to a sheet column or a range (in my solution I have referred to sheet columns for simlicity, which is not ideal). 

Hope this is helpful

Best regards

Yury

[#7]

Thanks for the help. Unfortunately, I am away from home and only have a tablet and my phone for the next week and neither one will open the attachment. So I will have to wait until I get home to check it. Thanks again for time to look at it.