Hi everybody,

I'm new here (and to Excel in a way) and need your help. I am discovering the data validation list at the moment and i'm now stuck.
I am managing an optician practice and I need to measure my staff dispensing results.
Here's what I'm trying to do:
In one cell, the dispenser will be able to select the type of lens they've sold (single vision, occupationals, bifocals or varifocals). Depending on their selection, they will be able to select the lens name in the following cell. I've used the "indirect(cell)" function for the data validation in order to offer the dispenser all the different options depending on the lens type.
Here's my source formula (in the AI22 cell):
=IF(E2="Single vision","AI3:AI13",IF(E2="Occupationals","AJ3:AJ10",IF(E2="bifocals","AK3:AK5",IF(E2 ="Varifocals","AL3:AL18"))))
and my data validation: =indirect(AI22)
I have managed to do it on 1 row. However as soon as I tried to "drag the formula down" the whole thing fails as the data validation becomes "=indirect (AI23)". I need it to "stay" in AI22 but I want the E2 in the formula to become E3.

I'm sorry if this is confusing and I hope you can help me.

Thank you very much,

JP