Autopopulate Contact Field with "Created by"

Now that formulas are allowed in contact fields, I can't quite figure out the right formula to use:

I have a contact field with a pre-populated list of users, I have a webform that forces the user to sign in so therefore have a nice system "Created by" column. I would like to write a formula in that contact column to be the formatted contact version of the username in from Created By.

Any help is appreciated!

Karen

Find more posts tagged with

Comments

Paul Newcome

The Created By column can be used in Automations to send alerts, reminders, etc to people. Is there a specific reason you need to move this information to a Contact type column?

cantpickname

First is to use in a "Current User" view within a report.

Second is both a formatting preference, as well as being able to update the specific field to another contact if needed (e.g. I submit the form but on behalf of someone else, since you can't edit a system column I could then update that contact column to the correct person).

Paul Newcome

Ah. Ok. That makes sense.

You would need to create a directory somewhere either on the same sheet or a different sheet. Then you would use an INDEX/MATCH to pull the contact info along the lines of...

=INDEX([Directory Contact Column]:[Directory Contact Column], MATCH([Created by]@row, [Directory Email Column]:[Directory Email Column], 0))

The catch is... Once you manually update this cell, it overrides the formula and deletes it from the cell. That means it could potentially impact the auto-fill feature for new forms that are submitted.

If that is a concern, then you could build in a workaround. Give them a field to add in a user if different from themselves.

Then adjust the formula to look at either the Created By column or this new field if they don't match.

cantpickname

Thanks all. For now the user group is fairly small and I think I will just add that contact column to the form and force the person filling out to select from the drop-down. It's manual, but gives the option of picking themselves or another name if they need to fill out for someone else.

Paul Newcome

Happy to help!

Feel free to revisit this thread if the user group expands to an unmanageable level, and we will see if we can help set something up for you.

Sign in to join the conversation!

Quick Links

Trending discussions

How to prevent or recover deletion of rows
Is it possible to prevent the deletion of rows in a sheet? Specifically, in cases where a user legitimately requires editor permission to the underlying sheet and where the row cannot be locked (as continued editting of the row is required.) If not, what are some strategies for recovering a row that has been inadvertently…
Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…