Getting a value from a previous row

Freymish

I am trying to parse a concatenated string using "~" as the delimiter to get the length of each substring so I can use a MID() to print it out. It's easy enough to do that using a Find statement. All of this is dynamically built off the contents of another sheet such that when a new item is enetered it gets picked up on this sheet so a static link won't really work.

The reference shhet looks like this:

Project #1

• Task1
• Task2
• etc

Project #2

• Task1
• Task2
• etc

My code grabs the non-blank cells in the column to create a string like this:

~Project #1~Project #2~etc~

I have created an index column ofr row numbers and a couple of others to try and determine the start poibt of the FIND() and the legth from the first ~ to the next ~ which tells me how to split it up in the MID() statement.

The trick is when I want to progress to the next instance of the ~ I have to use some point of reference as the new starting point of the FIND. I haven't figured out how to do this without creating a circular reference or a dependency error with a function value needing the ouput from another function as input which sets off a chicken and egg scenario.

If there were a column@Previousrow, or better yet column@row(n) where n= an integer or yields one. With that I could do this easily...

Has anyone done this before? Is there a simple way to increment which instance of the delimiter I want to use as the next one? Sorry if this is vague. I'm running out the door and wanted to at least get this out there before I go. :)

Thanks,

Jack

Leibel S

@Freymish

Ok. then use this formula to split into the row. You should not need the '~' delimiter system.

=INDEX(DISTINCT({Data_Range}),index@row)

Leibel S

@Freymish

Using an auto number you can create a ROW# column and reference that within your function

Title: Row ID (system column Auto-Number)

Title: ROW# Formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

In regards to parsing the data via the "~", let me know if you are having trouble. You can check up the below for help as well.

https://community.smartsheet.com/discussion/comment/315192#Comment_315192

Rachel.Graham

Hi Jack,

I use this example as a default in our Project Plans for a variety of uses.

First, add a text column to the sheet, "Level" and set a column formula of: =COUNT(ANCESTORS([Primary Column]@row)). This allows you to only show the concatenated formula on the level you desire.

Second, set another text column with the header for what you want this to be called, my Column 3 in pic. Set the column formula to: =IF(Level@row = 0, JOIN(CHILDREN([Primary Column]@row), "~")).

This formula can work in the other direction, as I use it with "Ancestors" instead of Children in project plans. I typically call the column Task Group, then I can filter on sections for different reports. Hope this helped.

Thanks,

Rachel

Freymish

@Rachel Graham , creating the delimied list wasn't the problem I did that a little differently using the this:

="~" + JOIN(DISTINCT({Data_Range}), "~") + "~"

Freymish

@Leibel Shuchat ,

I have a row Number column in place, but I don't know how to reference the number above the "[index]@row" I tried using match on the index value but I don't know how to tell it to get the value I want. In the example below I want to put [PNameLen2]1 into [PNameLen]2. That would allow me to set the paramenters for the MID() statement correctly.

=IF(NOT([PNameLen2]@row = ""), MID(PNames@row, PNameLen@row + 1, [PNameLen2]@row))

Leibel S

@Freymish

I think we can simplify this for you. How are you currently creating this '~' delimited list?

Freymish

Just going to the main sheet and pulling all the values in ProJName Column that aren't blank.

="~" + JOIN(DISTINCT({Data_Range}), "~") + "~"

Leibel S

@Freymish

Ok. then use this formula to split into the row. You should not need the '~' delimiter system.

=INDEX(DISTINCT({Data_Range}),index@row)

Freymish

That's very cool and worked great! Thank you!

Freymish

One last folow up to this. I'm getting #INVALID VALUE for the cells that don't have a project name. I tried the following but thet still show up:

=IF(NOT(ISBLANK(INDEX(DISTINCT({Master Projects Range 2}), index@row))), INDEX(DISTINCT({Master Projects Range 2}), index@row))

Conversly, this yields the same result:

=IF(ISBLANK(INDEX(DISTINCT({Master Projects Range 2}), index@row)), "", INDEX(DISTINCT({Master Projects Range 2}), index@row))

Leibel S

@Freymish

Wrap it in an IFERROR

=IFERROR(INDEX(DISTINCT({Data_Range}),index@row),"")

Freymish

OK. That worked. Thanks again!!