Pulling data from one form to another

claire bell

I am creating a sheet that will collect data from multiple regions through a specific contact. I currently do it through automation. When I fill in a specific region the email associated with this region is in the automation that I created and it populates the particular cell, and sends a form to that person. I am expanding this out to an addition 60 regions. I really don't want to have to write a rule for another 60 regions so I was wondering if there is an easy way of having a master spreadsheet of contacts for the regions and a rule/formula working so that when I put in a particular region it can pull the contact from that region from another sheet. Is there a way to do this or is this not possible. Keeping my fingers crossed it is.

Kelly Moore

Hey @claire bell

Could you create another sheet that contains the email address and the associated region. I'll call it your Lookup Sheet

Then, on your sheet where you are currently entering the Region, you will build an Index/Match formula in your current email column. Your automation will refer to 'specific cell', and whatever name the formula brings in will be where the email will be sent

In case you are unfamiliar- because data needs to be pulled from a different sheet into the main sheet, smartsheet refers to the data being referenced as cross-sheet references. You have to physically build cross sheet references, you cannot just copy paste this formula into your sheet.

Below is your formula. Build your cross sheet references by clicking the Reference a Different Sheet link that will be available in your formula window. I named the references so you could use them as guide when building your formula

=INDEX({Lookup Sheet Email column},MATCH(Region@row, {Lookup Sheet Region column},0))

Will this work for you? @mention me if you need any help building your cross sheet references
Kelly

PS. If needed, you may find this link below helpful

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references