Need to return the Task Id of another child row when criteria met

g8rb8t

I need to return the hyperlink of another child row when certain criteria is met. Could use some help. Basically the Red text is the formula I'm tying to build to return based on the certain criteria. Thanks for any assistance :) Please assume Task Id column is a hyperlink already.

ShelbyWarren

@g8rb8t I found another thread in the community that has already visited this. Here's what it said:

"By using the VLOOKUP Function and Cross Sheet Referencing, you can pull in a hyperlink from another Sheet."

Sounds like you may need to create a source sheet that contains the necessary hyperlinks.

ShelbyWarren

After reviewing the previous thread I mentioned earlier, it seems that Smartsheet formulas won't actually return a hyperlinked text or number value, it will only return a cell that contains the actual URL address if it is entered into the cell. Here's what the original thread said,

In reviewing the image provided, I did notice that you have hyperlinked text values; any Cell Reference or Cross Sheet Reference will return hyperlinked text as text values only. In order for it to return a hyperlink, this will need to be the website URL (for example, use "https://smartsheet.com" instead of "Smartsheet")

g8rb8t

Thanks @ShelbyWarren - I can figure a workaround for the hyperlink piece. Likely more critical is if I can retrieve the row. Thanks for the reply.

ShelbyWarren

I think I understand what you're trying to do now. Give me a min and I'll reply

ShelbyWarren

Try this to get the ball rolling:

Insert this in the "Next Task" column, row 1, and drag it down the column (don't make it a column formula): =IF(AND([Task Type]@row <> "Task", Status@row = "Done", Status2 <> "Done"), Task2, "")

This will show the Next Task that needs to be completed when the task before it is marked as done, then when it is marked as Done that row's task will disappear and the task below it will show the Next Task.

g8rb8t

This is close, however the sub-tasks won't always be in the same order (unfortunately) so I can't just refer to the row above etc. in the formula.