Pull PTO Dates on a Project Plan
I have two sheets. One is a project plan that includes task descriptions, assignee, start and end dates. I have another sheet (PTO Calendar) where project team members enter vacation start and end dates.
I have a formula that will check a box on the project plan if the time off from the PTO calendar happens between the start and end dates on the project plan. What I need is the ability to pull the PTO start and end dates from the PTO calendar for the task assignee on project plan when the PTO conflict check box is checked. Any ideas on what that formula may look like? I've tried VLOOKUP's and indexes and having trouble making it work.
Best Answers
-
Hi @Jaz693
You can adapt the formula you have in the checkbox column to be in your INDEX(COLLECT function instead.
An INDEX(COLLECT works like this:
=INDEX(COLLECT({Column to Return}, {1 Column with Criteria}, "Criteria 1", {2 Column with Criteria}, "Criteria 2"}, 1)
So you would use the same Column references and criteria as you have in the checkbox, does that make sense? If this doesn't help, can you post what your checkbox formula is?
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Jaz693
Thanks for this information! The criteria in your INDEX(COLLECT should be the same as your COUNTIFS.
Try this:
=IF([PTO Conflict]@row = 1, INDEX(COLLECT({PTO Start Date}, {Master PTO Calendar Name}, Assigned@row, {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row), 1)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah,,,, that was the piece, had to use the same criteria. It works! thank you so much!
Answers
-
Have you tried an INDEX COLLECT combination?
If you need help to set it up, please share a screen shot of your two sheets (hiding any information that you don't want to be publicly visible).
-
I have tried index, match, but not an index collect. How does that work?
screenshot1 is of the PTO calendar that has the dates for a team member.
screenshot 2 is of the project plan that has the PTO conflict checked and columns to pull the date. I just can't seem to get it pull in
-
Hi @Jaz693
You can adapt the formula you have in the checkbox column to be in your INDEX(COLLECT function instead.
An INDEX(COLLECT works like this:
=INDEX(COLLECT({Column to Return}, {1 Column with Criteria}, "Criteria 1", {2 Column with Criteria}, "Criteria 2"}, 1)
So you would use the same Column references and criteria as you have in the checkbox, does that make sense? If this doesn't help, can you post what your checkbox formula is?
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah, makes sense it worked. Thank you for providing the format!
-
Actually I spoke too soon. I entered this formula: =INDEX(COLLECT({PTO Start Date}, [PTO Conflict]@row, 1, {Master PTO Calendar Name}, Assigned@row), 1) and i get #Incorrect Argument Set. but if I take the [PTO Conflict]@ row,1 out, I get a date returned.
I only want the formula to return pto start date for the assignee from the master pto calendar if pto conflict checkbox is checked. I think I have this right?
The PTO conflict checkbox does have a formula too (but it works). Here it is: =IF(COUNTIFS({Master PTO Calendar Name}, HAS(@cell, Assigned@row), {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row) > 0, 1)
-
Hi @Jaz693
Thanks for this information! The criteria in your INDEX(COLLECT should be the same as your COUNTIFS.
Try this:
=IF([PTO Conflict]@row = 1, INDEX(COLLECT({PTO Start Date}, {Master PTO Calendar Name}, Assigned@row, {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row), 1)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah,,,, that was the piece, had to use the same criteria. It works! thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!