Index(collect) argument

Jeffmurr

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,

Dale Murphy

@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

Dale Murphy

@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