How can I filter an Excel sheet based on a column's inactivity

Are you looking for something like this?

http://excelexperts.com/VBA-Tips-Track-Changes-By-Adding-A-Time-Stamp

Hi tomfarrar, (or should I call you Tom)?

I don't think so. Let me explain what I am trying to do. I should have linked from another question. But, I wanted to start new.

So, basically, I am a doctor, and I want my referral specialist to use a spreadsheet to keep track of the referrals, i.e.

Patient | Phone | Consultant | Phone# | Etc. | Comments |

What happens it she will start the referral, say send a patient to a cardiologist. Send records and notes and await a referral date and time. Well, time will go by and she is supposed to call and check to see what the status is every seven days. BUT, OF COURSE, I PAY HER $20.00 AN HOUR to not do it.

I would be cool to be able to set the filter to seven days and then show every patient that has not been updated. Because had she gone in and checked within seven days or on day seven, then she would have added: Still waiting for approval, will call us with date and time within a week, need more records, etc.

Otherwise, out of say 75 referrals, two months will go by, the parent will call and say what is the deal with my referral to cardiology, she will call and find out they never even got the records or referral.

It would also be cool if I could click on 7 days or longer, and see if any updates have been made to the comments section. I think I will start here. And, then see if you have any questions. Or if you have a better way to do it. Not sure if I can upload an Excel sheet. I can always upload it to Box.com, if that makes it easier.

Thanks for your help.

Hi Doc - It would be to upload a sample file, and you can by using the attach file hyperlink at the lower left hand side of the comment box you use to post here at EE.

OK, will do. I have an IT person (MCITP) not a kid down the street who plays Ninetendo, lol, logged in remotely. As soon as he gets off, I will send the  file. I will email him now and see if he can email it to me.

Please call me Bert. :)

Referrals.xlsx

Some dummy data is what is needed really. What form are the comments in? Is it just Text, not cell comments?

If it's a comment made in the cell itself then you can simply filter for blanks

Well as I see it if the info is already on the spreadsheet, then you can you use a simple formula to calculate days passed.
For example if you need to just calculate the days from column Bx to today, add a column next to comments to do that (I named it days passed in the example):
O2        =TODAY()-B2, format to a number and add conditional formatting to get pink after X days
(in my example attached I have it PINK after 7 days)

If you need to check other variables, like Status = pending we could add an if statement in the calculation
P2          =IF(A2="pending";TODAY()-B2;0)

Check the sample I've attached and it may help you out if I understood what you were after :)
Referrals_example.xlsx

Thanks everyone,

Sorry, I have been away. I will look at these now. I see now how it would be difficult to do without data, but also, as Tom states, the data has to remain HIPAA compliant. I wish I knew a little about Excel. But, then I wouldn't be asking.

Thanks. Bear with me.

To answer Roy's question (this without looking at the example sheets, yet), it is text. You can't just filter on blanks, because it will continue to be updated:

08/02/16 Send progress notes and referral to Dr. Smith.
08/09/16 Have not heard back from Dr. Smith's office. I called and left message with receptionist
08/10/16 Received call from receptionist. Still waiting for Dr. Smith's approval.

Let's circle back to the original question:  If you are asking to know the last time the Comments column was updated (not worried about the other columns), I think a solution that tags a date of last update could be what you are looking for. Then another columns that would compare that date to the current date.   In the example data you showed 08/10/16 would be the important date for filtering.  For instance, if no other changes were made, this row would show up as 7 days old on 08/17/16.

If this is what you are looking for, I think the original link I provided, in conjunction with other Expert responses comparing date to current date,  would get you down the road to a solution.  Keep the conversation going.

Hi again,

Thanks again for all the comments. I always finish my questions, and I always try to grade appropriately. I have been away for a couple of days. I will post back in a day or so.

I really apologize. Everyone is helpful, and I feel like I have neglected you.

Referrals_Temp.xlsx

OK, I think it is best to get the sheet set up correctly with the proper add-in info working, before actually obtaining what I need to do. First, Tomfarrar knows more than I do about how to make the referral sheet, lol. So to hopefully answer some questions and ask some questions, I will comment by column. Unfortunately, there are a lot of dates. Referrals are one of the biggest headaches in medicine because 1) there are many to keep track of, 2) each consultant does it differently -- they tell us the date or they call the parent -- which we hate, because we are out of the loop, 3) some can be six to twelve months down the road and 4) the referral specialist is never as OCD and anal as the physician and just doesn't stay on top of things.

A. This should be either pending or complete
B. This should be the date the referral specialist for my office (RS) enters the initial information from the consult. The consult is ordered by me from the patient's record and is sent to the RS's inbox. That is the official documentation, but it ends up having 80 or 90 pending consults. It is not user friendly and other staff cannot access the data. So, even though it is double entry, I need her to enter it in the above format, so it is accessible, easy to follow, and she can't miss a step
C. Last name of patient
D. First name of patient
E. Best phone number to reach patient
F. The name of the consultant, which I hope will begin to self populate. It would be nice if it then automatically populated the phone and fax number of that doctor
G. Date records sent. Usually will be the same date as B. I have it where it can drop down to Sent or Non sent. I am trying to change that to date calendar, but I can't figure out how
H. This should be the name of the person at the referral office and the date the RS confirmed they received the records. This should be the following day to give the consultant time to get the records from their fax machine. THIS IS ONE OF THE TWO BIGGEST REASONS THE FORM HAD TO BE CREATED. My RS simply would not call to confirm they received the records. So, weeks go by, we call, and they say they never received the referral. She tends to think that once she sends the referral: progress notes, records, insurance information; that it is started. The other thing is we call sometimes, and they say, "Oh we don't see anyone under 12 yo." Well, that could have been confirmed the next day.
I. Phone number of consultant
J. Fax number of consultant
K. Date of referral
L. Time of referral (I don't know what happened, but when you enter the date and double-click in the cell, both the date and time shows up. The date goes away when you click in another cell or save. Certainly being able to do one column for date and time would be easiest
M. This is always tough. I want a date (again) when the patient was notified. This is another hugely important column
N. The comments is the entire reason this question is being asked. This has to be a text column. The RS needs to be able to each day quickly see what referral has not been completed in the past seven days. It is also difficult, because some referrals take a year, so you can't call the consultant every week. This is something the RS just doesn't do or care about. So, a referral can go five weeks just sitting there. The parent or patient calls wanting to know the status and there is no info. Unfortunately, not only is this for the RS, it is one more thing I have to check so I can to her and say, why is their no activity on this?

At some point, maybe I have to just say I need an Excel specialist to do this for me. Of course, even then, you would have to go back and forth explaining why this is needed or important, but I guess that is what a professional would do.

I am sure you will get some good feedback on the additional information provided.  I will try to look at it tonight myself.

I realise this discussion has been going on for some time now and looking at using Excel.

Have you considered Excel may not be the best platform for the task and looked at using other options???

I am not a database specialist but I suspect this would be fairly standard for a Customer Database with in-built tables for customer (aka patient) details and consultant details. Each patient record could then have a sub section for history notes, contact types etc.

The reporting side would then be an extract of the data.

I used to work in an office where we used a piece of software that kept all the customers' detail and all contacts with the customer were registered. It could be linked to MS Word templates for sending out letters; when a letter was sent a copy/link was stored against the customer's record. Unfortunately, I can't remember the name of software; apologies.

Yes. the conversation has gone on a bit, but this is perhaps not a simple answer.  And though I hope the solution to be fast, the correct answer and approach may take a bit of time.  See comments on attached.
EE.docx

Once again, this is a very difficult subject for me, and my work has been overwhelming. I will get through these comments, and I will comment over the weekend.

I have around 600 questions on EE since 2005, and I have never abandoned a question. It is embarrassing to see that.

Thanks again. Your work and help is very appreciated.

@Rob,

Just to get my question on track, I do like your suggestion. If I could find the right software, that may be a good option.

The irony is if I could just get my staff to do what they are supposed to do, this could be done on paper, lol. But, alas, we are a paperless office. This "program/database" is to accomplish three things. One is to allow-force the referral specialist to do her job correctly, the second is to allow everyone in the office to view the referrals quickly, and thirdly, allow me to monitor her work.

And, there isn't much to monitor. The referrals, getting the records together, doing the insurance work, prior authorizations, etc. is top notch, and I wouldn't have a clue as to how to do it. I just have to monitor that it is being done correctly.

For the most part, she does a great job and has to multitask with checking patients in and out and answering phones, etc. She has plenty of time to do her work though. Some times it is the patient's fault. I tell all patients they should hear back within seven days (hence the seven day rule), and, if they don't to call. Some fall through the cracks mostly due to the consultant's office. But, at times, I will be in the room, and the patient will ask how is that dermatology referral coming? I haven't heard anything in three months -- which is part of the patient's fault.

But, worse, is when we get a call that where the patient/parent says, I have called and/or left messages three times and have not heard back. It's nice (and many times happens, that it is clearly documented "Patient called on July 20th. Informed him that we are still waiting from dermatology to approve the referral. Offered to refer to another office. Patient aware." Nice to have that.

Lastly, what drives me crazy is when the referral specialist neglects to call the consultant's office the following day with, "Did you receive our referral." There is nothing worse than finding out three weeks later, they never got it. They probably did, but it means nothing. And, once again, had you called in seven days, you would have found that out.

I don't write this novel to vent. Just to give a little more information that may help.

Yes, Rob, you are correct. Our electronic medical records works great with Access. Then it was upgraded to SQL Server, and it worked far better and became HIPAA compliant. Sometimes, you just need a better mouse trap.

@Tom,

Your are most likely correct that everyone is busy at their own jobs. I hope you didn't take my comment to mean hey everyone, please comment right away, and I will get back to you when I can. I am highly embarrassed that I have taken so long to get back, and also realize that it has made it more difficult.

Your comment is a good one and one that has made me really look at this a little bit differently. Yes, the comments would have to be separate comments. I just tried out typing quite a bit of information in the comments section. I don't know how to set up wraparound text, although I am sure I could figure it out. But, even when using the enter key, it seems to take up another row.

It seems as though the function of this sheet would have two purposes, one of which would work and is already in place, and the other the more difficult of accomplishing what I am trying to do here. In other words as to having all the data at your fingertips would be helpful, but being able to use some macro or formula so that it shows only those rows which need to be addressed based on a certain criteria seems much more difficult. Just the realization you brought up on the comments section needing to take up so much space makes it nearly impossible.

As EE reminds its users all the time, sometimes the correct answer is "No, it can't be done." I am not quite ready to give up yet. The only sticking point is filtering by the comments section.

Thanks.

Bert

Here is something to think about.  See spreadsheet and instructions.
track-changes-by-adding-a-time-stamp.xls

Nice tips on the wraparound. And, I looked at the next idea. It is helpful, but I agree with you that it isn't perfect. I mean it just looks that after a while it may become cumbersome. I did notice that when you do the wraparound, and then click on the next row, it allows the row above to be the same size height as the cell you made.

I think I will close it here for now. I do not see that I am making any progress for others to build on. Ironically, the best solution was the one that pointed out that the cell would have to get added to making the worksheet unwieldy.

I think also if "I" were designing an Excel sheet for someone who knew as much or more than I (could tweak it so it worked better), then it would be worth moving forward. Maybe after working with it some more, I can open it as a new question with a pointer. Thank you everyone.

@xtermie,

Thanks for your comment. When I opened the Excel spreadsheet, it didn't seem to have the pink cells. Maybe I was doing something wrong.

Doc - One more thought the next time you post a question:

If there are going to be many comments for one referral, you may want to consider separating the comments from the initial referral record.  This is a one-to-many relationship .  is common in many database applications.  The could carry an id that ties them back to the original reference.  

This may seem to complicate your original spreadsheet, but in the end, if done right, could make the process work much better.  The new process could be handled in an Access DB, or possible with Excel's PowerPivot.  Just keep the thought..  You may end up with a tool that could be used by other doctors.  Which, by the way, what are other doctors doing?  

I'm sure I will run across you again sometime.  Keep asking the questions.  - Tom

I actually kinda thought of that. I guess not at the level you talk about. I was thinking about other sheets (tabs) or whatever.

I don't know what other doctors do. Some may have a referral specialist that is very OCD and doesn't miss stuff. Or creates her own way of doing things. Some may have Electronic Medical Records that is much more versatile.

Some may not care as much about it as I do.

Or all three, lol.  Thanks very much for your help.