Master Contact List used for multiple sheets.

Isaac Gallardo

I would like to know if there is a way to have a Master Contact List that could be used as a reference for all other sheets. 

e.g. 

In one sheet a have a column that says, Owner: Quality Manager. But, since I have multiple sheets with the same Owner column. I wonder if I can relate this Quality Manager to a Master Contact list that says Quality Manager=" a contact/email address". So I don't have to update all my sheets if someone changed position. In addition, I want to use a workflow that assigned This Owner to generate updates or approval. Same conditions as contact list column in a regular Smartsheet. But, in this case. The contact information will be pulled from another sheet.

Thank you!

Paul Newcome

You would need to add in one more column to the sheet you are wanting to pull this data to (I will call it [Assigned to] for the purposes of this example). You can drop that right next to the Owner column.

In this new [Assigned to] column (that is contact type), enter a formula like this...

=INDEX({Different Sheet Names}, MATCH(Owner@row, {Different Sheet Tittle}, 0))

You will also want the Names column on the "Different Sheet" to be a contact type of column.

What this will do is look for that title and pull the contact info from the Names column of your table. This means any time you update the table, the sheet you are pulling the data too will automatically update.

This [Assigned to] column is what will drive anything Contact related.

Paul Newcome

Yes. You would create the directory with the reference information and the information you would want to pull.

Then on each of your other sheets you would use cross sheet references in an INDEX/MATCH type of formula to pull the appropriate data.

If you want to go ahead and set up a mock directory and provide screenshots of that along with screenshots of the sheet you want to pull this data to with sensitive/confidential data blocked, removed, or replaced with "dummy data", I would be happy to help you put the formula itself together as well.

Isaac Gallardo

Paul, Thank you for replying back. Now that I have completed my database. I would like to share with you some dummy data, and see if you can give me a hand using INDEX/MATCH.

In the attached screen capture you will see the first row of my database and on a different sheet I have the name of the Beverage Manager. How do I tell the database that Beverage Manager means a contact name?

Thank you!

Test.jpg

Paul Newcome

You would need to add in one more column to the sheet you are wanting to pull this data to (I will call it [Assigned to] for the purposes of this example). You can drop that right next to the Owner column.

In this new [Assigned to] column (that is contact type), enter a formula like this...

=INDEX({Different Sheet Names}, MATCH(Owner@row, {Different Sheet Tittle}, 0))

You will also want the Names column on the "Different Sheet" to be a contact type of column.

What this will do is look for that title and pull the contact info from the Names column of your table. This means any time you update the table, the sheet you are pulling the data too will automatically update.

This [Assigned to] column is what will drive anything Contact related.

Isaac Gallardo

Thank you very much, very well explained!

Paul Newcome

Happy to help! 👍️