Referencing Multiple Columns in another sheet to Match a Location and Date Range to a Project

So I have been looking through the community for a while and have seen a couple solutions mildly similar to what I am looking to do, but can't quite get it right. Feel free to link me to another solution if you feel it would solve this problem:

I have a reference sheet which I do not own that contains a listing of Tasks with their Start date and Finish date. These tasks fall within larger projects, and there may be multiple Projects planned per Location.

The sheet I am working in tracks the costs incurred on these locations. I will collect the information of the Location name and the date of the cost in my sheet. I am looking to use a formula to match the Location and Date to look up which project was active on that Location at that time.

For example: If the reference sheet shows that Location A has Task A1A for Project 1 Start 1/1/20, Finish 2/1/20. Then has Task A1B for Project 1 Start 2/2/20, Finish 3/1/20.

There is also Project 2 on Location A with Task A2A Start 6/1/20, Finish 8/1/20.

In the Cost record there will be entries for Location A on 1/15/20, 5/1/20, and 7/1/20. I want the column in this cost sheet to display Project 1 for the 1/15 cost, Project 2 for the 7/1 cost, and "Inactive" for the 5/1 cost. by looking for the cost entry to be on the same location, between the start and end date of one of the tasks in that project.

Any advice would be much appreciated! I will have to build a separate sheet if the description is not enough to explain this situation due to the format of the reference sheet. please let me know if this is necessary.

Best Answers

  • H. Obringer
    H. Obringer ✭✭✭
    Answer βœ“

    NICE, @Paul Newcome ! Thank you. I was trying various hacked up versions of that after seeing some of your other answers, but this is my first time experimenting with the Join(Collect.

    From your formula the only adjustments I made was to add another date criteria and to make it more like:

    {Start ref}, @cell <= [date]@row , {Finish ref}, @cell >=[date]@row

    And then I also added in an IFERROR to get the "Inactive" status if the criteria are not met.

    The way this came out gave me a repeating join... so I am going to tinker with this a tiny bit more to see if I can sort that out... But at least I am past the formula errors. Thanks again! 😁



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!