Help with Formula

[l_d18]

Hi, 

   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 

[#2]

@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.

[#3]

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. 

Many Thanks, 

Laura

[#4]

Hi Laura @l_d18 ,

I too am convinced that pivot tables are the way to go.

My advise would be to have a look at some introductury videos about pivot tables at Youtube.

https://www.youtube.com/watch?v=qu-AK0Hv0b4 for instance, but there are far more.

Kind regards

Hans

[#5]

@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.

[#6]

Hi @Riny_van_Eekelen 

   Thank you so much for your help. I will do as suggested and watch a few clips to educate myself and then put this in place. 

Best Wishes, 

l_d18

[#7]

Hi @Hans Le Roy 

   Thank you so much for your reply. I will have a look at some videos and see if I can get my head around it. 

Many Thanks 

[#8]

@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.

[#9]

an updated version is attached in which the #NA error will not be displayed for the extra rows.

(You may want to study how these formulas work.)

[#10]

(You may want to study how these formulas work.)