Cross-Reference Formula in Sheet 1 with Multiple Rows that MATCH in Sheet 2
RESOLVED: Scroll down to my second reply to see how I handled this.
In Sheet 1, I have 5 columns that are for required documents to be submitted as part of a development process. In Sheet 2, I collect submissions that people can use to submit a form with a URL to one of those documents. Each form asks for a program code (e.g., 5000).
In Sheet 1, I create a cross-reference formula in each of the 5 columns that looks in Sheet 2 to see if a link was submitted for each of the documents, by matching the Program Code in that row in Sheet 1.
So, Smartsheet should look in the corresponding column (e.g., one of the documents is Budget), if there is a link, and the Program Code in that row in 5000, it should return that link in Sheet 1.
It is likely the documents are submitted at different times, so Sheet 2 might look like this:
Sheet 2 (collects information from form submission)
In this screenshot, there have been three submissions, each with Program Code 5000, but each with a link (and submission date) for the Market Scan, Business Canvas Model, and Budget documents.
PROBLEM:
In Sheet 1, it is only returning the link and the date in the row of the first instance of the Program Code. In the screenshot, since the link to the Business Canvas Model is in the top-most row for Program 5000, it only shows the link for the Business Canvas Model in Sheet 1.
Sheet 1 with Cell Reference Formulas to Sheet 2
There should be a link and date for Market Scan and Budget, but those are both blank.
Here is the formula in the H-Business Model Canvas (Link):
=IFERROR(INDEX({DocumentSubmission-BusinessModelCanvas}, MATCH([Program Code]@row, {DocumentSubmission-ProgramCode}, 0)), "")
{DocumentSubmission-BusinessModelCanvas} refers to the column in Sheet 2 where links would appear if submitted in a form. [Program Code}@row is "5000" in this case, a column in Sheet 1, and {DocumentSubmission-ProgramCode} is a reference to the "Program Code" column in Sheet 2.
As a side note, [Business Model Canvas (Link)]@row just copies whatever is in the column to the left. I had hoped to be able to create a Text-hyperlink to avoid showing the users full URLs. But that functionality (like =HYPERLINK) isn't available yet in Smartsheet.
Any idea why it's only paying attention to the top-most row with the 5000 Program Code (or stops after the first row only? If for some reasons it's only looking at the first (or top-most) instance, how can I get around this so I can look at multiple submissions (given these links are submitted by different people at different times in the development process?
Answers
-
I asked ChatGPT and it suggested using a format like this:
=INDEX({Sheet2 Column A}, MATCH(1, ({Sheet1 Column 2} = {Sheet2 Column B}) * (ISBLANK({Sheet2 Column A}) = FALSE), 0))
So I adapted that for my Market Scan column:
=INDEX({DocumentSubmission-MarketScan}, MATCH(1, ([Program Code]@row = {DocumentSubmission-ProgramCode}) * ("Market Scan" = {DocumentSubmission-DocumentType}), 0))
The references to Sheet 2 are the same, but I added {DocumentSubmission-DocumentType} which is a reference to a column in Sheet 2 in which the user selected the document type from a drop-down list.
So, there are two MATCH criteria (for the Market Scan link):
- The [Program Code]@row in Sheet 1 matches the program code in the Sheet 2 reference: {DocumentSubmission-ProgramCode}
- The Sheet 2 reference {DocumentSubmission-DocumentType} is "Market Scan".
Unfortunately, I m getting an #INVALIDOPERATION. Any confirm I can include two MATCH criteria, and if this is the right format to use?
-
Sorry, was really eager to resolve this —- and came across a solution proposed by @Paul Newcome back in 2020 when someone posted similar question, and it worked perfectly in my situation.
=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)
My updated formula:
=IFERROR(INDEX(COLLECT({DocumentSubmission-Budget}, {DocumentSubmission-ProgramCode}, [Program Code]@row, {DocumentSubmission-DocumentType}, "Budget"), 1), "")
-
Thank you for posting your solution. I'm glad you got this resolved!
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives