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.
-
What formula do you have in the End column?
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!