Index Collect including Cross Sheet Date Comparison - Formula Help

Diane Moore

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)

1. Historical Patient Treatments
   1. Treatment - Name of Treatment - {Treatment}
   2. Acct - Customer Account {Acct}
   3. MRN - Patient Medical Record Number {MRN}
   4. Newest - A checkbox indicating that this row contains the MAX Treatment-Date for the patient. {Newest}
   5. Treatment Date {Treatment-Date}
2. Current Month Patient Treatments
   1. Account
   2. MRN
   3. 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

Paul Newcome

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.

Paul Newcome

Are you able to provide a screenshot with sample data?

Diane Moore

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

Paul Newcome

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.

Diane Moore

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!

Paul Newcome

Happy to help. 👍️