data validation-drag the formula down

**frenchboy** · 05-28-2011, 11:12 AM

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

**Richard Buttrey** · 05-28-2011, 11:32 AM

Hi,

Why not upload your workbook so that we can take a look. It's likely that you may not need INDIRECT() which is a volatile function and can slow things down.

If behind your Q. is the need to create dependent Validation lists then you may find Debra Dalgleish's http://www.contextures.com/xldataval02.html web site of some help.

REgards

**frenchboy** · 05-28-2011, 11:37 AM

Very good link, thank you very much.
How can I upload my workbook?

Thank you

JP

**Richard Buttrey** · 05-28-2011, 01:52 PM

Hi,

Go to the 'Manage attachments' option that you see underneath the message box in which you write your request.

Regards

**JBeaucaire** · 05-28-2011, 10:33 PM

You'll need to click on GO ADVANCED under the Quick Reply box below to open the extended features window. Then you can use the paperclip icon to attach your workbook.