Hi,

I've done it like this:

Drug Formulation Dose column D column E
LAMIVUDINE 200mg TABLETS 20MG/KG


Drugs Formulation ABA Formulation LAMI Dose ABACAVIR Dose LAMIVUDINE
ABACAVIR 200mg TABLETS 10MG/ML SYRUP 4MG/KG 20MG/KG
LAMIVUDINE 625mg TABLETS 200mg TABLETS 3MG/KG 15MG/KG



Range Range Range Range Range
name 1.Listbox name 2. Listbox name 2. Listbox name 3. Listbox name 3.
Listbox
DRUGS ABACAVIR LAMIVUDINE D_ABACAVIR D_LAMIVUDINE

Formula 1. LB Formula 2. LB Formula 2. LB Formula 3. LB Formula 3. LB
=Drugs =INDIRECT(A2) =INDIRECT(A2) =INDIRECT("D_"&A2) =INDIRECT("D_"&A2)

But if the dose for one drug is the same for all formulations it
doesn't seem to make sense to create its own validation list. On the
other hand if there are different doses depending on the formulation we
would need to create a validation list per formulation as in:

Drug Formulation Dose column D column E column F column G
LAMIVUDINE 200mg TABLETS 15MG/KG
LAMIVUDINE 10MG/ML SYRUP 20MG/KG
ABACAVIR 200mg TABLETS 4MG/KG


Drugs Formulation ABA Formulation LAMI Dose ABACAVIR Fo1 Dose ABACAVIR
Fo2 Dose LAMIVUDINE F1 Dose LAMIVUDINE F2
ABACAVIR 200mg TABLETS 10MG/ML SYRUP 4MG/KG 3MG/KG 20MG/KG 15MG/KG
LAMIVUDINE 625mg TABLETS 200mg TABLETS

Range Range Range Range Range
name 1.Listbox name 2. Listbox name 2. Listbox name 3. Listbox name 3.
Listbox
DRUGS ABACAVIR LAMIVUDINE D_ABACAVIR D_LAMIVUDINE

Formula 1. LB Formula 2. LB Formula 2. LB Formula 3. LB Formula 3.
LB Formula 3. LB Formula 3. LB
=Drugs =INDIRECT(A2) =INDIRECT(A2) =INDIRECT("D_"&A2&"_"&left(B2,4))


Here I constructed the name for the validation ranges for the dose as
"D_ABACAVIR_200m". As you will see this can get rather complicated.

Maybe a VBA solution would be easier. If you want, send me a sample
sheet and I'll see what I can do.

Regards

Hans

Sorry for the crap formatting.