nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Copy the most recent application cell data from another sheet

Hauss

I have a master sheet with promotion applications linked to a SmartSheet form. Each applicant is identified by a ID number.

There is a second sheet with the managers evaluation form. I don't want to send an update request or approval. I then want to copy the cells from the managers evaluation back in on the same row as the application in the master sheet. This way it can be viewed by future approvers.

However, each applicant may apply for multiple promotions over time. I want the copied cells to match the correct application in the master form. How would you add a MAX or COLLECT value to a formula like this to make it work?

=INDEX({Leader Evaluation - Tech Advancements Range 4}, MATCH(uID@row, {Leader Evaluation - Tech Advancements Range 3}, 0))

Find more posts tagged with

Formulas

approvals

Workflows

Accepted answers

Shaz

@Hauss This workflow makes it even easier, you dont try to index based on date. But your formula is using uID, thats not unique.

Create an Application ID column auto number in Masters sheet where request is received.
Create an Application ID Text/Number column in Leaders Evaluation sheet.
Add Form Query String to prefill Application ID ( add ?Application%20ID={{Application ID}}
Form Link inside messae should look like https://app.smartsheet.com/b/form/019f36........?Application%20ID={{Application ID}}
Now the form will be prefilled with application ID for manager, you can hide it if you dont want them to see just to avoid editing it.
Now index formula based on application ID. =INDEX({Leader Evaluation -Tech Advancement Range}, MATCH([Application ID]@row,{Leader Evaluation Submission ID Range},0))

Hauss

Thank you for this! I really appreciate it.

All comments

Paul Newcome

Are you able to provide some screenshots (mock data is fine) for context?

Do you have an unique identifier per submission?

Shaz

Try This

=INDEX({Leader Evaluation - Tech Advancements Range 4},MATCH(MAX(COLLECT({Manager Submission Date Range},{Manager Evaluation uID Range}, uID@row)),{Manager Submission Date Range},0))

There should be a submission date column,

First collect the dates and find max for the same ID. This will give the search date of matching row.

Paul Newcome

@Shaz This will only work if one submission is made per day. If there are multiple submissions per day, you will bring in the same data on every row because the MATCH is only bringing in a date.

For example, ID1234 submits today then ID5678 also submits today. The MAX/COLLECT brings back today's date, but the MATCH will pull from the first match it encounters. This means that if new submissions are at the bottom of the sheet, the data brought over for ID5678 will actually be from the row submitted by ID1234 because that was the first MATCH on today's date.

Shaz

@Paul Newcome agreed but I look at the context also.

1. Promotion applications never submitted morethan once on same day.

2. Keeping a manual submission date on a form is not a professional approach, they should be auto column (Created date). So even if two submission happens on same day, formula still works

3. Match formula takes first match is correct. Then it should be linked with another reference like manager ID inside COLLLECT()

Shaz

So the correct formula

=INDEX(COLLECT({Leader Evaluation - Tech Advancements Range 4}, {Manager Evaluation uID Range}, uID@row, {Manager Submission Date Range}, MAX(COLLECT({Manager Submission Date Range}, {Manager Evaluation uID Range}, uID@row))), 1)

Paul Newcome

@Hauss I see you mention an applicant id, but do you have a submission id? How are the managers indicating which submission exactly their form submission is for?

Hauss

Currently, I only have an applicant ID. When that applicant fills out the form, there is an automation that sends the manager another form for evaluation (on a separate sheet). The manager does enter the applicant ID on the evaluation form. I was hoping to use the applicant ID to link it back.

How would you suggest setting up a second ID? I see SmartSheet can populate a row ID. How would I get this same ID on the second form?

Shaz

@Hauss This workflow makes it even easier, you dont try to index based on date. But your formula is using uID, thats not unique.

Create an Application ID column auto number in Masters sheet where request is received.
Create an Application ID Text/Number column in Leaders Evaluation sheet.
Add Form Query String to prefill Application ID ( add ?Application%20ID={{Application ID}}
Form Link inside messae should look like https://app.smartsheet.com/b/form/019f36........?Application%20ID={{Application ID}}
Now the form will be prefilled with application ID for manager, you can hide it if you dont want them to see just to avoid editing it.
Now index formula based on application ID. =INDEX({Leader Evaluation -Tech Advancement Range}, MATCH([Application ID]@row,{Leader Evaluation Submission ID Range},0))

Hauss

Thank you for this! I really appreciate it.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.