find and output relevent data

[amaanebrahim]

Hi,

I'm trying to find a medication displayed on sheet 1 within a list of medications (displayed on sheet 3) and output its relevant usage (relevant totals cell displayed on sheet 2) - for example if Allopurinol 100mg tabs is the medication displayed on sheet 1, then the quantity cell should display 272. I have been trying to use the if(isnumber(search function but it only throws out value errors, can anyone help me with this.

Thanks
[TABLE="width: 657, align: left"]
<tbody>[TR]
[TD]Medication[/TD]
[TD="colspan: 3"]Quantity
[/TD]
[TD="colspan: 7"] sheet 1
[/TD]
[/TR]
[TR]
[TD]Allopurinol 100mg tabs
[/TD]
[TD="colspan: 3"]display totals column depending on medicine here ie here it would be 272[/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD]Amitriptyline 10mg tabs
[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD]Amitriptyline 25mg tabs
[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD]Amitriptyline 50mg tabs
[/TD]
[TD="colspan: 3"]



sheet 2[/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Medication
[/TD]
[TD]Pack
[/TD]
[TD="colspan: 2"]x
[/TD]
[TD]xx
[/TD]
[TD]xxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxxx
[/TD]
[TD]xxxxxx
[/TD]
[TD]Totals

[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Alendronic acid once weekly 70mg tabs
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]
[/TD]
[TD]26
[/TD]
[TD]
[/TD]
[TD]58
[/TD]
[TD]145
[/TD]
[TD]73
[/TD]
[TD]306
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Allopurinol 100mg tabs
[/TD]
[TD]28
[/TD]
[TD="colspan: 2"]80
[/TD]
[TD]15
[/TD]
[TD]
[/TD]
[TD]35
[/TD]
[TD]58
[/TD]
[TD]56
[/TD]
[TD]272
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Allopurinol 300mg tabs
[/TD]
[TD]28
[/TD]
[TD="colspan: 2"]20
[/TD]
[TD]30
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]62
[/TD]
[TD]28
[/TD]
[TD]173
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Amiodarone 200mg tabs
[/TD]
[TD]28
[/TD]
[TD="colspan: 2"]
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]16
[/TD]
[TD]
[/TD]
[TD]46
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Amitriptyline 10mg tabs
[/TD]
[TD]28
[/TD]
[TD="colspan: 2"]71
[/TD]
[TD]57
[/TD]
[TD]
[/TD]
[TD]68
[/TD]
[TD]230.75
[/TD]
[TD]175
[/TD]
[TD]629.75
[/TD]
[/TR]
</tbody>[/TABLE]

 

[NdNoviceHlp]

Not real clear on how your data is set up but this may work...
Place this in Module code....

Public Function PillTotal(DrugName As String, Rng As Range) As Double
For Each c In Rng
If c.Value = DrugName Then
'Pill total in row "I" of sheet2 ***change to suit
PillTotal = Sheets("Sheet2").Cells(c.Row, "I")
Exit For
End If
PillTotal = 0
Next c
End Function

To operate, if U have the name of the drug your looking for in sheet1A2 and your list of drug names in sheet2 A2:A7 with their corresponding drug totals in column "I" then in sheet1B2 enter this formula...

=PillTotal(A2,(Sheet2!A2:A7))

HTH. Dave

 

[amaanebrahim]

Thank you so much! This worked!

Not real clear on how your data is set up but this may work...
Place this in Module code....

Public Function PillTotal(DrugName As String, Rng As Range) As Double
For Each c In Rng
If c.Value = DrugName Then
'Pill total in row "I" of sheet2 ***change to suit
PillTotal = Sheets("Sheet2").Cells(c.Row, "I")
Exit For
End If
PillTotal = 0
Next c
End Function

To operate, if U have the name of the drug your looking for in sheet1A2 and your list of drug names in sheet2 A2:A7 with their corresponding drug totals in column "I" then in sheet1B2 enter this formula...

=PillTotal(A2,(Sheet2!A2:A7))

HTH. Dave

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave