Index(collect) argument

Good morning,

This should be a simple formula to create but I am not properly completing my argument.  I have a sheet that has three columns, Most recent (checkbox), date, & summary. From another sheet I want to receive data that is in the summary column if the checkbox in the most recent is checked. The logic on the target sheet will never have more than one check box selected.


Formula –

=INDEX(COLLECT({sheetname - Narrative Summary - In Range 2}, {sheetname

- Narrative Summary - In Range 1}, true))

Range 2 = the summary column

Range 1 = most recent column where the checkbox is selected.

 

Any guidance would be most, most appreciated.


Thanks,

Best Answer

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓

    @Jeffmurr I think the help says column is optional, but generally Index needs row and column addressing. Here is what works for me (the IFERROR makes sure the view stays clean if Most Recent isn't checked).

    =IFERROR(INDEX(COLLECT(Summary@row, [Most Recent]@row, true), 1), "")

    dm

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Answer ✓

    @Jeffmurr I think the help says column is optional, but generally Index needs row and column addressing. Here is what works for me (the IFERROR makes sure the view stays clean if Most Recent isn't checked).

    =IFERROR(INDEX(COLLECT(Summary@row, [Most Recent]@row, true), 1), "")

    dm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!