Reference cell value from a different Smartsheet

Carmen H

Hi Smartsheets community,

I was wondering if anyone would know a way to reference a project ID from a separate Smartsheet by searching for Project name and returning the project ID. I was previously manually searching and linking them across the 2 smartsheets BUT:

1. Sometimes new line items are added to the master list (smartsheet 1) so we want to map to the right project ID in smartsheet 2
2. New items and more detailed tasks will be added to the ECD project list (Smartsheet 2) so if there are larger programs of work that needs to be added back into Smartsheet 1, can we automate this and make sure all information in row is auto-updated?

Smartsheet 1

This is master project list where the project ID is a formula combining column headers: Pillar & Unique # & Team.

This is a portfolio view so we cluster mutiple projects together.

Smartsheet 2

the ECD project/task list which usually would be multiple projects that may have been wrapped up in the master list (Smartsheet 1). Some project names may match to Smartsheet 1 but there can also be additional ones added. What formula can be used to search project name in master list to return project ID?

Can any new ones added into this list, then be auto-added back into Smartsheet 1?

Amit Wadhwani

Hi @Carmen H

This is one of the strongest feature of Smartsheet that I love.

You first need to go to your ECD project/task sheet and create a cross-sheet reference. You can read more about it here - https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Once you have created the reference, you need to use INDEX MATCH formula to bring the project ID against the project name. If you have configured the formula correctly, you will notice that the project ID would be blank for the projects that are not there in the master sheet. In such cases, you have several options

1. Manually copy the project name in the master sheet, which I guess you are already doing.
2. Create a Data Mesh to copy this project name into the master sheet (you will have to use the premium app)
3. Create an interim sheet where the row without project ID gets copied. This interim sheet will have an additional checkbox column that once checked will search the master list and populate the project name if it doesn't exists already.

It is a little complicated. If you are not an advanced Smartsheet user, we can help you build this entire automation.