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


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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!