08-10-2020 07:01 AM
08-10-2020 07:01 AM
I was wondering if I could get some help with a formula. I am creating a database for patient care and trying to show when patients have completed their treatment and are going back to their local hospitals. I have 2 queries - the latter being an expansion of the first.
1) Is it possible in Excel to create a rule whereby if "Yes" is selected in a specified column, (as in something has been completed), then it will move that entire row of information into another sheet? e.g. Have patients transferred care
2) Is it possible to do the above but with more detail such as, if "yes" is selected and the patient's local site is selected as "QVH", then that row of information will be moved to the sheet labelled "QVH" . Trying to find a way of organising the information and making it more accessible.
All of the sites are listed in the sheet labelled dropdown menus, under hospital sites. I've attached how an example of the spreadsheet.
Any advice or support would be hugely appreciated. I really have been struggling with Excel
08-11-2020 12:35 AM
@l_d18 Rather than transferring data from one sheet to another, consider using pivot tables. In the attached workbook, I first transformed the master data into a structured table and named it "Patients". Then I created a pivot table containing all information from that master table (called "Patients"), with one filter for rows that have "Yes" in the Transferred Care column and another for the individual sites. Now, the neat thing with pivot tables is that you can create separate pivot tables (Show Report Filter Pages... under Options) for each of the sites. All will be based on the same master data, but with different filters applied. I've done that in the attached workbook as well. Update the master data (note that the table expands automatically if you add records, and you don't need to update the data source for any of the pivot tables). On the Data ribbon, press "Refresh All" and all your site tables will display the details for all patients with "Yes" in the transferred care column.
Let me know if this could work for you.
08-11-2020 01:29 AM
Hi @Riny_van_Eekelen ,
Thank you so much for your reply and for your help and suggestion. This does look a bit complicated to me (I'm not great with excel) and the person who will be in charge of the spreadsheet is even less familiar with Excel than I am, hence I was hoping for a formula that would sort the information out for us and separate out across different sheets. Do you know if this would be achievable at all?
The main database list will be continuously being added to and edited so I would like to keep the spreadsheet looking as simple as possible.
08-11-2020 01:51 AM
08-11-2020 02:09 AM
@l_d18 .... that's exactly what I had in mind. Keep it simple and easy to maintain. Using a structured table makes adding records a piece of cake. Pivot tables can be a bit overwhelming at first, but once you grasp what they can do, you probably will use them all the time. You mentioned that you want to move entire rows to other sheets if certain conditions are met. That requires programming in Visual Basic, which is much more challenging than using pivot tables. Your only other alternative is to manually copy rows from the master and paste them in the individual site sheets. But that is both laborious and prone to error. Your choice, though.
08-11-2020 12:13 PM
@l_d18 , Hello, with just formulas also it is possible to export the "Yes" & "YES+QVH" rows to a separate sheet. Refer attached file in which you can follow the steps written in callouts.
Similar to QVH example, i hope you would be able to make sheets for rest of the sites.
Yes, pivot tables have their strength of robustness and elegance.
But since you mentioned, so sharing this formula based method just as another possibility.
08-11-2020 12:42 PM
(You may want to study how these formulas work.)