Circular Reference with Vlookup (Works in Excel)

jgsmartsheet

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!

Paul Newcome

Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?

Paul Newcome

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)), "")

jgsmartsheet

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.

Paul Newcome

What formula do you have in the End column?

jgsmartsheet

I was hoping to have the End column be the Start column + the Duration column:

Paul Newcome

Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?

jgsmartsheet

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!

Paul Newcome

Happy to help. 👍️