Transfer data from one sheet to another automatically [Solved/Closed]

myrna1310 - Mar 24, 2015 at 02:08 PM - Latest reply: vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen
- Apr 14, 2015 at 05:50 PM
Hello,

I would like to populate data on a spreadsheet based on data obtained from another. Is this possible? If so, how can I do this?
Example: Sheet 1 contains the patients I see on a daily basis and all their information: name, date of birth, medical record number, reason for visit, diagnosis, etc. (they are listed by date)
On sheet 2, I would like to see how many times each individual patient has come in for the same complaint, and if possible, on what dates.
Any help would be extremely appreciated!

See more 

19 replies

vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Mar 26, 2015 at 12:37 AM
0
Thank you
Hello Myrna,

I just need to clarify the headings that you want to use as there are, for example, in your post, some in sheet 3 that are not in sheet 1. Also, in sheet 1, you refer to "Home Health Referral" and "Home Health Completed" and in sheet 2 "Referred to Home Care" and "Home Care Completed". Are these supposed to be the same items? (I have made them the same items in the table below as there does need to be consistency for any code to execute correctly. Please advise if this is not correct). To extract the data from sheet 1 to the other sheets, all the required headings for the various activities need to be in sheet 1, which I have assumed will be your "Input" sheet. I've just re-arranged them as follows:-

  Sheet 1                    Sheet 2              Sheet 3
Patient Name Patient Name Asthmatics Seen
Date Seen Date Seen Asthma Ed.
DOB DOB Sick Calls(#)
Age MH/AH/Athena# ED Visits(#)
MH/AH/ Athena # Reason For Visit Direct Admits
Dx Last Clinic Visit ED Referrals
Reason for visit Last ED Visit ED Referrals Seen
Last Clinic Visit Last H'lisation In Patient Referral
Last ED Visit
ED Visits(#) Sick Calls(#) In Patient Referrals Seen
Last H'lisation Rx Calls(#) Referred to Home Care
Direct Admits Provider Home Care Completed
ED Referral ED Referral
ED Referrals Seen Referred to Home Care
Asthmatics Seen Home Care Completed
Asthma Ed.
Other Education
Sick Calls(#)
Rx Calls(#)
Notes
Provider
PCP
Home Contact Info
F/U Call
In Patient Referral
In Patient Referrals Seen
Referred to Home Care
Home Care Completed


Please advise if this is how you were thinking of setting out the three spread sheets.

I would also recommend creating a unique Patient ID (numeric or alpha numeric) which would simplify data transfer rather than use a Patient's name as you may have, for example, five Bob Jones on your list which would obviously then cause confusion with the data transfer.

Let us know your thoughts.

Kind regards,
vcoolio.
Yes, you are correct in that sheet 1 would be the input sheet. The changes you made on Home Care Referral and Home Care Completion is ok. They are the same item on those sheets. Thank you for that clarification.

From what you sent me, the only thing missing in sheet 2 is Inpatient Referral. Otherwise it looks good! I wanted to use sheet 2 as a individual patient look up for me when I need individual information. The MH/AH/Athena# is the patient ID that we use. Would I have the ability to look the patient up by both patient or ID # or would it have to be one or the other?

I wanted to use sheet 3 as a monthly report, for example:
In sheet 1 under Dx, the possible entries are Asthma and Sick Call. I wanted to be able to pull a number for Sheet 3 for the number of times these occurred on a monthly basis. For example:
Sheet 1 Sheet 3
patient1 Asthmatics Seen 3
dx asthma Sick Calls (#) 1

patient 2
dx asthma

patient 3
dx asthma

patient 4
dx sick call

As I look through it, we can delete Direct admits.
Maybe what I'm looking for is more of a filter for certain things to show up on Sheet 2 and 3. Does that make sense?
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Mar 27, 2015 at 12:32 AM
0
Thank you
Hello Myrna,

I've put a little something together in a test work book for you. You can have a look at it here:-

https://www.dropbox.com/s/5gp1ys3hzt0pbb3/Myrna.xlsm?dl=0

As you can see, the Patient Data is transferred from the "Input" sheet to the "Patient Summary" sheet (you can rename them at any time) by clicking on the Transfer Patient Data button. An input box pops up and in this you place the Patient ID (MH/AH/Athena#) then click OK. To keep things as simple as possible, the transfer of data is based just on the Patient ID as the criteria as this would be unique to each patient. Using an input box will ensure that the correct Patient details are transferred. Do you want the individual Patient data cleared from the "Input" sheet once the data is transferred?

As the dataset grows in the "Patient Summary" sheet, you can use the inbuilt Excel filter function to easily find information for any Patient. High-light the column(s) you wish to filter by, go to the Data Tab--->Sort & Filter Group--->select Filter. You will see small boxes with little down arrows in them in the selected column(s). Click in the box and you will from there be able to filter any details you wish to refer to.

The test work book is not complete as I would like to know your thoughts on how exactly you would like to tackle sheet 3.You can experiment in the test work book in the above link to see how you would like sheet 3 set out. I was thinking that, since you would like a monthly report, perhaps from sheet 3 onwards we could create a sheet for each month of the year (I'm sure that the data in each of these monthly sheets would be extensive!). You could then save the whole work book as a template and use it year after year, modifying/improving it as you wish as time goes by.

I look forward to receiving your thoughts and ideas on this.

Cheerio,
vcoolio.
For sheet 1, I have decided to separate MH/AH/Athena# into a column for each. These are 3 different ID numbers available for each patient, and I would like to look them up by any of those three.

For sheet 2, I think I will simply use the filter option on sheet 1 to look up individual patient information, and we can delete sheet 2.

For sheet 3, I really like your idea to use it as a template to be used year after year! I would like the report to show up as number only that come from the data in sheet 1.
For example:
Jan Feb March April May June July Aug Sep Oct Nov Dec
Asthmatics
Seen 45
Asthma Education 31
Sick Calls 11
Ed Visits 1
Direct admits 0
Ed Referrals 10
Ed Referrals Seen 8
In Patient Referral 3
In Patient Referrals
Seen 2
Referred to Home
Care 20
Home Care
Completion 17

The problem I have in sheet 3 is that, as you mentioned earlier, I don't have some of these fields listed in sheet one. What I was wanting, is the following:
Asthmatics seen to be populated from the "Dx" column in sheet 1. Every time I enter "Asthma" in sheet 1 under the "Dx" column, it should generate a count of one in Sheet 3 under "Asthmatics Seen", and continue to sum this up monthly. Same thing for, Sick calls #, I would like a number generated in sheet 3 every time I enter "Sick Call" under "Reason for visit" in sheet 1. As for # of ED visits, I'm rethinking that field, so for now we can just leave it out.

I know this all sounds complicated, and I don't even know if it's possible. I'll wait to hear your response. Thank you soooo much for all the help!
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Mar 28, 2015 at 09:05 AM
0
Thank you
Hello Myrna,

Good idea to forget sheet 2. It was basically just going to be a copy of sheet 1 anyway.

Well, to simplify things a little, I've made some changes to the original work book set out and moved away from macros and gone to formulae instead.

If you have a look at my latest sample:-

https://www.dropbox.com/s/o94s2fd5usc245a/Myrna%27s%20Template.xlsx?dl=0

you will see that I've created a work book with twelve month tabs and a Summary tab. This can be used as a template for continuous yearly use.

In the Summary sheet, you'll see the headings that you want to summarise as per your last post but this time they are arranged vertically with the months across the top horizontally. I've only done a little formulating just to give you an idea of how the Summary sheet works in conjunction with the monthly sheets. To test it yourself, just add some more text to the rows I've already placed some in (horizontally) and see how the values change in the Summary sheet. I've also added a summary at the bottom of the dataset in each month sheet which will act as a check and for the Summary Sheet to draw its values from.

You can filter on Column A in any sheet to quickly access any information that you may need.

A few of questions for you:-

1) In the Dx row, will "Asthma" be the only word that is typed into this row, cell by cell?
2) For all the other rows in the month sheets (only the ones that need to be summarised in the Summary sheet), which will be text and which will be numeric?
3)If they are text, what text do you intend to use?

I hope that we are closer to the required result this time!

Cheerio,
vcoolio.
It looks great! Thank you so much! I am not able to test it for some reason. Do I need to have a dropbox account?
1) In the Dx row, Asthma will not be the only word that is typed in.
2) If I understand the question, they should all be numerical.
Please let me know if you have any further questions.
I was able to figure out how to test it, and I think I understand your question better.

The following will be entered as text (the options will be "yes" or "no"), and need to be summarized as numerical:
Ed Referral
In patient referral
Referred to home care

It was my intention for the summary sheet was as follows for the following fields:
1) "ED referrals seen" (1 numerical value on summary sheet) to be generated by every "yes" answer under the "Ed Referral" row in the monthly sheet.
2) "In patient referrals seen" (1 numerical value on summary sheet) to be generated by every "yes" answer under the "In patient referrals" row in the monthly sheet
3) "Home Care Completed" (1 numerical value on summary sheet) to be generated by every "yes" in the "Home Care completed" row in the monthly sheet.


Again, please let me know if you have any further questions.
Thanks!
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Mar 31, 2015 at 01:29 AM
0
Thank you
Hello Myrna,

If you have a look at the test work book again:-

https://www.dropbox.com/s/o94s2fd5usc245a/Myrna%27s%20Template.xlsx?dl=0

you'll see that I've covered the items from your last post. Have a play with it and see what you think.

For rows 33 and 35 (Asthmatics Seen and Sick Calls) in the month sheets, I've changed the formula to a COUNTA() formula. This will count any text in rows 8 and 9 (Dx and Reason for Visit) and will show up in the Summary just as a numeric value.

I have formulated just the first three months in the test work book just so you have an idea of how it works.

What about the remaining six rows in the Summary(Asthma Education, ED Visits, Direct Admits, ED Referrals, In Patient Referral, Referred to Home Care)? Do these need a tally beside them or is it all OK as it is? I think you said earlier that you didn't need the ED Visits row.

Let me know how it goes.

Cheerio,
vcoolio.
For rows 8 and 9, the only text counted in rows 33 and 35 should be the specific text "asthma" and "sick call". It should exclude any other text from the count.

For the remaining rows, it should be as follows:
Asthma Education: texts saying "yes" should be counted. all others excluded

ED visits: ok as is

Direct admits: Can we add a row for this at the end? Maybe row 30? And then have text saying "yes" be counted. Any other text should be excluded from the count.

ED Referrals, In Patient Referrals, and Referred to Home Care: any text "yes" should be counted. Any other text should be excluded.

Thank you!!!
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Apr 1, 2015 at 10:02 PM
0
Thank you
Hello Myrna,

I think that its all covered now:-

https://www.dropbox.com/s/nef949igba34d4b/Myrna%27s%20Template.xlsm?dl=0

I've added an "Individual Info" sheet to the work book just in case you want to look at an individual Patient's details separately (or you can still use the filter for this purpose). Just click on the "Individual Info" button which you will find on each sheet, an input box will pop up, place a Patient's ID number (any of AH#, MH# or Athena# : it will work with any of these) and you will be taken directly to the "Individual Info" sheet showing the particular Patient's details. If you don't need it, then it can be deleted.

Let me know what you think and I'll then help you with setting it up.

Cheerio,
vcoolio.
WOW!! This is EXACTLY what I wanted!!! Especially the "individual info sheet". I didn't even know if any of it was possible. I can't thank you enough for your time and help! This will really help organize all the data as the number of our patients grows.
I tried copying and pasting all the data I have from another excel spreadsheet into the one you made for me, but I get an error message. Will I have to type everything in?

Also, in the monthly sheets, can the age be calculated from the DOB against today's date on every column?
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Apr 3, 2015 at 04:07 AM
0
Thank you
Hello Myrna,

The bad news............yes, you'll have to enter the data from scratch. Its a new Work Book so, to save any more headaches with errors, do it this way. It will all be worth it in the end. Perhaps you have a Junior Assistant who may have some time to spare during a day (he/she may well ask for a pay rise)! Start any new entries from the present and catch the others up.

I have uploaded a new, blank work book for you ready to use.

https://www.dropbox.com/s/nef949igba34d4b/Myrna%27s%20Template.xlsm?dl=0

Download it to your Desk Top, or wherever you want to save it, rename it to suit yourself, and you'll have a new template to use year by year. When you first use it, enter the current year in cell A1 in the "Summary" sheet, "Save As" the work book to whichever folder you want and after that, just "Save" each time you close the current year work book. The template will then remain empty ready for the next year and so on.

The Patient's age will now self calculate in each column of each monthly sheet (Row 5). I have high-lighted Row 5 in each monthly sheet in yellow more to warn you not to delete, change or clear a Patient's age directly from the relevant cell because if you do so, the formula that calculates the age will be deleted. If you need to change or clear the age, do so by changing or clearing the DOB in the row (Row 4) above it.

To preserve all the formulae, please remember:-

- Do not clear any data directly from the "Summary" sheet or from the summaries at the bottom of each monthly sheet. To clear any data from the "Summary" sheet or the other summaries, do so from the monthly sheets only (Rows 2 - 31) except, of course, from Row 5 (Age).

The Patient data in the "Individual Info" sheet will be overwritten each time you select a new Patient's data to be analysed.

Good luck with it all.

Cheerio,
vcoolio.
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Apr 13, 2015 at 11:52 PM
0
Thank you
Hello Myrna,

Just wondering if it has all fallen into place for you.

Cheerio,
vcoolio.
Hello,
I have not been able to use it since I have been out sick. I will test it as soon as I am able. Thank you for your help!
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen > myrna1310 - Apr 14, 2015 at 05:50 PM
Get better soon, Myrna.
BTW, who looks after a sick Doctor?
Glad I was able to help.

Cheerio,
vcoolio.
vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - Mar 24, 2015 at 06:39 PM
-1
Thank you
Hello Myrna,

Earlier this month, I helped another Doctor with a similar query to yours.
Here is the link to that thread:-

http://ccm.net/forum/affich-798305-transferring-information-from-previous-spreadsheet

Let us know if its at least similar to what you are wanting. I'm sure that we could modify it to suit you if need be.

Cheerio,
vcoolio.
It seems similar. I can be more specific...These are the fields/categories I collect data for on sheet 1 (on the x axis). The information is organized by date of visit on the y axis.
Patient Name
Date Seen
DOB
Age
MH/AH/ Athena #
Dx
Reason for visit
Last Clinic Visit
Last ED Visit
Last Hospitalization
Asthma Education
Other Education
Sick Call
Rx Call
Notes
Provider
PCP
Home Contact Info
F/U Call
Home Health Referral
Home Health Completed

I would like to automatically pull information from here to sheet 2 and sheet 3.
For sheet 2, I need the following information for each individual patient only when typing in the patient name (looking up that information by patient one at a time). For example, if I type in a patient name under that field, for the rest of the information to appear.
Patient Name
DOB
MH/AH/Athena #
Date Seen
Reason for Visit
Last Clinic Visit
Last ED Visit
Last Hospitalization
Sick Calls (#)
Rx Calls (#)
Provider
Ed Referral (Y/N)
Inpatient Referral (Y/N)
Referred to Home Care (Y/N)
Home Care completed (Y/N)

In sheet 3, I need to compile the number of ________ per month from sheet 1.
Ex:
Asthmatics Seen (2yrs or older) 20
Asthma Education 18
Sick Calls 10
Ed Visits 1
Direct admits 0
Ed Referrals 4
Ed Referrals Seen 3
In Patient Referral 1
In Patient Referrals Seen 1
Referred to Home Care 9
Home Care Completion 5