Cascading combo boxes for project

[Hustler24]

Hi,

I am making a generic spreadsheet for a project that I am doing.

I have linked a list of drugs in one column with the relevant strength
of tablets of each of these drugs.

I would like to also link the dose of drug required in another column
so that when I choose a specific drug, I can also choose a relevan
strength and a relevant dose.

For example:

DRUG - Abacavir

FORMULATION - 200mg tablets or 625mg tablets

DOSE - 4mg/kg twice daily.

I am struggling to link the relevant dose to the drug. I have used th
Indirect method to link the drug names and formulations.

Hope you can help!

Thanks.

Michael

 

[Guest]

if you use a combo box to selet a drug then vlookup(drug,drug
table,column2,false) will return the strengths and vlookup(drug,drug
table,column3,false) will return dose.This assumes you have a table with all
three variables listed,the drug in say column A,the strengths in B and the
dose in C.

 

[Hustler24]

Thanks for your information.

So I have to initially make a table of drug names that repeat for eac
formulation that the drug has i.e

ABACAVIR 200mg TABLETS 4MG/KG
ABACAVIR 625mg TABLETS 4MG/KG
LAMIVUDINE 10MG/ML SYRUP 20MG/KG
LAMIVUDINE 200mg TABLETS 20MG/KG

etc etc, in order to use the vlookup function?

Thanks again

 

[flummi]

No.

Any vlookup with the key ABACAVIR will ALWAYS result in the FIRST entry
of ABACAVIR to be selected.

For dependent Listboxes look at this:

http://www.contextures.com/xlDataVal02.html

Alternatively you can use Autofilter to select what you need from the
above table.

Hans

 

[Hustler24]

I have managed to create a dependent column for the formulations base
on the drug I select.

The problem I'm having is being able to show choices for the relevan
dose to be given based on the drug I select.

This is because I have named all the relevant formulations as th
drug's name, and used the INDIRECT function, following the instruction
on the site that Hans provided.

How do I link doses to the drug chosen, if I have to also name th
doses as the same name as the drug, as I have done above?

Thanks for your advice

 

[flummi]

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.

 

[Hustler24]

Hi Hans,

In the spreadsheet attached, I have listed all of the drugs that we ar
using along with the formulations and each of the doses we'd like t
select from in Sheet1. Sheet2 contains the current combo boxes that
have created.

After this is complete. we'd like to set up formulas in adjoining boxe
to take into account the numbers in this data i.e. multiplying dose b
weight for instance.

You will need to explain to me how to get Excel to multiply number
when text is present in the cells that you are multiplying together.
For example, 40kg multplied by 5mg/kg.

One thing at a time though!

Many thanks for your help. It is appreciated.

Michael

+-------------------------------------------------------------------
|Filename: GenericDrugDatabase.zip
|Download: http://www.excelforum.com/attachment.php?postid=4467
+-------------------------------------------------------------------

 

[flummi]

Hi Michael,

Sorry, but I get an error message "invalid attachment" when I click the
link.

It would speed up things if you could send me the workbook via email.

Regards,

Hans

 

[Hustler24]

Can you send me your email by private message and I can send it to you.

Thanks

 

[flummi]

Hi Michael,

haven't received your workbook yet.

Regards

Hans