Need help determing functions, programming needed for Worksheet (to help a cancer patient)

[carolbien]

First of all, thank you in advance for your help with the four questions I have below! Background: I am trying to help a doctor communicate to a cancer patient about her medications, and for the patient to have a worksheet to check off all the meds she is supposed to take each time of day and how she is supposed to. I am using Excel 2010.

I was given a Worksheet (#1 below) that the doctor created. The "Doctor's Worksheet" containing Names of Medications/Number of Pills To Take At Which Times/Food Conditions. Now, I need help constructing the Worksheet (#2 see below) for the patient to print out a list of their daily meds and check of what they take.

**Worksheet 2 must be linked to Worksheet 1 values, so when doctor adds/deletes/changes a medication or its instructions, data on Worksheet 2 AUTOUPDATES. I cannot rely on the patient to utilize a filter function.***

I know how to paste a link into Worksheet 2, but there's some additional "programming" that's needed in Worksheet 2 to make it easier for the patient to understand. That's what I don't know how to do.

Here's what I need to figure out for Worksheet 2, the Patient's "Daily Meds Sheet":

1) If Sheet1's field B2 (Morning Med) > 0 (meaning they have to take some of that med in the morning), then we want Sheet1's A2 field (Product Name) value to appear in Sheet 2's A2 (the "Morning Med Names" column)

That way the patient will know they have to get that specific pill bottle out. 2) Then, if such a value appears in Sheet2's A2 (Morning Med Name), we need to pull Sheet1's B2 (Morning Med) numeric value into Sheet2's B2 cell (No.). That will tell the patient how many pills of that medication they need to take at that time.

3) Similarly, if there is a value in Sheet2's A2 (Morning Med Name), we need Sheet1's C2 (Food) text value to appear in Sheet2's C2 (Food).

4) In order to help the patient to take meds that need to be taken without food first, we need Column C (Food) sorted by custom values (N, F, W, Y) (these stand for No Food, Fatty Food, With Or Without Food, Yes Food).

The sorting of Column C should affect the reorganization of only Columns A&B (since there are columns associated with times of day). This is basically the programming that I need help figuring out how to do--for all the cells in a column until Row 150. The programming repeats for the Afternoon Meds (Sheet1 Columns D&E; Sheet2 Columns E-H), Evening meds (Sheet1 Columns F&G; Sheet2 Columns I-L), etc. The last two times of day (B4 Bed Meds and Anytime Meds) don't have a Food column associated with them. Column D, H, L, O, and R (Taken?) is simply a blank column for the patient to fill in with X's to keep track of what meds have been taken (no programming necessary) Could you kindly help me figure out what functions I need to use, what programming I need to do, or offer a template/script I can plug my values into? I really appreciate the assistance, and so will this cancer patient and her doctor! We can use this for other patients if we are successful. Thank you, Carol

<tbody>
</tbody>

Doctor's Sheet1

	A	B	C	D	E	F	G	H	I
1	Product name	Morning Med	Food	Afternoon Med	Food	Evening Med	Food	B4 Bed Med	Anytime Med
2	Air Power	1	Y	0	Y	1	Y	1	0
3	Alfacalcidol 4mcg	0	W	2	F	0	W	4	0
4	Amantadine	0	W	1	N	0	W	0	0

<tbody>
</tbody>

Patient's DailyMeds Sheet2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Morning Med Name

No.

Food

Taken?

Afternoon Med Name

No.

Food

Taken?

Evening Med Name

No.

Food

Taken?

B4 Bed Med Name

No.

Taken?

Anytime Med Name

No.

Taken?

2

3

4

<tbody>
</tbody>

In case it's helpful to providing me a script or template for all of the Columns, here is the detailed requirement for the Afternoon Meds.

If Sheet1's field D2 (Afternoon Med >0, then we want Sheet1's A2 value (Product Name) to appear in Sheet2's E2 (the "Afternoon Med Names" column)

Then, if there is a value in Sheet2's D2 (Afternoon Med Name), we need Sheet1's D2 (Number) value to appear In Sheet2's F2 (Number To Take)

If there is a value in Sheet2's D2 (Afternoon Med Name), we need Sheet1's E2 (Food) value to appear in Sheet2's G2 (Food)

Column G(Food) needs to be sorted by custom values (N, F, W, Y) and the sorting should affect only Columns E&F values (Afternoon Med Name, Number To Take).

<tbody>
</tbody>

THANK YOU!

 

[mumps]

Try this code:

Sub CopyData()
    Application.ScreenUpdating = False
    Sheets("Sheet2").UsedRange.Offset(1, 0).ClearContents
    Dim x As Long
    For x = 2 To 150
        If Cells(x, "B") > 0 Then
            Cells(x, "A").Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Cells(x, "B").Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            Cells(x, "C").Copy Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        End If
        
        If Cells(x, "D") > 0 Then
            Cells(x, "A").Copy Sheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
            Cells(x, "D").Copy Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
            Cells(x, "E").Copy Sheets("Sheet2").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
        End If
        
        If Cells(x, "F") > 0 Then
            Cells(x, "A").Copy Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0)
            Cells(x, "F").Copy Sheets("Sheet2").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
            Cells(x, "G").Copy Sheets("Sheet2").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
        End If
        
        If Cells(x, "H") > 0 Then
            Cells(x, "A").Copy Sheets("Sheet2").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0)
            Cells(x, "H").Copy Sheets("Sheet2").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0)
        End If
        
        If Cells(x, "I") > 0 Then
            Cells(x, "A").Copy Sheets("Sheet2").Cells(Rows.Count, "P").End(xlUp).Offset(1, 0)
            Cells(x, "I").Copy Sheets("Sheet2").Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[carolbien]

Thank you Mumps! That, and all the other work suggestions you made, ended up working out beautifully! Very much appreciated.