Link to home
Start Free TrialLog in
Avatar of Bert2005
Bert2005Flag for United States of America

asked on

I have a fairly difficult formula I would like Excel 2013 to do? It is too much to write in the question.

Hi experts,

My spreadsheet is designed to contain on all referrals to subspecialists in pediatric medicine. After a referral is made, (let's say it was made on Monday, June 27th -- two days ago). The consultant tells my referral specialist we have received the records, and we will be calling you for a date and time of the referral. Great! Now some consultants will see the patient very quickly, and some may book two months out. Of those that book a long way out, I would be ecstatic if they said we will see your patient on January 1, 2020. I am exaggerating a little, but just give me a date and time.

The problem is many of those who book way out do not give a date and time. They tell you they will get back to you, and it could be weeks before you hear back. And, therein lies the problem. You must keep track of tens and hundreds of referrals and make sure they don't get lost in the shuffle.

What happens is no matter who your hire to do your referrals and no matter how many times you tell them, they do not come up with a good system, so three months later your patient calls and asks what is up with my son's referral. You look and there is nothing documented since the day you made it. THAT IS A PROBLEM. Sorry to yell, but I am the one who trakes the blame, and it is both bad for the patient and embarrassing.

So, I have made an Excel spreadsheet, which contains everything from date made, patient, referral specialist, phone numbers, etc. Now here is what I would like it to do.

Say there are 100 patients with various referrals. In the status column, it will say pending and completed. So, already we have a filter that brings us down to 70 patients. Now, after we make the referral say on the 27th of June, I want my referral specialist to call the consultant every seven days to see what is up with the referral. Just to be clear so you know how simple this should be, my specialist has two things to do. Do any referrals I make that day. And call on the referrals that were started seven days ago to check on the progress. Now, while that would be the 4th of July (just a coincidence) for the referral we are dealing with now, it would be every seven days. So, the consultant says on the 4th we are still waiting for the doctor to review the referral or we are still waiting for ....... whatever. So, she needs to call in seven more days.

So, each day there will be a subset of referrals that fall in the category of needing to be called where there referral started in multiples of seven days ago.

So, I would like a column that filters based on what referrals were made where they are Pending + Made 7 days ago or 14 days ago or 21 days ago and so on and so on. Obviously, the only problem would be that seven days may fall on a Saturday or Sunday or a holiday such as July 4, 2016. In that situation, I would want the formula to produce the ones above plus those that would have been shown on a Saturday or Sunday or a holiday (I would be happy with leaving out the holidays if that would make it too difficult as we only recognize eight holidays a year. Also, if a referral where the seventh day or multiple of sevens fell on a Saturday or Sunday and, therefore, showed up in the filter on Monday, I would want it to revert back to every seven days from the time it was made.

Of course, the other thing which may make it simpler, given all referrals would be made on Monday through Friday, I guess every seventh day would make the referral fall on the same day of the week it were made.

OK, so I talked way too much and made what was probably an easy question difficult.

Thank you.

Bert
ASKER CERTIFIED SOLUTION
Avatar of bromy2004
bromy2004
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Bret,

"I have a fairly difficult formula I would like Excel 2013 to do? It is too much to write in the question."

I don't know about your actual question, and hopefully Bromy's answer will help, but in future I suggest you make your question titles a lot more concise.  For starters, don't ask things like "It is too much to write in the question." in the title.  Put that in the body of the question if you like.

And the title could probably be reduced to something like "Excel 2013 complex formula", without losing any meaning.

If you keep it as concise then experts can more quickly read it so decide whether they should open it or not.  That's significant when they're reading many per day.

Thanks.
tel2
Vigilante Question Title Police   8)
...and for Pete's sake, we don't need the entire background of how your issue came into existence.  Please read Jim's article on how to ask better questions to get better answers, which is perfect for this occasion.

https://www.experts-exchange.com/articles/18499/Top-10-Ways-to-Ask-Better-Questions.html

Jeremy Dempsey
Lengthy Drawn-Out Question Vigilante Police
Amen to that, fellow vigilante!
Take a (virtual) pay rise.

tel2
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bert2005

ASKER

Sorry everyone. I have been without Internet for two days. I will get back to you today. I apologize again.
@bromy2004 Thank you for taking the time to answer the question. It worked great.

@Jim I don't know how to tell you how much I appreciated you comment. I tried to do the best I could. Thanks for recognizing that.

@slightwv and Nick67 Thank you for your suggestions on Access. Unfortunately, I am worse with that app, lol, and my question would be even longer. But, I get the message. Thanks.

@the other two Your message is well taken, and I will work on my questions. But, the way you wrote the messages were rude. Directing me to the link for the article would have sufficed. And, to actually make fun of me by writing Amen for the vigilante police was not helpful. So, I have a suggestion for you both of you. If you don't like the title of the question and/or you think it is too long, then don't read the question.
@Jeremy,

I would suggest you read this link http://tinyurl.com/mutually-exclusive in regard to your Vigilante Police signature.

I would suggest the simple version rather than the full. It's shorter and, well, simpler.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>Unfortunately, I am worse with that app

Every one of here was there are some point in their past!!!!!

Time to take off the training wheels...  Just because it might intimidate you doesn't mean it isn't the correct tool.

If you are afraid of guns and all you have is a sharp stick and a gun and a tiger is charging you, what would you prefer to have?

Don't be afraid of what you don't know.  You have some of the top Experts around to help you!!!  We are here and for some of us, we are here to help!

I'm not an Access Expert but I know enough to be dangerous so I normally don't follow the Access Topic Areas.  If you ask followup questions and would like what help I can offer, feel free to message me and I'll do what I can.

P.S.
I'm 100% in agreement with you in your comments back to the vigilantes.  The True Experts that came to this question understood it perfectly!

>> suggest the simple version

lol.....   ;)
@slightwv,

Thanks for the input and especially your opinion. I think I could tackle access. Probably a bit too much for this project. I am not as used to the new site, but had they noticed, I have nearly 600 questions.

I am the admin for a user board for physicians who own a particular EMR. We are always getting questions like we have two doctors. What would you suggest for a network? Well, that's a bit vague and it takes forever to find out whether they want client/server, VoIP, Exchange vs Office365 and how expensive a server. We tend to like too much than too little info.

The irony is that they doctor who developed the EMR did so by teaching himself C++ and Access, sold the company for a ton of money, and now it is rated the #1 EMR for SMB and using SQL 2012. So, you are right. Access can be learned.

You are right about Access. But, I will probably just stick to Excel and find a way for her to call these consultants weekly.

Thanks again. I love EE and all the help I get from the dedicated experts.
Hi slightwv,

> "I'm 100% in agreement with you in your comments back to the vigilantes.  The True Experts that came to this question understood it perfectly!"

We don't know what experts may have skipped this question because of its length.

This issue I raised was not about understanding the question (or title), and neither Jeremy nor I said anything about that.  It was about suggesting changes which would make things faster and easier for experts in future.  If you make things easier for experts, then you probably get more/faster answers for yourself, and may even save yourself typing time.

I think my 1st post was polite and should have been helpful, and my vigilante signature was obviously a joke (and I put a smiley face next to it lest anyone didn't read it as a joke).

I sent my 2nd post mainly because I saw that Jeremy had used a similar signature to mine, which I found amusing.  My "amen" was aimed at his message (because I had focused on the title and missed the more significant issue which was the question itself), not his...directness.  I'm sorry that I didn't make that distinction of approval clear, Bert.

If you think I was rude, slightwv, then what do you think it looks like when you imply that Jeremy and I are not "True Experts"?  I was trying to help Bert and all experts who might read his question titles in future, and I did it with the knowledge that I was very unlikely to get any points for it (because it didn't answer his actual question).  Sorry to hear that no one seems to appreciate that investment in the future.  If I wasn't a True Expert, then I wouldn't have the EE points that I do, and I wouldn't try to help Bert (and all experts who may read his questions in future).

I hope Bert learned something from the link Jeremy provided.

tel2
@tel2,

Not to start a flamewar, but I think you are missing the point. Your first comment was appreciated. It was your second post which only served to joke with Jeremy at my expense. It was not professional.

I would suggest in the future if you wish to help, do so without cute little signatures that are only funny by making fun of the author.

You and Jeremy should not be the "vigilantes" of EE. They have mods for that.

I very much appreciated your help. It just would have been taken better without the signature.

I appreciate your taking the time to write and clarify. I do think you are an expert. I don't, however, think the same way about Jeremy. There is absolutely no reason to start a comment out with "for pete's sake." I find that sometimes using links to established definitions helps to get one's point across without making it personal. I love this definition:

http://www.dictionary.com/browse/for-pete-s-sake

The key word in this definition is impatience. Impatience is an uncomfortable feeling and most people try to distance themselves from it. Jeremy would have done best to just leave the question alone. He didn't come across with his link and words of wisdom as he was trying to help as more than he had come across one more ignorant asker.

You mentioned your number of questions answered, which shows your dedication and knowledge. May I mention being on for 11 years, paying my monthly payment month by month and having around 600 questions answered. So, somehow, I get my questions answered.

You are probably right. I received answers. Logic follows a better question would have received more. And, I will work on that. But, to be honest, when I am stressed about a project, my main focus isn't how good the question is but the content.

Thanks for your help. I hope we can just let it be here. Bert
Hi Bert,

> "I would suggest you read this link http://tinyurl.com/mutually-exclusive in regard to your Vigilante Police signature."
The above link in your response to Jeremy seems to take me back to this EE question.  Any ideas why?

> "Not to start a flamewar..."
Don't worry - I'm not planning on doing any flaming.  I'm just respectfully sorting out some misunderstandings, just as you seem to be.

> "...but I think you are missing the point."
I'm feeling the same way about you missing my points.

> "Your first comment was appreciated."
Good.  The reason I included the comment about the politeness of my first post was, I was not sure whether the term "messages" in your statement: "But, the way you wrote the messages were rude", included my first post.  Now I am clearer on that, thank you.

> "It was your second post which only served to joke with Jeremy at my expense. It was not professional."
I wasn't trying to be professional in that post, Bert, but I think I've covered this in my previous post, which is one of my points that you seem to have missed.  I'm referring to the entire paragraph which ends in an apology to you.

> "I would suggest in the future if you wish to help, do so without cute little signatures that are only funny by making fun of the author."
This is another point you seem to have missed.  The vigilantes signature was not intended to make fun of you at all.  I was making fun of my position (i.e. a clearly bogus title for someone who's not officially working for EE in this role, but did it anyway).  Maybe I'm just thick, but I still don't even understand how it could be interpreted to be making fun of you.  It was about me, not you.

> "You and Jeremy should not be the "vigilantes" of EE. They have mods for that."
Mods have official roles and are not vigilantes.  I would not want to make a big thing about question title lengths by calling in mods, and I don't think it's serious enough to involve them.  I expect most askers would be willing to listen to reason from anyone who can help improve things for the future, and some of your comments indicate to me that you do appreciate our main points.

> "I very much appreciated your help. It just would have been taken better without the signature."
It seems you misunderstood the point of my signature.  That happens sometimes with some types of humour.  Some people would find it funny.  I've explained other aspects of this 2 paragraphs up.

> "I appreciate your taking the time to write and clarify. I do think you are an expert. I don't, however, think the same way about Jeremy. There is absolutely no reason to start a comment out with "for pete's sake." I find that sometimes using links to established definitions helps to get one's point across without making it personal. I love this definition:
http://www.dictionary.com/browse/for-pete-s-sake
The key word in this definition is impatience. Impatience is an uncomfortable feeling and most people try to distance themselves from it. Jeremy would have done best to just leave the question alone. He didn't come across with his link and words of wisdom as he was trying to help as more than he had come across one more ignorant asker."

Jeremy may have been having a bad day and let that get the better of him for a moment.  Who knows, but (possibly temporary) lack of diplomacy doesn't disqualify someone from being an expert.  Some askers would be able to receive the good from his message and overlook the bad.  It's seems you're not one of them, and depending on my mood at the time, I might not have been one of them either if I was the asker.  I'm not saying you necessarily should have sucked it up in silence, but I'm giving you some perspective.

> "You mentioned your number of questions answered, which shows your dedication and knowledge. May I mention being on for 11 years, paying my monthly payment month by month and having around 600 questions answered. So, somehow, I get my questions answered.
You are probably right. I received answers. Logic follows a better question would have received more. And, I will work on that."

Good.
Looking at the number of points Jeremy has earned for the relative period he's been with EE, it looks as if he's got a comparable amount of dedication and knowledge.  And like all EE experts, he's sharing it for no monetary gain.

> "But, to be honest, when I am stressed about a project, my main focus isn't how good the question is but the content."
I wonder if a similar principle would have applied to Jeremy.  When he's stressed/impatient/annoyed/whatever about someone's question, his main focus might not always be on how polite his response is, but on getting the message across for the benefit of all in the future, and unfortunately this resulted in a less-than tactful response which seems to have offended you.

> "Thanks for your help. I hope we can just let it be here. Bert"
I accept your thanks, but if "I hope we can just leave it be here" means "you already understood my position and you want to have the last say", then I have to disagree, which is why I've further explained it above.  But I acknowledge that you may have meant something else.  If you don't want to invest any more time on this, that's fine - I don't need you to.  If you do, then that's fine too.

Thanks.
tel2