Autofill drop down options based on presence of specific characters within specific cells

[appreciatethehelp]

Hi, hope all are keeping well.

I’m making a list containing details of some of my students regarding their test results.
Certain tests determine the students’ development of skills in the following areas: “Stamina”, “Speed” & “Technique”, which are graded using drop down options “Lvl. 1”, “Lvl. 2” & “Lvl 3”.

I want to set up the sheet so that when “yes” is present in the “passed test” column these drop down options are selected/filled automatically at the correct “level”- if the test determines skills at a certain, graded level, and the student has passed it, I don’t want to have to fill out details of the skill grades said test represents; it could and should be done automatically.

Here is an illustrative example doc:
Student test results example.ods

Cheers.

Edit: I know I could achieve a similar result using IF formulae in the “Stamina”, “Speed” and “Technique” columns in lieu of drop down lists, however I’m curious as to whether it can be done this way, as there are advantages to this approach.

[anon73440385]

I want to set up the sheet so that when “yes” is present in the “passed test” column these drop down options are selected/filled automatically at the correct “level”

That’s - at least for me - incomprehensible, how a single “yes” automatically should be able to distinguish between three different detail levels.

In addition, the whole idea is unclear, since validity drop downs are for selections (sic!) and and hence you can't perform an automatic selection from a selection list. If there is an algorithm for a decision which value to choose, than there is a formula and a selection list is superfluous.

[dscheikey]

Hi,

I build an answer Sheet. I hope this is that what you expect. The formula is in a named range. So you can select Automatic or choose a level yourself from the drop down.

best regards

Jürgen

LV123.ods

[dscheikey]

Hello appreciatethehelp, have you seen that I have answered your question. Did you look at the sheet? I’d say your problem is solved. A comment from you and marking the question as answered would be helpful. To mark the question please click on the small hook in the circle.