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

C

carolbien

New Member
Joined
Aug 7, 2013
Messages
2
[TABLE="width: 624"]
<tbody>[TR]
[TD]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.[/TD]
[/TR]
[TR]
[TD]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.[/TD]
[/TR]
[TR]
[TD]**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.***[/TD]
[/TR]
[TR]
[TD]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. [/TD]
[/TR]
[TR]
[TD]Here's what I need to figure out for Worksheet 2, the Patient's "Daily Meds Sheet":[/TD]
[/TR]
[TR]
[TD]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)[/TD]
[/TR]
[TR]
[TD]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.[/TD]
[/TR]
[TR]
[TD]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).[/TD]
[/TR]
[TR]
[TD]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).[/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]

Doctor's Sheet1
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product name[/TD]
[TD="align: center"]Morning Med[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Afternoon Med[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Evening Med[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]B4 Bed Med[/TD]
[TD="align: center"]Anytime Med[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Air Power[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Alfacalcidol 4mcg[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Amantadine[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

Patient's DailyMeds Sheet2

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Morning Med Name[/TD]
[TD="align: center"]No.[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Taken?[/TD]
[TD="align: center"]Afternoon Med Name[/TD]
[TD="align: center"]No.[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Taken?[/TD]
[TD="align: center"]Evening Med Name[/TD]
[TD="align: center"]No.[/TD]
[TD="align: center"]Food[/TD]
[TD="align: center"]Taken?[/TD]
[TD="align: center"]B4 Bed Med Name[/TD]
[TD="align: center"]No.[/TD]
[TD="align: center"]Taken?[/TD]
[TD="align: center"]Anytime Med Name[/TD]
[TD="align: center"]No.[/TD]
[TD="align: center"]Taken?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 624"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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.[/TD]
[/TR]
[TR]
[TD]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)[/TD]
[/TR]
[TR]
[TD]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)[/TD]
[/TR]
[TR]
[TD]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)[/TD]
[/TR]
[TR]
[TD]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).[/TD]
[/TR]
</tbody>[/TABLE]
THANK YOU!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try this code:
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
 
Upvote 0
Thank you Mumps! That, and all the other work suggestions you made, ended up working out beautifully! Very much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ottsel
  • Solved
VBA: Custom Count and Search Function Help Needed
Replies
5
Views
501
Ottsel
Ottsel
Jambi46n2
  • Question Question
VBA Nested Loop Help!
Replies
1
Views
379
Jambi46n2
Jambi46n2
A
  • Question Question
Guys please help! Excel problem with index sort and filter function
Replies
2
Views
639
Adfassa
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Help with macros to open many files, and extract data. Need help with match / find.
Replies
9
Views
1K
johnnyL
J

Forum statistics

Threads
1,223,228
Messages
6,170,876
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top