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?

Find more posts tagged with

Accepted answers

All comments

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.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Does this work for you?

Kelly

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.