Index Formula?

Tamara Cook

Hi Smartsheet Community,

Our Legal team has a sheet which they use to track all of their deals. We recently implemented the Salesforce/Smartsheet Connector which is great except for one issue: there is no field in Salesforce for the Legal Reviewer. I'm now looking for a way to auto-populate that Legal Reviewer field based on various criteria. I created this index sheet with the hopes of being able to write an INDEX formula but the formula will have to search across multiple criteria. For example, if Region = EMEA, Industry = AEC and Country = Sweden, then return [Legal Reviewer]'s name. The problem is, the combination of criteria used is different across each Region. Is this even possible? Any insights you have is greatly appreciated!!

Tamara

Kelly Moore

Hey @Tamara Cook

Without seeing the structure of your target sheet where you want the name inputted, I'll make some assumptions about your data. I will assume your Legal partners are the reviewer's name you need? I'll assume this is a CONTACT column. I'll assume the Reviewer's column in your target sheet is also a CONTACT column (it must match). I will assume your target sheet is going to use all of the columns that you presented in the Index sheet.

=INDEX(COLLECT({Index sheet Legal Partners}, {Index sheet Region}, [target sheet region]@row, {Index sheet Industry}, [target sheet industry]@row, {Index sheet Country}, [target sheet country]@row, {Index sheet Manager}, [target sheet manager]@row), 1)

The 1 is part of the INDEX function and indicates the position of the answer in the specific collected list for each row.

See if this works for you. Remember with cross-sheet references they have to be individually built by inserting the reference into the formula. As a good practice, if you don't do it already, when working with cross sheet references, change the name from the generic smartsheet range number name to represent the specific sheet - column name you are referencing. It will make it easier for you (and the community if you need help) to understand your formula.

Let me know what we need to do to further tweak your formula.

Kelly

Kelly Moore

Hey @Tamara Cook

Without seeing the structure of your target sheet where you want the name inputted, I'll make some assumptions about your data. I will assume your Legal partners are the reviewer's name you need? I'll assume this is a CONTACT column. I'll assume the Reviewer's column in your target sheet is also a CONTACT column (it must match). I will assume your target sheet is going to use all of the columns that you presented in the Index sheet.

=INDEX(COLLECT({Index sheet Legal Partners}, {Index sheet Region}, [target sheet region]@row, {Index sheet Industry}, [target sheet industry]@row, {Index sheet Country}, [target sheet country]@row, {Index sheet Manager}, [target sheet manager]@row), 1)

The 1 is part of the INDEX function and indicates the position of the answer in the specific collected list for each row.

See if this works for you. Remember with cross-sheet references they have to be individually built by inserting the reference into the formula. As a good practice, if you don't do it already, when working with cross sheet references, change the name from the generic smartsheet range number name to represent the specific sheet - column name you are referencing. It will make it easier for you (and the community if you need help) to understand your formula.

Let me know what we need to do to further tweak your formula.

Kelly

Tamara Cook

Wow, Kelly you are a huge life-saver! That solution worked like a dream. Also, thanks for the tip about renaming the cell references. Super important with a lengthy column like this one.

I soo appreciate your help!