Link in assignment with a lookup that is between two dates

Billy Rock

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

Genevieve P.

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

Mary_A

@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.