Index across sheets


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

  1. SPAR Review Testing: Uses a form to collect document submissions. This sheet has a submission date column.
  2. 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.

  1. =IF(AND([Submission Date]@row >= {Schedule Start Date}, [Submission Date]@row <= {Schedule End Date}), {SPAR Reviewer 1}, "N/A") --- Received "Invalid Operation"
  2. =MATCH(IF(AND([Submission Date]@row >= {Schedule Start Date}, [Submission Date]@row <= {Schedule End Date}), {Week Cell}), 0) --- Received "Invalid Operation"
  3. =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


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!