Help with working out overlap on vacation and project tasks
Hello, I need some help with a formula I'm trying to modify from here
Formula to Link PTO Tracker to Project Schedule — Smartsheet Community
I keep getting #INVALID REF
I'm new to Smartsheet so a lot of this is going over my head, my workflow is finding something close to what I need and feeding it through GPT with prompts until it spits out the right result, learning as I go.
In this case it is the first time I'm trying to ref something from another sheet so I'm a bit lost.
My ref sheet is "Planned Leave" with "Assignee", "First DOL" and "Last DOL" Columns.
My "Project sheet" where the formula lives has "Assigned To", "Start Date" and "End Date"
I've got this so far
=IF(AND(COUNTIFS({Planned Leave Assignee}, [Assigned To]@row, {Planned Leave First DOL}, @cell <= [End Date]@row, {Planned Leave Last DOL}, @cell >= [Start Date]@row) > 0, @cell <= [End Date]@row, @cell >= [Start Date]@row), [End Date]@row - [Start Date]@row + 1, 0)
Any help would be appreciated.
Best Answer
-
That particular error comes from not setting up a cross sheet reference properly.
Answers
-
That particular error comes from not setting up a cross sheet reference properly.
-
Thank you, the formula is pulling from the other sheet now.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!