INDEX(COLLECT( is returning the same value over and over, help!

Evan M.

Hello all,

I'm using the below formula to return values from column (Reference Specifications(document number/ITP numb), but it keeps returning only the first match (1070-06-CTSC-38QT-0009_01), how can I return all other items in the category 😔

=INDEX(COLLECT({FLOC Docs Submission Info-ETS_Aug11 Range 5}, {FLOC Docs Submission Info-ETS_Aug11 Range 2}, @cell = Sites@row, {FLOC Docs Submission Info-ETS_Aug11 Range 6}, @cell = "Quality"), 1)

MCorbin

Index brings back a value from a specific row based on the rest of your criteria.

Try using Join(Collect(....

Evan M.

Hi @MCorbin , I tried (join) but it will collect all data in one cell

MCorbin

OK, so help me understand what you're trying to do...

It looks to me like you want all of the Reference Numbers where the Document Category is "Quality" and the Site/Sub task matches site/subtask in another sheet. Is that right? Or are you looking for a more specific one? If you're not trying to collect all of them in one cell (as there appear to be multiple for each combination), what are you trying to do?

Or - should each row here correspond to a matching row in your other sheet? If that's the case, you need something that makes the row in this sheet and the row in the matching sheet unique. Right now, you have multiple rows for the Site/Document combination. There's nothing to tell Smartsheet WHICH of these rows you want it to take. Do you have another column that can help narrow that down?

Might be helpful to show your target sheet - what you're trying to place from this sheet to where in the target sheet.....

Evan M.

Correct, I tried adding unique values and it worked, thank you @MCorbin 😎