INDEX Function | Smartsheet Learning Center
https://help.smartsheet.com/function/indexReturns an item from a collection based on provided row and column indexes
Conditional Formatting Referencing Other Sheets
ChaunyT
✭✭✭
Hello,
I'm building a process and want to add some automations to minimize errors. I have a column for "partner" that is a filled in via a form on sheet 1. I have a list of partners that we do not work with in sheet 2. I would like for the row (or cell) in sheet 1 to change colors if the form response is one of the names listed in sheet 2 (even better if it could just change a status without the color change being needed). Is this a functionality anyone else has managed to do?
I looked into cell linking and even some of the cell referencing options but I'm not seeing anything that quite matches up and would love to know if anyone has made this work.
Answers
-
Hi @ChaunyT
I think an INDEX MATCH formula would be one way to accomplish this. You could add a column on Sheet 1 for Partner Approved Yes/No (or something like that). You'd leave that field off the form. When the form is submitted the new column would look up the Partner name against sheet 2 and return maybe a check box or Yes/No status.
However, since its possible to enter a non-approved partner, I'm guessing you aren't using a dropdown for the Partner name on Form. You might want to consider a drop down showing only approved partners. Or have a dropdown with all partners, regardless of approval status, just to ensure the name gets entered correctly on sheet 1 so its compared accurately on sheet 2.
Here's an article on index/match in case you need it.
I hope that helps.
Matt
-
Hello Matt,
Okay, I tried the Index Match but adding the yes no column would be perfect! I'm going to try it out! Thanks for this!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …