Help with working out overlap on vacation and project tasks

Grant Jones

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.

Paul Newcome

That particular error comes from not setting up a cross sheet reference properly.

Create cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

Paul Newcome

That particular error comes from not setting up a cross sheet reference properly.

Create cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

Grant Jones

Thank you, the formula is pulling from the other sheet now.

Paul Newcome

Happy to help. 👍️