Index across sheets

I am attempting a complex formula that will link two SmartSheets. (See below for screenshots)

SPAR Review Testing: Uses a form to collect document submissions. This sheet has a submission date column.
SPAR Reviewer People Matrix: A people matrix that is used as a schedule showing who is responsible for reviewing the documents within a date range. There is a start date column, end date column and a contact column showing the person's email that is assigned

I want to create a formula that will assigned the appropriate reviewer using the People Matrix to the row based on the submission date. I have tried a number of different formulas.

=IF(AND([Submission Date]@row >= {Schedule Start Date}, [Submission Date]@row <= {Schedule End Date}), {SPAR Reviewer 1}, "N/A") --- Received "Invalid Operation"
=MATCH(IF(AND([Submission Date]@row >= {Schedule Start Date}, [Submission Date]@row <= {Schedule End Date}), {Week Cell}), 0) --- Received "Invalid Operation"
=INDEX(COLLECT({SPAR Reviewer 1 Name Range}, {Schedule Start Date}, ">=[Submission Date]@row", {End Date Range 1}, "<=[Submission Date]@row"), ",") ---- Received "Invalid Data Type"

I have tried clicking both the first cell in the row and the entire column in the cross referenced sheet.

I appreciate any help! Been at this for several hours without a solution.

SPAR Review Testing Sheet

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try something like this:

=IFERROR(INDEX({Spar Reviewer 1 Column}, MATCH(MIN(COLLECT({End Date Column}, {End Date Column}, @cell>= [Submission Date]@row)), {End Date Column}, 0)), "")

All comments

Paul Newcome

Try something like this:

=IFERROR(INDEX({Spar Reviewer 1 Column}, MATCH(MIN(COLLECT({End Date Column}, {End Date Column}, @cell>= [Submission Date]@row)), {End Date Column}, 0)), "")

Christina09

Hi Reese,

Try this formula: =INDEX(COLLECT({SPAR Reviewer 1 Name Range}, {Schedule Start Date}, >=[Submission Date]@row, {End Date Range 1}, <=[Submission Date]@row),1)

I took out the " " and added 1 for the row index.

Paul Newcome

@Christina09 I'm not sure that will work because you are looking for an entry where the start date is AFTER the submission date and the end date is BEFORE the submission date. Going the INDEX/COLLECT route means you are going to need to flip those arguments.

Reese

@Paul Newcome - Thank you! The formula worked as is!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.