Automatically Change "Modified by" email item into a Name

Claire Diener

Hello!

My team is creating a form to collect, track, and act on paid-time-off requests and part of the system we're creating is making a form for folks to submit their requests through.

We need to share parts of the content of this sheet with multiple different parties so plan to create some reports and dashboards to share that information with them. However, we need a name associated with each request and our organization has sometimes funky email addresses for people (so can't just rely on the modified by column), and we really don't want to have to ask for people's names and email's within the form. To get around this we've made the form require people to be signed into Smartsheet, but that only seems to give us their email???

So the question is: how can we automatically get the name of the person submitting the form without having to ask for it?? If the form knows the person's email just by requiring the person to be signed into Smartsheet, should it be able to give us the name too?

As context, our organization is not on Microsoft 365 so we can't implement any pivot apps.

Thanks for the help!

Claire

Paul Newcome

I am not sure if a 3rd party app such as Zapier or the API would work for this as I don't have much experience with either.

 

I do know however, that you can create a directory of sorts with everyone's name in one column and the corresponding email in another column. You can then use an INDEX/MATCH formula to look at the email and hit against that directory to pull the correct name into an additional column that you don't need to have included on the form.

 

Would something like that work for you?

Andrée Starå

Hi Claire,

I'd recommend Paul's suggestion. Seems to be the easiest for what you need.

Zapier can also be an option if Smartsheet can't do exactly what you want.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Claire Diener

Hi Paul and Andree,

 

Yes, I think that could work, though it is a bummer to have to collect hundreds of names and emails into a sheet and keep that updated.

If the new sheet we make to capture names and emails is called "Pivot" and the columns are "Name" and "Email" and the sheet I want the formula in has a column for "Pivot Name" and "Modified by" (because the email will be collected via the form, and will be captured in the modified by column), what would the formula be in the "Pivot Name" column/cells?

Thanks!

Paul Newcome

It would look something along the lines of this:

 

=INDEX({Pivot Sheet Name Column}, MATCH([Modified by]@row, {Pivot Sheet Email Column}, 0))

 

The INDEX function pulls data from a range based on a row number and an optional column number. Since we are only referencing single columns at a time, we can leave the column number out.

 

The MATCH function returns a number based on where within a range the specified data is found. Basically if the email is the third one down on the list, the MATCH function will return a 3 when looking at a single column.

 

This provides the appropriate row number for the INDEX function to pull from. It is very similar to a VLOOKUP function, but is MUCH more flexible.

Claire Diener

Thank you!!

Paul Newcome

Happy to help! 

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Krista Meston

Hi All, This is clearly not an option for an organization with 1000's of employees. Our emails do not contain our names so really the fields are useless to me. Any other ideas?