Formula that pulls data from specific columns in a successor row

Laura Krylov ASGLLC

I found the row ID (not a column but the row id created by smartsheet) of the successor rows using the successor column. I'm struggling to figure out how to reference the smartsheet row id in a formula in order to pull data for the successor row for a specific column.

The use case is a helper column that will show the Owners of all the Successor rows. I want the successor owners to be in the Predecessor row so I can set up an automation to send the owners of successor tasks a message when the predecessor task is marked complete.

Has anyone does this before?

James Keuning

I do not think this problem has been solved. See this thread here, and the thread that that thread references.

The problem is when you have mulitple successors, Smartsheet cannot reference the Assigned To for each row. Now, you could take that Successors column and parse it out to multiple columns (and you will need to have one column for every successor, which might make this impossible) and then look up the Assigned To and concatenate those columns into one field. I personally would not do that, because it breaks data normalization conventions, but it could be done. The problem with this is that when you concatenate the contacts into one field, Smartsheet does not recognize them as contacts anymore, and it defeats the purpose which is to use the field for automation.

The formula to do this "one column per successor trick" is:

=INDEX([Assigned To]:[Assigned To], MATCH(INDEX(COLLECT(RowID:RowID, Predecessors:Predecessors, RowID@row), 1), RowID:RowID, 0))

=INDEX([Assigned To]:[Assigned To], MATCH(INDEX(COLLECT(RowID:RowID, Predecessors:Predecessors, RowID@row), 2), RowID:RowID, 0))

=INDEX([Assigned To]:[Assigned To], MATCH(INDEX(COLLECT(RowID:RowID, Predecessors:Predecessors, RowID@row), 3), RowID:RowID, 0))

The incrementing number (the 1, 2, 3 after the RowID@row),) in the formula is the successor number. So the first formula will return the Assigned To of the first successor, the second formula returns the second successor, etc. And if these formulas are all on one row, you can get all of your successors into one row. Getting them into one cell to drive automation is the hard part.

Here is a screen shot which should help orient this crazy answer. The above formulas are in the highlighted cells. The thing I did not do is include any error handling to make it so this formula only acts on rows with successors.

This thread discusses the problem with getting multiple contacts into one cell:

Show Multi-Contacts with a JOIN(COLLECT(...) formula -- working, but not showing as contacts — Smartsheet Community

Laura Krylov ASGLLC

Thank you so much @James Keuning ! This is extremely helpful.

Laura Krylov ASGLLC

@James Keuning I can't use RowID as that's not one of my columns. I am pulling the Row Number from smartsheet.

I modified the formula you provided but I get an incorrect argument. It collects the result in the Successor field but I don't know how to tell it to go to row 665 to get the Owner because the Row number not a column, it's just the row created by smartsheet. How is that row on the far left, created by smartsheet, supposed to be referenced in formulas?

Laura Krylov ASGLLC

This is my successor formula =JOIN(SUCCESSORS([Task Name]@row), ",")

James Keuning

The number on the far left is the row number. For example, if you use a MATCH formula, it will return the row number. And if you reference a field using [Field Name]1 the 1 is that row number. You also use the row number in INDEX.

So in your case, you use:

=INDEX(Owner:Owner, VALUE(Successor@row))

But here is the problem: the row number needs a number, and that Successor value is treated as text. You can tell it's Text because SS aligns it left. Numbers align right. So that is why we have VALUE in our formula. We convert the "number" to a number. If this is confusing, create a column and just multiply a number by the successor, you will get an error. Add VALUE and the multiplication will work.

BUT - All that only works when you have only one number in that Successor field. If you have more than one, you need to parse each number which is effectively performing the operation that I created above.

EDIT: to be clear about what INDEX does: It tells SS to go to the Owner column (referenced by the range Owner:Owner) and go to Row 665. If you did something like Owner1:Owner10 you would be telling SS to go to the Owner column and deal with rows 1-10. There are reasons to do this, but probably not in this case. And referencing ranges or cells like that makes Column Formulas impossible.

Laura Krylov ASGLLC

Thank for the breakdown. That's very helpful. My Successor value is actually coming back as a number. SS let me perform calculations on it without adding VALUE.

I think the core problem I'm having is because SS rows can't be used in formulas. This is what I found:

"Smartsheet's inherent row numbers are visible on the left side of the sheet and are automatically adjusted as you add, move, or delete rows. These numbers are not directly usable in formulas because they are not treated as cell content or data. They are more akin to identifiers for your visual reference only."

So, though it returns the correct row number in my formula, I can't use that result/or can't figure out how to reference it, in another formula. The suggestion from SS is to create another column that can be explicitly referenced. Seems like a rather silly workaround when inherent row number is there.