Project Sheet -- Dependencies report

Fabio Botelho

Hi @ellepitch @Debbie Sawyer @ewiederh

Does someone have a change to implement Dependent on column with multiple dependencies? As the same, this is exactly what I've been looking for.

I really appreciate if you share the formula or some other workaround to have it.

Thank you.

Debbie Sawyer

@Fabio Botelho

Goodness me, I'd forgotten about this thread.

I didn't quite get it working.

I got stumped with seeing double digit predecessor numbers as numbers rather than text strings...

You can see from this screen shot, Within the dependent On column I was able to return the Task Name for the rows declared in the Predecessor column for rows with a single digit predecessor, and a set of predecessors that are split with a comma, I could cope with the single row predecessors with SS (FF and SF) written with them. But as soon as we got into double digit row numbers, my system read them as single digits before reading them as double digits...

Eg for the row with 10 as its predecessor it returned row 1 and 10, instead of just 10. I think it is to do with my using a Contains function, but I'm not sure which other one to use.

I am happy to share this sheet to anyone that might want to help overcome the final hurdle, then I can share the solution with anyone that wants it.

@Paul Newcome - fancy helping me complete this?

Paul Newcome

@Debbie Sawyer Here is an old thread that honestly got left behind. Basically the key is to use a multi-select dropdown to be able to allow you to leverage the HAS function. The thread below could probably be made much more efficient as a lot has changed in the past almost 2 years, but it should help get you on the right track.

I am going to try to revisit the thread when I get a chance, but there's no telling when that will be.

https://community.smartsheet.com/discussion/84257/successors-testing

Debbie Sawyer

@Paul Newcome

Hmm - I'll do some playing with the Has function, as I am currently using Contains against the multi select...

In your old thread you ask about solutions for the SS, FS etc, I have combatted that part, just this insistence on reading two digit number as single digits as well!!

Thanks for the heads up on trying HAS...

Debbie Sawyer

Frustratingly I think I need a combination!

One Column formula

= 1

RowNum formula

=SUM(One$1:One@row)

Text RowNum formula

=RowNum@row + ""

P Value formula

=Predecessors@row

Multi P Value formula

=IF(OR(CONTAINS("S", [P Value]@row), CONTAINS("F", [P Value]@row)), SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([P Value]@row, ",", CHAR(10)), "F", ""), "S", ""), SUBSTITUTE([P Value]@row, ",", CHAR(10)))

New Dependant on Multi formula

=JOIN(COLLECT([Task Name]:[Task Name], [Text RowNum]:[Text RowNum], CONTAINS(@cell, [Multi P Value]@row)), CHAR(10))

HAS instead of Contains formula

=JOIN(COLLECT([Task Name]:[Task Name], [Text RowNum]:[Text RowNum], HAS(@cell, [Multi P Value]@row)), CHAR(10))

So the New Dependant on Multi column works except it reads the double digit row numbers as single digits before it reads them as doubles (so that column does return the right data, but it also includes a few wrong rows too)

The HAS instead of Contains column reads the double digits correctly but it doesn't cope with more than one entry in the predecessor column.

Any ideas how I can combine the two formulae to get the right result!?

Paul Newcome

Use the HAS but flip the arguments around.

Notice how your CONTAINS function is essentially "backwards"? The typical syntax is CONTAINS([Multi P Value]@row, @cell), but you have it the other way around.

The HAS function syntax is opposite of CONTAINS, so having @cell first is the "norm". Try making @cell the second argument in the HAS function.

I also feel like we should be able to make your Multi P formula a bit more efficient:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([P Value]@row, ",", CHAR(10)), "F", ""), "S", "")

You shouldn't need the IF statement. The SUBSTITUTE shouldn't throw an error if there is no "F" or "S". It should just leave it alone.

Debbie Sawyer

@Paul Newcome

Fabulous! Thank you!

New Multi P Value (thank you Paul)

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([P Value]@row, ",", CHAR(10)), "F", ""), "S", "")

New HAS (which is the Dependent On column)

=JOIN(COLLECT([Task Name]:[Task Name], [Text RowNum]:[Text RowNum], HAS([New Multi P Value]@row, @cell)), CHAR(10))

@Fabio Botelho - there you go - it all works now.

Good luck!

Paul Newcome

@Debbie Sawyer If you wouldn't mind... Go ahead and either copy the solution over to my SUCCESSORS thread or paste a link to this thread over there so I can steal it later (and so we have everything tied together for other users of course). Haha

Debbie Sawyer

@Paul Newcome

Embarrassingly Paul I have no idea how to tag a part of a thread within your thread! Please feel free to do it if you are able to! 🙄

(Can't believe I am admitting this on the community!!!!) #Everyday is a learning day! ha ha

Fabio Botelho

@Debbie Sawyer @Paul Newcome you are all the best! That's help me a lot. Works perfectly. Thank you.

@ellepitch @ewiederh tagging you on this thread just in case you haven't found a solution to this yet.

Muito obrigado 👊

ewiederh

@Debbie Sawyer @Paul Newcome @ellepitch @Fabio Botelho

I created a very similar solution on my end, but as a heads up, having any lags is a limitation of the formula.

Also - an important watch out. This is my FAVORITE column in my schedules, but because it uses a column reference, you can quickly reach your 25 million formulas per sheet if you have a large number of rows! One of my schedules is ~ 775 rows currently and it's slow but not broken....yet. Another one of my schedules exceeds 2,000+ and i have issues if i try to add more than a few lines at a time, and saving takes an eternity. It's a double edge sword to include - because it is super helpful in large schedules!