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)

  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

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot with sample data?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Diane Moore
    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
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Diane Moore
    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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!