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
