Forum Discussion
Trying to create a patient-tracker spreadsheet (or database)
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.
