Nested ranges in INDEX/COLLECT??

MsMaryannM

I need to pull in all of the email addresses from a sheet into another sheet so that my workflow can alert the people listed in that cell. I have the cell set up as a contact list allowing multiple contacts.

I need to pull all the emails for "Anywhere US" where the level is "Major" into the sheet that has my workflow when a new row is added via a SmartSheet form:

This was the formula I tried but I am getting "unparseable" errors:

=INDEX(COLLECT({Incident Call Tree Range 1}, {site}, Site@row, {Level}, Level@row, "Email"), 4)

What am I doing wrong? Do i need to create a VLOOKUP in the COLLECT function? Is this even possible with SmartSheet?

KPH

It is the cross sheet references that are the problem. You need just 3. You can name them whatever you want but I am going to make suggestions.

One will be the Email column. Call it Email.

One will be the Site column. Call it site (I think you already have this one).

One will be the Level column. Call it Level (I think you already have this one).

If you use those names then the formula will be:

=JOIN(COLLECT({Email}, {site}, Site@row, {Level}, Level@row), ",")

All you pass into the COLLECT is the range to collect (this comes first) and then a series of criteria ranges and criteria (in pairs, one after the other).

So in the COLLECT above we are collecting the value from Email where the value in site matches Site on the current row and the value in Level matches level on the current row. That collection is then the range in our JOIN and each is separated by a comma.

KPH

I think you might have VLOOKUP and INDEX confused.

What is this part in bold?

=INDEX(COLLECT({Incident Call Tree Range 1}, {site}, Site@row, {Level}, Level@row, "Email"), 4)

If you remove that that the syntax will be correct. But I think there is more…

The COLLECT function will be collecting the values in Incident Call Tree Range 1

where site matches Site@row and Level matches Level@row.

I think that first range might be incorrect. It should be the Email column not the entire table. The name suggests it is the whole table. Check this reference in bold is to the email column:

=INDEX(COLLECT({cross sheet reference for Email column}, {site}, Site@row, {Level}, Level@row), 4)

You also probably want the first row that matches not the 4th.

Change the 4 to a 1.

=INDEX(COLLECT({cross sheet reference for Email column}, {site}, Site@row, {Level}, Level@row), 1)

Let me know how you get on. Good luck!

MsMaryannM

Hi KPH - The "EMAIL" is the name of the column where the emails are contained. In the first image, you see in the column labeled email? I need to pull in all the emails for that site and it's related level. So essentially I want user@email.net & user2@email.net to pull into the other sheet where this formula will sit.

If i can get this formula to work it should end up looking like this in sheet that has my workflow to email alerts.

KPH

The column containing the email addresses must be the first one in the collect function - the range you are collecting. I don't think you need whatever you have referenced as {Incident Call Tree Range 1}

Instead set up a cross sheet reference to the Email column in it's place.

Then the COLLECT function should work.

The INDEX will return the first email address that matches the criteria in the COLLECT.

If you want multiple email address in one cell, you need to use a JOIN function, instead of INDEX.

Your range will still be the COLLECT function. Instead of specifying the row, you specify the delimiter.

=JOIN(COLLECT({cross sheet reference for Email column}, {site}, Site@row, {Level}, Level@row), ",")

MsMaryannM

Now I'm getting an INVALID REF error. Should the sheet with the emails have the email column be first? I know with VLOOKUPS the formula is specific to have what you're looking up be in the first column you're referencing. Does the same logic apply here? SO instead of my ref sheet having site-level-email it should be email-site-level?

KPH

The column order does not matter for COLLECT. You need 3 cross sheet references:

Email, Site, and Level.

You need to create each cross sheet reference (don't just copy my example formula) and the names you give them need to match the names in the formula.

MsMaryannM

This is what is giving me the INVALID REF error:

=JOIN(COLLECT({cross sheet reference for Email column}, {cross sheet reference for Site column}, {cross sheet reference for Level column}, {site}, Site@row, {Level}, Level@row), ",")

do I need to separate each sheet reference in its own COLLECT?

(Sorry i'm new to JOIN and COLLECT functions)

KPH

It is the cross sheet references that are the problem. You need just 3. You can name them whatever you want but I am going to make suggestions.

One will be the Email column. Call it Email.

One will be the Site column. Call it site (I think you already have this one).

One will be the Level column. Call it Level (I think you already have this one).

If you use those names then the formula will be:

=JOIN(COLLECT({Email}, {site}, Site@row, {Level}, Level@row), ",")

All you pass into the COLLECT is the range to collect (this comes first) and then a series of criteria ranges and criteria (in pairs, one after the other).

So in the COLLECT above we are collecting the value from Email where the value in site matches Site on the current row and the value in Level matches level on the current row. That collection is then the range in our JOIN and each is separated by a comma.