Circular Reference with Vlookup (Works in Excel)

I have a project management table I was hoping to make in Smartsheet. Basically, if a task has a "Predecessor", I'd like the "Start" for that task to be the end of the Predecessor task (whichever task has a matching "Task ID"). If a task doesn't have a Predecessor, I'd like its Start date to be the End date of whatever in the above row. I was able to do this in Excel, but in Smartsheet I end up with a "Circular Reference" error. I've tried a Helper column and everything else I could think of, so any help would be appreciated!
This is the Excel:
I've tried to use pretty much the same function and end up with this:
Thanks!
Best Answer
-
Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?
Answers
-
Try an INDEX/MATCH instead of a VLOOKUP.
=IFERROR(INDEX(End:End, IF(Predecessor@row <> "", MATCH(Predecessor@row, [Task ID]:[Task ID], 0), [Task ID]@row - 1)), "")
-
Hi Paul, thanks for the help. I'm still getting a circular reference error for some reason:
Here's the same picture with the formula exposed for the cell in Start8.
What you suggested worked perfectly in Excel, but still isn't working in Smartsheet for some reason.
-
-
I was hoping to have the End column be the Start column + the Duration column:
-
Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?
-
Because Paul, I was foolish and had not realized that this existed in Smartsheet - wow, what incredible functionality that that opens up! Thank you very much for your time and patience with this, this really made someone's job significantly easier!
Help Article Resources
Categories
Check out the Formula Handbook template!