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

Welcome to the New Smartsheet Online Community

You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

  • Accepted 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! 😁


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Screenshots would DEFINITELY be very helpful in helping to find a solution. Sensitive/confidential data can be blocked, removed, or replaced with "dummy data".

  • OK. I'll work on that. It isn't only that it is confidential, but the schedule is very 'wide' meaning the columns I need to look to are spread apart by a bit, so it will be multiple snips...

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    If there are any columns in between that won't necessarily affect this solution, those columns can be hidden to save some space as well.

  • Here is an example schedule. We can pretend these are the only two scheduled Projects. This data is already entered.

    For example on the cost entry, the date and location will be filled in. I am looking for a formula that will fill in the project name.

  • I was originally hoping for a way to do this so that I could use datamesh and not have the formula break when projects are deleted eventually.

  • Ooo update! @Paul Newcome , found the DISTINCT function, and adding that in to keep duplicates out of my 'COLLECTion'.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.