software

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: www.excelforum.com/member.php...oamp;userid=19678
View this thread: www.excelforum.com/showthread...hreadid=522531if 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!
quot;Hustler24quot; wrote:

gt;
gt; Hi,
gt;
gt; I am making a generic spreadsheet for a project that I am doing.
gt;
gt; I have linked a list of drugs in one column with the relevant strengths
gt; of tablets of each of these drugs.
gt;
gt; I would like to also link the dose of drug required in another column,
gt; so that when I choose a specific drug, I can also choose a relevant
gt; strength and a relevant dose.
gt;
gt; For example:
gt;
gt; DRUG - Abacavir
gt;
gt; FORMULATION - 200mg tablets or 625mg tablets
gt;
gt; DOSE - 4mg/kg twice daily.
gt;
gt; I am struggling to link the relevant dose to the drug. I have used the
gt; Indirect method to link the drug names and formulations.
gt;
gt; Hope you can help!
gt;
gt; Thanks.
gt;
gt; Michael.
gt;
gt;
gt; --
gt; Hustler24
gt; ------------------------------------------------------------------------
gt; Hustler24's Profile: www.excelforum.com/member.php...oamp;userid=19678
gt; View this thread: www.excelforum.com/showthread...hreadid=522531
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=19678
View this thread: www.excelforum.com/showthread...hreadid=522531No.

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

For dependent Listboxes look at this:

www.contextures.com/xlDataVal02.html

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

Hans
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: www.excelforum.com/member.php...oamp;userid=19678
View this thread: www.excelforum.com/showthread...hreadid=522531Hi,

I've done it like this:

DrugFormulationDosecolumn Dcolumn E
LAMIVUDINE200mg TABLETS20MG/KGDrugsFormulation ABAFormulation LAMIDose ABACAVIRDose LAMIVUDINE
ABACAVIR200mg TABLETS10MG/ML SYRUP4MG/KG 20MG/KG
LAMIVUDINE625mg TABLETS200mg TABLETS3MG/KG 15MG/KG
RangeRange Range RangeRange
name 1.Listboxname 2. Listboxname 2. Listboxname 3. Listboxname 3.
Listbox
DRUGSABACAVIRLAMIVUDINED_ABACAVIRD_LAMIVUDINE

Formula 1. LBFormula 2. LBFormula 2. LBFormula 3. LBFormula 3. LB
=Drugs=INDIRECT(A2)=INDIRECT(A2)=INDIRECT(quot;D_quot;amp;A2)=INDIRECT(quot;D_quot;amp;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:

DrugFormulationDosecolumn Dcolumn Ecolumn Fcolumn G
LAMIVUDINE200mg TABLETS15MG/KG
LAMIVUDINE10MG/ML SYRUP20MG/KG
ABACAVIR200mg TABLETS4MG/KGDrugsFormulation ABAFormulation LAMIDose ABACAVIR Fo1Dose ABACAVIR
Fo2Dose LAMIVUDINE F1Dose LAMIVUDINE F2
ABACAVIR200mg TABLETS10MG/ML SYRUP4MG/KG 3MG/KG 20MG/KG 15MG/KG
LAMIVUDINE625mg TABLETS200mg TABLETS

RangeRange Range RangeRange
name 1.Listboxname 2. Listboxname 2. Listboxname 3. Listboxname 3.
Listbox
DRUGSABACAVIRLAMIVUDINED_ABACAVIRD_LAMIVUDINE

Formula 1. LBFormula 2. LBFormula 2. LBFormula 3. LBFormula 3.
LBFormula 3. LBFormula 3. LB
=Drugs=INDIRECT(A2)=INDIRECT(A2)=INDIRECT(quot;D_quot;amp;A2amp;quot;_quot;amp;left(B2,4))Here I constructed the name for the validation ranges for the dose as
quot;D_ABACAVIR_200mquot;. 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.
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: www.excelforum.com/attachment.php?postid=4467 |
-------------------------------------------------------------------

--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: www.excelforum.com/member.php...oamp;userid=19678
View this thread: www.excelforum.com/showthread...hreadid=522531Hi Michael,

Sorry, but I get an error message quot;invalid attachmentquot; when I click the
link.

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

Regards,

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

Thanks.--
Hustler24
------------------------------------------------------------------------
Hustler24's Profile: www.excelforum.com/member.php...oamp;userid=19678
View this thread: www.excelforum.com/showthread...hreadid=522531Hi Michael,

haven't received your workbook yet.

Regards

Hans