Trying to gather cell info from 2 different unique id's on different sheets

chhunt

The following sheets in my description are in 3 different workspaces if that makes a difference. We also do not have the licensing for control center if that is an easier solution, so im working with what Ive got.

I have a sheet called "Project Intake" where most projects source from. There is an auto number column called "Project ID" for each row to indicate its unique numbered project (SM-001). Within that Project Intake sheet there is a column for the project managers name. When a new project row is added, it will copy that row to a sheet called "Implementation Metrics" that is used by another group.

Implementation Metrics has an index(collect) column formula on the Project Manager column pointing back to the Project Intake sheet to keep it updated in the chance the PM is changed.

=INDEX(COLLECT({Project Intake Sheet | Project Manager}, {Project Intake Sheet | Project ID}, [Project ID]@row), 1)

I have another sheet called "WAN Metrics" that is used by a sub-group that does side projects that is built the same way the Project intake sheet works, which also copies new rows into the Implementations Metrics sheet, but uses the unique Project ID as WAN-001.

The problem im running into is that the index(collect) column formula on the implementation metric sheet points to the Project Intake sheet and any WAN project that is added shows an #Invalid Value error, which im assuming is due to the Project intake sheet does not know of the WAN id's existence. Im still new enough to smartsheet I cant figure out how to tie multiple index(collect) together to look at multiple sheets. Ive tried tinkering with if(contains), but it seems it want to look at a specific cell vs a row.

The logic im trying to use (having the formula on the implementation Metrics project manager column) is:

If the Project ID contains "SM", get the project manager listed in the Project Intake sheet, or if the Project ID contains "WAN" get the project manager listed in the WAN Metrics sheet

chhunt

I figured out the formula, I just needed to step back and add a little bit at a time. The following formula resolved my issue:

=IF(CONTAINS("WAN", [Project ID]@row), (INDEX(COLLECT({WAN Metrics | Project Manager}, {WAN Metrics | Project ID}, [Project ID]@row), 1)), (IF(CONTAINS("SM", [Project ID]@row), (INDEX(COLLECT({Project Intake Sheet | Project Manager}, {Project Intake Sheet | Project ID}, [Project ID]@row), 1)))))

Jennifer Kurtz

Hi @chhunt !

I think you could use the IF function to tell Smartsheet which Index(Collect( to follow to grab the right data.

This would look something like this:

=IF(CONTAINS(["SM", [Project ID]@row), INDEX(COLLECT({Project Intake Sheet | Project Manager}, {Project Intake Sheet | Project ID}, [Project ID]@row), 1, IF(CONTAINS("WAN", [Project ID]@row), INDEX(COLLECT({WAN Metrics Sheet | Project Manager}, {WAN Metrics Sheet | Project ID}, [Project ID]@row),1

(…I almost always leave the parentheses off the end because I easily get lost on how many there should be! ;>)

Does that help?

Dan Curl

So the Sheet "Implementation Metrics" has rows that are SM-Projects and rows that are WAN-Projects correct? There are not rows that are both, correct?

Have you attempted something like this?

=IF(LEFT([ProjectID]@row,2)="SM",{{SMProjectSheet-CollectFormula}},{{WANProjectSheet-CollectFormula}})

chhunt

I figured out the formula, I just needed to step back and add a little bit at a time. The following formula resolved my issue:

=IF(CONTAINS("WAN", [Project ID]@row), (INDEX(COLLECT({WAN Metrics | Project Manager}, {WAN Metrics | Project ID}, [Project ID]@row), 1)), (IF(CONTAINS("SM", [Project ID]@row), (INDEX(COLLECT({Project Intake Sheet | Project Manager}, {Project Intake Sheet | Project ID}, [Project ID]@row), 1)))))

Jennifer Kurtz

Awesome - it's satisfying when you figure out a formula!

chhunt

Thank you for the responses Dan and Jennifer, you're posts came in as I was adding my last comment. Ill keep what you provided in mind in case I have any other issues.

Michelle Choate 2

You are already saying exactly what you need to do - Multiple IF statements.

IF(CONTAINS("SM",[Project ID]@row),INDEX(COLLECT{Project Intake Sheet | Project Manager}, {Project Intake Sheet | Project ID}, [Project ID]@row), 1),IF(CONTAINS("WAN",[Project ID]@row),INDEX(COLLECT{WAN Metrics Sheet | Project Manager}, {WAN Metrics Sheet | Project ID}, [Project ID]@row), 1)