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

H. Obringer

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.

Paul Newcome

Ok. So on the Cost Entry sheet where you want the data pulled to (your target sheet), you would use something like this...

=JOIN(COLLECT({Schedule Sheet Project Name}, {Schedule Sheet Location}, @cell = [Cost Location Entered]@row, {Schedule Sheet Date Column of Choice}, @cell = [Cost Date Entered]@row))

This will find the row where the cost date and location match and pull the associated Project Name.

H. Obringer

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

H. Obringer

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

H. Obringer

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.

H. Obringer

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.

Paul Newcome

Ok. So on the Cost Entry sheet where you want the data pulled to (your target sheet), you would use something like this...

=JOIN(COLLECT({Schedule Sheet Project Name}, {Schedule Sheet Location}, @cell = [Cost Location Entered]@row, {Schedule Sheet Date Column of Choice}, @cell = [Cost Date Entered]@row))

This will find the row where the cost date and location match and pull the associated Project Name.

H. Obringer

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

H. Obringer

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

Paul Newcome

Happy to help! 👍️

April Barrera

Paul, @Paul Newcome

I am trying to do this same thing and getting an error. Can you assist?

My formula is =JOIN(COLLECT({Designated Recipient Focus Area}, {Designated Recipient Focus Area 1}, @cell = [Focus Area]@row, {Designated Recipient Service Line}, @cell = [Service Line]@row))

For the first Designated Recipient Focus Area I highlighted the who 3 columns in the PM List Sheet. For the Second Designated Recipient Focus Area 1, I only highlighted the Focus Area Column. For the Designated Recipient Service Line I only highlighted the Service Line Column.

I am trying to get the assigned project manager to populated in the field based off two criteria - the focus area and service line.

Example

Main Sheet (Target Sheet)

Focus Area Service Line PM

Healthcare Administration (where formula is to add PM name)

PM List Sheet

Focus Area Service Line PM

Healthcare Administration April B.

Gov. Svc Accounting Jenny C.

Please let me know what is wrong with my formula.

Thanks!

Paul Newcome

@April Barrera I imagine you are probably getting the incorrect argument error?

All ranges must be of the same size and shape. So if you have one range that is 3 columns wide, then the rest of the ranges must also be 3 columns wide.

April Barrera

@Paul Newcome Thank you!

@Paul Newcome I am using this column in a report to only allow the current user to see the report of the lines assigned to them, but it is not populating. Is this not possible?

Paul Newcome

@April Barrera It should be possible. Do you have multiple people assigned to the same row? Is it working for any of the rows at all? Are you able to provide a screenshot of the setting where it determines which user to base the report on?

April Barrera

@Paul Newcome The formula works but the pullover is no longer an email even though both columns are set up as contact lists. Since it does not populate as an email, it is not recognized in the report as a current user until I change the text to an email by retyping it in the column.

Why is the column populating as text instead of an email?

Paul Newcome

@April Barrera That is because you cannot populate multiple usable contacts in a single cell via formula unless you are doing a direct reference.

In your screenshots I don't see any columns for focus area or service line.