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
Best Answer
-
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)), "")
Answers
-
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)), "")
-
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.
-
@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.
-
@Paul Newcome - Thank you! The formula worked as is!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!