Subtask dates populate from parent
Hi,
Does any one know if there is a way to auto up date a subtasks dates when you move the tasks between parent tasks?
The scenario is we have a course booking tracker and the PARENT task is the course instance and the SUBTASKS are the attendees. If we need to change the attends course slot we would like to drag to a different session and the dates auto update.
The second part of the challenge is different course have different lengths and I am using a vloookup table to get other course data but cant work out how to auto change the end date based on a looked up duration?
Answers
-
Alright this was cool to figure out...
On the parent row for each course, use the following formulas (adjust column names to suit.)
List your start date in a Start column.
In a Duration column, pull in your course duration in days. I am assuming these are weekday courses, with holidays excluded?
For the End column:
=WORKDAY(Start@row, Duration@row, {Holidays List})
My formula includes a list of holidays I have in a helper sheet. The end date will give you the date that is that duration's length after start date in workdays.
On your child rows:
In the Start date column, use this to reference the parent of this cell:
=PARENT()
In the End date column:
=WORKDAY(PARENT(Start@row), PARENT(Duration@row), {Holidays List})
Now, when you drag a child row underneath a parent row, the formula cells for that row will show an error at first. Hit the indent button the set it as a child of the other parent row, the start and end dates will update to match the parent.
Here's my test sheet:
When I move Kacie from Course 2 to Course 3, it throws an error:
But after I indent the row, it updates with new start and end dates that match Course 3:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!