• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filtering random data from record based on recent due date

N

Nicki

Member
Hi
Please could you help me with auto filtering random data showed on "Caseload Overview" tab (column A-G) taken from "Patient Tracking"(file attached). I hope i have explained it properly. Many Thanks
 

Attachments

  • Tracking List.xlsm
    44.8 KB · Views: 2
Hi,

Do your "Caseload Overview" show "Active" record only,
or "Active"+"Deceased"+"Inactive" altogether ?

Regards
Bosco
 
Last edited:
Hi,

1] In "Patient Tracking" sheet I added a helper in Col. U for "Follow-up nos.", and few comments for checking purpose.

2] See attached file

Regards
Bosco
 

Attachments

  • Tracking List(1).xlsx
    37 KB · Views: 2
It is fab Bosco. However could I ask for a minor change in filtering please. Sorry my mistake in explaining earlier. I need to see only people with the most recent follow up date (up to the next 30 days from today's date). Now I can see all patient name with the most recent follow up date. I want to see the most recent follow up date patient. I hope that does make sense please ask if I am not clear enough.
Many thanks

see attached file
 
Last edited:
Please see attached file. I see all patient with their most recent follow up, I need to see only the most recent follow up date up to 30 days.
 

Attachments

  • Tracking List 2.xlsx
    217.3 KB · Views: 3
Please see attached file the layout shown in sheet1.

the most recent follow up date up to 30 days ( example if I am looking at the overview today 17/11/2017, I'd like to see the patient appointment from today up to 17/12/2017. I donot want to see those who have appointment after 17/12/2017 or before 17/11/2017 on overview page. Please ask if it is not clear. in Patient data tab all date are highlighted in Pink needs to be shown up in Overview tab automatically.
Many Thanks
 

Attachments

  • Tracking List 2.xlsx
    54.1 KB · Views: 2
Nicki said:
Please see attached file the layout shown in sheet1.

the most recent follow up date up to 30 days ( example if I am looking at the overview today 17/11/2017, I'd like to see the patient appointment from today up to 17/12/2017. I donot want to see those who have appointment after 17/12/2017 or before 17/11/2017 on overview page. Please ask if it is not clear. in Patient data tab all date are highlighted in Pink needs to be shown up in Overview tab automatically.
Many Thanks
Click to expand...
1] "Most Recent Follow-up Due Date", in Sheet1 F5, copied down:

=IFERROR(AGGREGATE(14,6,Overview!G$5:G$26/(TODAY()<=Overview!G$5:G$26)/(EDATE(TODAY(),1)>=Overview!G$5:G$26),ROWS($1:1)),"")

2] Suggest in Patient Data Sheet, Column R "Date Follow-up Due",

Your formula >>

=IF(Q4="1-month follow-up ",IF(J3=""," ",IF(ISERROR(J3+30)," ",J3+30)),IF(Q4="6-month follow-up ",IF(J3=""," ",IF(ISERROR(J3+180)," ",J3+180)),IF(Q4="12-month follow-up ",IF(J3=""," ",IF(ISERROR(J3+360)," ",J3+360)))))

Changed to this (See Column X) >>

=IF(Q4="","",EDATE(LOOKUP(9^9,J$3:J4),LEFT(Q4,FIND("-",Q4)-1)))

3] See attached file

Regards
Bosco
 

Attachments

  • Tracking List 2(a1).xlsx
    57.9 KB · Views: 6
Thanks Bosco it is brilliant. Thanks for advising on excellent formula on Follow up due date. It is way more smarter than the long formula I had used :)
 
Sorry Bosco, Sheet1 Formula it is great but I want to get the result in sheet one from Patient data not from Overview, Ignore overview tab or delete it, then apply the formula between Patient data and Sheet 1. to get the same result. Many Thanks
 
Nicki said:
Sorry Bosco, Sheet1 Formula it is great but I want to get the result in sheet one from Patient data not from Overview, Ignore overview tab or delete it, then apply the formula between Patient data and Sheet 1. to get the same result. Many Thanks
Click to expand...

See revised file as per attachment.

Regards
Bosco
 

Attachments

  • Tracking List 2(b).xlsx
    44.1 KB · Views: 3
You must log in or register to reply here.
Back
Top