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!