Forum Discussion
Trying to create a patient-tracker spreadsheet (or database)
Hi,
I have a small spreadsheet with all of my patients but am struggling to set up a more efficient set of sheets and possibly a dashboard, rather than it just be a simple and fairly ineffective list.
In my main sheet I have the list to which I add every new patient I see (Name, DoB, ID number) and details of their cancer (site, stage, type etc) and then their first treatment (date seen, radiotherapy or chemo or immunotherapy).
Because patients often have more than one type of treatment at various times and in various sequences to record all the important events in one sheet is messy at best. I'd like to set up separate worksheets for each of the treatment types so that I can gather the relevant data, but would like the records to be linked to save duplication of typing (as I type this I increasingly see this needs to be a relational database I think..) and to try and keep one unified set of data.
Is it possible to set up a rule so that as a I add a new patient to the main list, and then tick the treatment boxes 'chemo' and 'radiotherapy', that the basic demographics would then be copied into a new line in the chemo and radiotherapy sheets automatically?
I have access to Access but it's beyond me I think. Similarly I've had a quick look in PowerApps and not sure I've got anything like the skills for that! Would it be feasible to do this in Excel with vlookup or other rules?
In terms of scale this would be relatively few rows (60 so far, around 200 patients a year) but a lot of columns to record the various events of interest.
I've put together a very small dummy book to try and illustrate what I'm getting at.
Thanks for any advice!
The FILTER function may be what you're looking for. I've added it to two of your detailed treatment sheets here in this. Here's what the formula for the Radiotherapy group looks like:
=FILTER(Table1[[Name]:[Number]],Table1[RT]="Yes","None")
Now, this will continue to add new patients for whom a "Yes" is entered in the RT column. You can set up similar criteria for the chemo group, and so forth. The problem you might encounter--more likely "will encounter"--is if you start giving radiotherapy to a patient high on the sequence of the core Patient List, and they interfere with the sequence already established. That's where you're right to think that a full relational database is more likely to meet your need. Unless you can come up with a sort of some kind that will resolve that.
Another possibility that occurs to me is to have your two basic tables
- Patient, ID, Treatment types (as you have them now)...
- Transactional history: ID, date, description of each treatment regardless of type, date, outcome,
Couple those two tables with the use of FILTER and UNIQUE and SORT to assemble a dashboard or two for high level summaries. This video might help you construct such an approach: https://www.youtube.com/watch?v=9I9DtFOVPIg
IF you have access to Power Query (I don't, in my Mac world), that too might work--others will have to help with that.
