Multiple Values Filtered to One Cell

Genevieve P.

Hi @LGraf

In this instance, the error is appearing based on how you typed out your {ranges}.

To clarify, when you reference a column in the same sheet as the formula, you list it twice to tell the range where to start and where to stop.

For example, if my range in [Test Column] was looking at row 1 to row 5, I would state the range like this:

[Test Column]1:[Test Column]5

In your previous formula, you wanted to reference the entire column regardless of row numbers, so as new rows came in they would be included. When you do this, you take out those two row numbers:

[Test Column]:[Test Column]

However! When you're using {cross sheet references} you select the entire range in one go - you're telling the range where to start and stop. This means you only need to select that range once, like so:

{Test Column Cross Sheet}

So in your formula:

=JOIN(COLLECT({EC Contract or Event PO#}, {EC Vendor Name}, [Vendor Name]@row), " - ")

Keep in mind you'll need to create these references by using the pop-up box (click "Reference Another Sheet") that allows you to search for sheets and select the column to reference as well. Here's more information: Create cross sheet references to work with data in another sheet

Here's another Community post where I have step-by-step examples on how to create a reference.

Cheers,

Genevieve

LGraf

Excellent point re-referencing a whole column in another sheet. However, Smartsheet is still returning an #UNPARCEABLE error. I did link the reference sheet using the Reference Another Sheet option.

Genevieve P.

Hi @LGraf

Can you post a screen capture of the formula open in a cell of your sheet?

LGraf

See below.

Genevieve P.

Hi @LGraf

Do you have a column titled "Vendor Name"?

It looks like the column title for the matching value may not be correct.

LGraf

It was indeed the column name... We track the 'Vendor Name' in both sheets but the columns have different headers. Another great catch. Thanks again for your help!

Kevin7859

I am using this same function, but keep getting a syntax not quite right error:

Formula: =JOIN(COLLECT({SSS Name}, {District Name}, District@row, [{Contact Type}, "School Safety Specialist"]), ",")

SSSName: Column of Names to return and join with a "," based on 2 criteria

District Name is Range and Criteria 1

Contact Type and "School Safety Specialist" is Range and Criteria 2

Some districts have multiple designees so I am trying to join their names into one column, and once that is done I will use a similar formula to join their email addresses in another column.

Genevieve P.

Hey @Kevin7859

You're receiving an error due to the [square brackets] in the end part of your formula. Try removing these:

 =JOIN(COLLECT({SSS Name}, {District Name}, District@row, {Contact Type}, "School Safety Specialist"), ",")

Cheers,

Genevieve

Kevin7859

https://community.smartsheet.com/discussion/comment/398606#Comment_398606

That worked better than expected. Thank you