Case note presentation

chris-in-cal

New Member
Joined
Dec 12, 2007
Messages
28
Platform
  1. Windows
Hello,

I would like suggestions on how to present case notes.

I have been given an excel worksheet with about 50 records on it. There are 50 clients who are referred from one group of people "OG counselors," to a second group of people "Support workers."
Every month the support works copy all of their text support notes for the month into a cell. Then after updating the worksheet with all the months text case notes they email it to me.
The spreadsheet is practically unreadable/unusable and the feedback I get is that the counselors do not even attempt to sift through it.

1) I don't want to ask the 'support workers' to do anything different, just keep sending their excel worksheet.
2) I am trying to learn how I can use excel to present the notes in an easy way to the counselors
3) I am considering that Access might be the tool I have to use, by feeding the monthly worksheet into it, but I have very little Access experience.

Suggestions?


OG counselorSupport workerClienttext notes
Sally counselor JonesRogerJohn client DoeThis is a very long case note containing several hundred characters that is copied from some other platform into this cell.
Susan counselor SmithSamJane client WongAnother very long and different case note containing several hundred characters that is copied from some other platform into this cell.
Alice counselor WilliamsRogerJack client HarrisBlah blah blah.....Another very long and different case note containing several hundred characters that is copied from some other platform into this cell. Another very long and different case note containing several hundred characters that is copied from some other platform into this cell.Another very long and different case note containing several hundred characters that is copied from some other platform into this cell.Another very long and different case note containing several hundred characters that is copied from some other platform into this cell.Another very long and different case note containing several hundred characters that is copied from some other platform into this cell. Blah blah blah....
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Chris. It's not quite clear how you want the data organized...by councilor, by support worker, by client, by anyone of these, or by something else? Maybe organize it as needed and sent it to Word for output? HTH. Dave
 
Upvote 0
Hi Chris. It's not quite clear how you want the data organized...by councilor, by support worker, by client, by anyone of these, or by something else? Maybe organize it as needed and sent it to Word for output? HTH. Dave
Thank @NdNoviceHlp

I want to send the counselor an easy to read copy of all the support notes that is sort by counselor. Each counselor should be able to read each of their clients and next to the client's name is the all of the text of what the support workers have been doing over the last month. If you were a counselor @NdNoviceHlp you would get a monthly report with

ndNoviceHlpClient name 1A whole months work of notes from their support worker, format in a way that is clear and easy to read.
ndNoviceHlpClient name 2A whole months work of notes from their support worker, format in a way that is clear and easy to read.
ndNoviceHlpClient name 3A whole months work of notes from their support worker, format in a way that is clear and easy to read.


I hadn't thought of exporting to Word. That might be best. I was thinking of creating reports in Access....but as it is pretty small number of record <100, Word might be best.
 
Upvote 0
So for each councilor, you could provide a Word document with the client name above a table indicating their interventions and then repeated for each client (ie. name & table). Is the whole month's interventions for one client included in 1 cell as U have laid out (ie. a 1 cell table) or can there be several interventions related to the same client? Dave
 
Upvote 0
So for each councilor, you could provide a Word document with the client name above a table indicating their interventions and then repeated for each client (ie. name & table). Is the whole month's interventions for one client included in 1 cell as U have laid out (ie. a 1 cell table) or can there be several interventions related to the same client? Dave
Great Dave. This is correct. Yes, each client has one cell with the whole months worth of text notes crammed into it.
A word doc with.
DAVE
client 1Notes
client 2Notes
client 3Notes

Yes, it could work. Automatically process the worksheet so it output a number of Word docs equal to the number of counselors.
Each Word doc would identify the counselor at the top, and have the list of associated clients with their notes next to them.

So, perhaps automatically sorting the worksheet by counselor, and having this exported into individual well formatted Word Doc, and most importantly the cell filed with text, goes into Word so it is easy to read.

Doing this with as little manual manipulation by me, is ideal. Thank you.
 
Upvote 0
I am becoming a little more clear on my problem here and possible solutions. Thanks again Dave.
An MS forum has a similar question and come of the answers were:

Q: "How to autopopulate data from Excel cells to Word document" - https://answers.microsoft.com/

A1: Mail Merge - https://gregmaxey.com
A2: VBA Script, How to Integrate Excel Data Into a Word Document - https://www.makeuseof.com

From what we discussed which route seems to be best for my small scenario?
 
Upvote 0
From XL, I was thinking more like creating a userform to list your councilors. You would then select a councilor and create a separate Word document for that councilor. Each document could have the councilor's name in the header and then list each client name above a 1 cell table of interventions and then repeated for each client..... just seems like there will be a lot of stuff to place in there. You can do all of this "on the fly" and create a new document each time. The other methods require coding from Word and/or a Word template document that U need to set up. A template is OK but it's static ie. only the same amount of info can go into it each time. I'm guessing most folks will recommend using mail merge and Greg Maxey is a guru of Word. So don't let me discourage you. I can kick around a solution if U want. Just need to know if my suggested process and format is OK? What your sheet name is? What columns the actual data are in (A-D?), Is there anything else in the sheet and where is it? Do the rows start in 2 (ie header)? Do you mind if the data is alphabetically sorted by councilor name? Also it will be important to know that councilor name are always spelled the same... or does the same person show up more than once due to misspelling? Dave
 
Upvote 0
  • From XL, I was thinking more like creating a userform to list your councilors. You would then select a councilor and create a separate Word document for that councilor. Each document could have the councilor's name in the header and then list each client name above a 1 cell table of interventions and then repeated for each client..... just seems like there will be a lot of stuff to place in there. You can do all of this "on the fly" and create a new document each time.
The "userform" method: in Excel create a UserForm. When the newest worksheet arrives each month I will 'select' an individual counselor, and have an output into an individual Word doc. I then repeat until all of the counselors have all of their Word doc "reports' created. Right?
  • The other methods require coding from Word and/or a Word template document that U need to set up. A template is OK but it's static ie. only the same amount of info can go into it each time.
The "Maxey" method is to create and code a MS Word template. Then somehow(VBA script?), the data from Excel will populate the word doc.
  • I'm guessing most folks will recommend using mail merge
The "Mail merge" is using basic Word tools, right? Import a worksheet, then run an MS Word Mail merge and output reports.


I can kick around a solution if U want. Just need to know if my suggested process and format is OK? What your sheet name is? What columns the actual data are in (A-D?), Is there anything else in the sheet and where is it? Do the rows start in 2 (ie header)? Do you mind if the data is alphabetically sorted by councilor name? Also it will be important to know that councilor name are always spelled the same... or does the same person show up more than once due to misspelling? Dave

Sheet name: "Monthly Tracker"

ClientCounselorSupport WorkerReferral DateCase Notes

Yes, there is a header, data starts in A2.

Alphabetical is fine.

(I made a typo with one of the counselors names :) ) They all have the same correct spelling of their names in all their records.

Thanks Dave.
 
Upvote 0
"The "userform" method: in Excel create a UserForm. When the newest worksheet arrives each month I will 'select' an individual counselor, and have an output into an individual Word doc. I then repeat until all of the counselors have all of their Word doc "reports' created. Right?"
Correct. It could be automated to make a separate document for each councilor all at once and then saved in separate files... this is different and much easier than having all councilors in 1 document
"The "Maxey" method is to create and code a MS Word template. Then somehow(VBA script?), the data from Excel will populate the word doc."
Correct. You need to set up the template and then code to place data where U want it ie. send to a bookmark
"The "Mail merge" is using basic Word tools, right? Import a worksheet, then run an MS Word Mail merge and output reports."
I believe U are correct. I don't really like Word very much and I have no experience with mail merge. So I can't say.
How XL savvy R U? Can U set up a master workbook with a userform having a listbox and a few command buttons? It seems like U would want to import each months data to the master wb which would contain the userform and the code to import and process the data. It would be very helpful if U could dummy up an example workbook and post it or perhaps message me and arrange to email it. This seems fairly doable but working with Word always has its' quirks. Dave
 
Upvote 0
> How XL savvy R U?
Computer Science drop out. On Mr. Excel for ten years., passed an intermediate excel class 20 years ago. I barely use it, but have been taking a Youtube intermediate class to get back up to speed.
I'm pretty committed to seeing this work. But....I will have many stupid questions, and will be slow.


>Can U set up a master workbook with a userform having a listbox and a few command buttons? It seems like U would want to import each months data to the master wb which would contain the userform and the code to import and process the data. It would be very helpful if U could dummy up an example workbook and post it or perhaps message me and arrange to email it. This seems fairly doable but working with Word always has its' quirks. Dave

I will work on creating a master workbook with a userform having a listbox and a few command buttons...and post in here in about a day or two. Thank you again Dave.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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