How to auto populate approval from one sheet to another?

Sheet 1 has the column of

Name (Text)
Email (Contact List)
Request (Text)
Approver (Text)
Approval (Yes/No - Dropdown)

Sheet 2 has the columns

Approver Name (Text)
Approve? (Yes/No - Dropdown)

Once the form for sheet 1 is submitted I have it automated that it'll trigger an email to the 'Approver' contact list to open up Sheet 2 form and approve the request or not (PDF attachment to view the request via document generation).

Is there a way for me to populate onto Sheet 1 that whatever the response is on Sheet 2 it'll populate to the corresponding row in Sheet 1?

Example: Row 1 someone submits form on sheet 1, the approve approves it on sheets 2 and will populate back onto sheet 1 with the response of Yes/No in the 'Approval' column.

not sure if Data Mesh, or automation within the sheet is the best approach but would like to see what the options are if possible.

Find more posts tagged with

Forms

Sheets

DataMesh

automated archive

Accepted answers

All comments

NickStaffordPM

Yes you will use a VLOOKUP - you could also use an INDEX/MATCH combo, but VLOOKUP should work for you here.

=VLOOKUP(Approver@row,Sheet 2,2,FALSE) should work as long as the name in Approver (sheet 1, text) and Approver Name (sheet 2, text)

let me know if this doesnt work or if you have any additional questions!

Tummi

https://community.smartsheet.com/discussion/comment/470510#Comment_470510

With a VLOOKUP though. Wouldn't it have to be based on unique finds? I'm concerned with the potential of the same 'Approver' coming in approving requests at different times and I'm not sure if VLOOKUPS would be able to catch that.

NickStaffordPM

In that case, how will your sheet 2 distinguish this? Will you possibly have several lines in sheet 2 where the Approver Name is the same name?

Tummi

https://community.smartsheet.com/discussion/comment/470529#Comment_470529

It would be safe for me to assume that would be the case. There will be multiple approver names, but over time the same approver name can appear down the line.

NickStaffordPM

Unless you CONCAT the name with a date on both sheets to make a unique identifier, I am not sure how you will distinguish one approval from another over time if the second sheet only has two columns (name and approval status). One option is that once, on your sheet 2, the approval is Yes, it archives on the following Monday or something (moves to another archived approval sheet, so that way if someone is an approver twice, their name should only ever be actually on there once.

Maybe thing about how you can create a unique Identifier and if you are open to creating an archival processes within this.

Tummi

https://community.smartsheet.com/discussion/comment/470597#Comment_470597

Ahh makes sense, didn't think to have a unique identifier. Best case use I think I can make an auto number column to identify each row as unique to then VLOOKUP the unique identifier in to pull the response of approval. At least I think that's the best case usage.

Thanks for the insight

NickStaffordPM

You will need to ensure the same unique identifier is on both Sheet 1 and Sheet 2 for this to work - if you have any other questions or roadblocks just message back here and I'll support as I can

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.