Index Collect

Options

I am trying to collect a date based on a certain time point from another sheet by using INDEX(COLLECT) but something is not adding up. The Reference Sheet - IE Tracking Range 2 is the date I wanted populated in the cell. The Reference Sheet - IE Tracking Range 3 is the time point I am basing it off of but when I fill out the equation below I am getting invalid value. What am I missing?

=INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

Best Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/04/24 Answer ✓
    Options

    yea the criteria would be cycle 2. So would the subject id be the patient id? If so.

    =INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, “Cycle 2”,{Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Aimee Armitage
    Answer ✓
    Options

    Thanks Mark! I got it to work with something similar: =IFERROR(INDEX(COLLECT({Reference Sheet- IE Tracking Range 1}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row, {Reference Sheet- IE Tracking Range 2}, "Cycle 2"), 1), "")

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/03/24
    Options

    @Aimee Armitage

    You are missing a Criterion and a criteria.

    You need the Criteria for the Range {Reference Sheet- IE Tracking Range 3}. and what is the range your looking at [Patient ID Number]@row) and its criteria

    =Index(Collect(Look up range, Criteria range1,Criteria 1,Criteria range 2, Criteria 2, ….),1)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Aimee Armitage
    Options

    I am still confused on the criteria and criterion. In my reference sheet (seen below), I want to pull over the received date for subjects on Cycle 2 into my main sheet.

    In my main sheet, I am trying to enter the formula to have the date pull over for the patient for Cycle 2:

    Range 1 would be 'Received Date', range 2 would be the 'Time point' and then would the criteria be 'Cycle 2'?

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/04/24 Answer ✓
    Options

    yea the criteria would be cycle 2. So would the subject id be the patient id? If so.

    =INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, “Cycle 2”,{Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Aimee Armitage
    Answer ✓
    Options

    Thanks Mark! I got it to work with something similar: =IFERROR(INDEX(COLLECT({Reference Sheet- IE Tracking Range 1}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row, {Reference Sheet- IE Tracking Range 2}, "Cycle 2"), 1), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!