I've been using Excel for years just as a basic spreadsheet for medical billing. I've recently been looking into all that Excel can do to make spreadsheets less cumbersome, such as using its ability to use databases/tables. It's my hope to build one to save on my budget instead for having to pay for any expensive service. What I can't seem to find out is if there is a way to make it searchable. For instance, patient John Doe calls me with a question about his claim so I need a way to access his information quickly. Is this something that's possible? Thanks for any suggestions.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Is This Possible? (Beginner Question)
0
Discussion
Hi and welcome. There are many ways Excel can be used to do that, from simple worksheet functions (where you enter a detail like date of birth into cell to see matching cells, like the tutorial SEARCH() vs FIND() Function in Excel) to more complex searches (like VLOOKUP and INDEX/MATCH) which can return several values to creating custom forms in Excel's programming langauage VBA where you could enter what's known and get full results back in that form. Much depends on how your information is stored.
I appreciate that you can't share patient details but please edit your original question above to:
1. change its title to something more descriptive, like "Using Excel to search for records?" - other users would find that more helpful if they have a similar problem to yours
2. add an Excel file containing some sample (or anonymous) data. As little as 10 records might be enough.
3. say in the body of the question what kind of information you'd expect a patient give you to obtain their records e.g. data of birth, Zip code or postcode, claim number).
This will help me and others who might answer (and those who later seek answers).
Thanks in advance.
I appreciate that you can't share patient details but please edit your original question above to:
1. change its title to something more descriptive, like "Using Excel to search for records?" - other users would find that more helpful if they have a similar problem to yours
2. add an Excel file containing some sample (or anonymous) data. As little as 10 records might be enough.
3. say in the body of the question what kind of information you'd expect a patient give you to obtain their records e.g. data of birth, Zip code or postcode, claim number).
This will help me and others who might answer (and those who later seek answers).
Thanks in advance.
John_Ru (rep: 6142) Dec 20, '20 at 5:12 am
Answers
0
Imagine a table of all your bills: Patient, Date, Bill No., Amount.
With a few clicks you can filter that table to show only the bills to John Doe. With a few more clicks you can filter away all fills prior to a date, say July 1, 2020.
Imagine a table of all your receipts: Patient, Bill No., Date, Amount, Bank.
With a few clicks you might filter out all receipts from John Doe. With a few more clicks you could limit the display to receipts after the date of the bill you are interested in, say August 23, 2020.
You might add a column to your first table, call it Paid. With a simple formula you could reference the second table fully automatic and display "Paid" against all bills once they appear in the second table.
There is a simple rule for what you can do: If you can think of it, it can probably be done. Limits only apply to the amount of effort you may want to invest.
Discussion
@Variatus-- Good point, filtering is a neat approach (provided/easiest if the data is on one sheet). I'd forgotten that method in my earlier response (now changed to a discussion point, not an Answer as previously).
I'd guessed from the question (perhaps incorrectly) that the data was in more than one worksheet/workbook. Hopefully this will be clarified when a sample file is provided.
I'd guessed from the question (perhaps incorrectly) that the data was in more than one worksheet/workbook. Hopefully this will be clarified when a sample file is provided.
John_Ru (rep: 6142) Dec 20, '20 at 5:19 am
0
To filter data:
- Begin with a worksheet that identifies each column using a header row.
- Select the Data tab, then locate the Sort & Filter, group.
- Click the Filter command.
- Drop-down arrows will appear in the header of each column.
- Click the drop-down arrow for the column you want to filter. In this example, we'll filter the Type column to view only certain types of equipment.
- The Filter menu appears.
- Uncheck the boxes next to the data you don't want to view, or uncheck the box next to Select All to quickly uncheck all.
- Check the boxes next to the data you do want to view. In this example, we'll check the Laptop and Projector to view only these types of equipment.
- Click OK. All other data will be filtered or temporarily hidden. Only laptops and projectors will be visible.
Hope this helps.
Regards,
Jerry
Discussion
Jerry, your answer is a good guide to basic filtering but would be improved if you edited it to first explain that the example relates to types of equipment (laptops, projectors, whatever). I was slightly confused when I rushed through it (unlike the answer from Variatus which was created to relate to the user's situation involving patients and billing).
John_Ru (rep: 6142) Dec 29, '20 at 4:12 am
0
Your need for a patient's record "database" is something that Excel can fulfill quite effectively. (If I knew you I'd design an application for you). Type in "excel medical patients worksheet" into your search engine and you'll find a number of (free) templates that should do the job. Of course you can make any additions or adjusts to a worksheet as needed. Best of luck in this effort!