Link in assignment with a lookup that is between two dates
We have scheduled out assignments of who has priority for some planes our company uses. This is assigned at the beginning of each week (Monday to Sunday). I want to link in the assignments from our scheduling smartsheet into our tracking sheet that we use for when a plane is in use. This will be used for our tracking in 2021, so I have put in a stock date of 01/05/21 for now.
So, based on the name of the plane, I want to bring in who has the assignment into our "assigned to" column on our tracking sheet. Hopefully some screenshots help. The circled area is where I want the assignment to go into.
Thank you for your time
Answers
-
Hi @Billy Rock
From what I can see, it sounds like you're looking to pull in the name from the Assigned To column, with the criteria of the Plane Name & the Date.
To pull in the Assigned To name you can use an INDEX(COLLECT formula, using cross-sheet references into your source sheet.Try something like this:
=INDEX(COLLECT({Assigned To}, {Plane Name}, [Task Name]@row, {Start Date}, Date@row))
This assumes that the date in your Tracking sheet will be input as the Start Date for that week.
If that date in your Tracking sheet will be somewhere in the range between your start and end dates, then you can try something like the following:
=INDEX(COLLECT({Assigned To}, {Plane Name}, [Task Name]@row, {Start Date}, <=Date@row, {End Date}, >=Date@row))
Let me know if this works for you, or if I've misunderstood what you're looking to do!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Billy Rock You could also take a Plan and Assignments approach. This approach assumes you have N number of plans. Each plan has a multiple plane assignments you want to track.
Each plan sheet has a format that includes a Report on column.
Then, you create a report to list just on Falcon assignments.
You could also do report that lists all assignments irregardless of the type of plane;
Finally, if you wanted a bonus functionality you can also make assignments from the report without going to the plans.
This gives you the added ability to see across plans when assignments are in conflict.
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!