Return value from another sheet based on 2 criteria


I'm seeking to write a formula that returns the value in the cell for column "hours reported", when the criteria is met in two other columns.

Here are the specifics:

Sheet B is dynamic where the 3 columns referenced are 1) name of the project, 2) is a date and 3) are hours reported

In Sheet A, I want to return the hours reported if the project name is the Project name@row and the date is within the last 7 days.

How would I write this?


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @brhea110891

    An INDEX/COLLECT is the function combo that you need

    =INDEX(COLLECT({Sheet B Hours Reported column}, {Sheet B Project Name column}, [Project name]@row. {Sheet B Date column}, AND(ISDATE(@cell), @cell<=TODAY(), @cell>=TODAY(-7))),1)

    If you are unfamiliar with cross sheet references you may find this post useful.

    Does this work for you?


  • brhea110891
    brhea110891 ✭✭✭✭

    Hi Kelly,

    I really appreciate your help. Below is the formula I wrote, but returner as #UNPARSEABLE. I think my logic is faulty. Below I have added images of sheet B and A, and have explained what I am trying to do.

    =INDEX(COLLECT({PM Weekly Allocation Weekly project hours}, {PM Weekly Allocation Project Name}, [Project]@row. {PM Weekly Allocation Date Start}, AND(ISDATE(@cell), @cell<=TODAY(), @celll>=TODAY(-7))),1)

    Context - My PMO is trying to do some research to see how much time we spend on different projects. Weekly we are tracking the hours we spend on our assigned projects using a Smartsheet form (Sheet B: PM Weekly Allocation). Then, for each Project Manager, I have a sheet, e.g., Phillipe's Projects (Sheet A). On Sheet A, I want to capture, Philippe's hours per project at different time intervals: 1 week ago, 2, weeks ago, 3 weeks ago, 4 weeks ago, and then by month. From these sheets I plan on creating some reports/dashboards.

    Here is Sheet A

    Sheet B

    NOTE - We are also testing this with another setup where I have created a sheet with columns for each reporting week, but this would require more maintenance on my part :-) But in that case, I assume, I would use VLOOKUP Formula. I have not attempted that one yet.

    Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The period after Project@row should be changed to a comma.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!