Getting a value from a previous row
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:
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. :)
Help Article Resources
Check out the Formula Handbook template!