Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Index Collect including Cross Sheet Date Comparison - Formula Help
I have two sheets. On the current month's data (#2 below), I am trying to pull in previous treatments from the Historical Patient Treatments (#1 below)
- Historical Patient Treatments
- Treatment - Name of Treatment - {Treatment}
- Acct - Customer Account {Acct}
- MRN - Patient Medical Record Number {MRN}
- Newest - A checkbox indicating that this row contains the MAX Treatment-Date for the patient. {Newest}
- Treatment Date {Treatment-Date}
- Current Month Patient Treatments
- Account
- MRN
- TreatDate
The formula that I am trying to build should provide me (from Historical Patient Treatments
- {Treatment}
- matching {Acct} = Account@row
- matching {MRN} = MRN@row
- {Newest} is checked (1)
- {Treatment-Date} is +/- 14 days of the TreatDate@row
I have tried 25 different versions of this formula and keep getting UNPARSEABLE
Thanks in advance
Best Answer
-
It looks like you are using the DATE function incorrectly. Does this do anything for you?
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell >= TreatDate@row + 14),1)
I also notice a bit of an argument issue. You are only pulling in rows where the {Treatment Date} is both greater than TreatDate MINUS 14 days and greater than TreatDate PLUS 14 days at the same time. You may want to flip that second one around to make it less than or equal to instead.
Answers
-
Are you able to provide a screenshot with sample data?
-
@Paul Newcome - Does it help if I give you the formula that I think should work? If not, it is going to take me some time to make an example of this as I can't take screen shots of what I am doing since it contains PHI.
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= DATE(TreatDate@row - 14), {Treatment Date}, @cell >= DATE(TreatDate@row + 14)),1)
-
It looks like you are using the DATE function incorrectly. Does this do anything for you?
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell >= TreatDate@row + 14),1)
I also notice a bit of an argument issue. You are only pulling in rows where the {Treatment Date} is both greater than TreatDate MINUS 14 days and greater than TreatDate PLUS 14 days at the same time. You may want to flip that second one around to make it less than or equal to instead.
-
Thanks so much @Paul Newcome . This worked. And good catch on the date argument issue.
Here is the formula that worked: =INDEX(COLLECT({Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell <= TreatDate@row + 14),1)
I appreciate you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
