Cascading combo boxes for project
 

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 strengths
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 relevant
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 the
Indirect method to link the drug names and formulations.

Hope you can help!

Thanks.

Michael.


--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

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.
--
paul
remove nospam for email addy!



"Hustler24" wrote:


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 strengths
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 relevant
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 the
Indirect method to link the drug names and formulations.

Hope you can help!

Thanks.

Michael.


--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

Thanks for your information.

So I have to initially make a table of drug names that repeat for each
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.


--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

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

Cascading combo boxes for project
 

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

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

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

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

Thanks for your advice.


--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

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.

Cascading combo boxes for project
 

Hi Hans,

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

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

You will need to explain to me how to get Excel to multiply numbers
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 |
+-------------------------------------------------------------------+

--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

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

Cascading combo boxes for project
 

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

Thanks.


--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: http://www.excelforum.com/member.php...o&userid=19678
View this thread: http://www.excelforum.com/showthread...hreadid=522531

Cascading combo boxes for project
 

Hi Michael,

haven't received your workbook yet.

Regards

Hans