Matching new webform data to existing data

Sean McNulty

Hi There,

I have a sheet that automatically populates with data from a linked webform. This is the EOI stage. If the EOI applicant meets certain criteria those applications are sent to certain project team members for approval. Once approved by project team members the applicants are sent a round two application form. The data from the round two webform applications then automatically populate into a new sheet in the same workspace.

What I want to do is match some of the details from rows in the approved EOI applications to new data coming in from the round two applications. Are you able to advise how I would do this. At the moment the new form just creates rows below the fields that I want to match from the EOI process. I am thinking it might be the INDEX/MATCH functions at this stage and would be grateful for any assistance that could be provided.

Thanks.

Paul Newcome

Have you looked into Update Requests?

Sean McNulty

Thanks for your reply Paul. I did look into this, however I need the applicant/ supplier to fill in a new form that's on the formal application and I want to match this or join this with existing data I have in another sheet.

Paul Newcome

Ok. Do you have a unique identifier on each row for both sheets?

Sean McNulty

Hi Paul,

Yes I have,

EOI Register has ABN and the matching row in the other sheet is ABN/ACN same data different heading

(basically the ABN Numbers match)

Thanks

Paul Newcome

Ok. So the way an INDEX/MATCH works is the MATCH function automates the row number based on the unique identifier so that the INDEX function will know which row to pull from.

=INDEX({Other Sheet Column To Pull}, MATCH([Unique ID]@row, {Other Sheet Unique ID Column}, 0))

Sean McNulty

Thanks Paul,

I am now totally confused! Essentially what I am trying to do is to merge or sync data from a previous form to the data coming in from another form. So in the snippet below rows 1 to 84 are copied cells from the EOI register and cells 85 and 86 is new data coming in from the Formal Application form attached to this below sheet.

As an example row 22 (Freedom Constructions Queensland) is the data copied in from the EOI register and as you can see when we have sent the new Formal Application form from the new sheet the data is added to row 85. I somehow need to bring row 22 and row 85 together including the attachments. The data for both EOI and the Formal Application will continue to be collected through these forms for at least the next six months so it is not static and ideally I need it to auto populate the data comes in.

Thanks in advance. If you would like a direct copy of the SmartSheet I can also share that with you.

Sean.

Test Data joining EOI Register _Formal application.xlsx

Paul Newcome

Now I am confused. In one post you talked about 2 different sheets, but in your last post you talked about 2 different rows on the same sheet. Are we working in one sheet or two?

Sean McNulty

Hi Paul,

Form/Sheet 1- Register 1

Form/Sheet 2- Register 2

Customer fill out form that populates Register 1. If data matches a certain criteria it triggers a workflow for approval and once approved copies selected data from the row on register 1 to Register 2.

Once data is visible on register 2 second form is sent for additional data. When form completed a new row is created in Register 2 at bottom of the sheet.

I then need to manually match the row data that has dropped in from Register 1 with the new data that populates in register 2.

Not sure if this is achievable and also how/if attachment across to row can merge when completing such a task?

Hope this makes sense

Cheers

Andrée Starå

Hi @Sean McNulty,

Second form

I’d recommend using an Update Request instead if possible for the second form process like Paul mentioned earlier because then you don’t need to match the rows together.

Is there a reason why that wouldn’t work, and if yes, what is the reason?

Re. attachments

Unfortunately, it's not possible at the moment to merge different rows attachments to the same row, but It's an excellent idea!

Please submit an Enhancement Request when you have a moment

It would be a manual process to save from one row locally and then upload those to the "main" row, but if you use an Update Request, they attachments will already be on the same row.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Sean McNulty I do feel like an Update Request would work for this. I know you said you wanted to send a second form, but the Update Request is filled out as a form. This would allow the person to update the same row and would make the process much easier if it is an option. This would also allow the user to attach new items to the row since you cannot merge row attachments.

Sean McNulty

Thanks Gents,

It sounds like it would work if you only had to updates the same columns and or cells.

The problem I have is the first form has approx 20 data sets and the second form has over 50 additional data points How can I link the second form to a request update?

Thanks for your assistance

Andrée Starå

@Sean McNulty

You're more than welcome!

You'd use the Update Request instead of the second form, and you can select which fields should be included in the request. Similar to how you set up the form.

Make sense?

More info about the update request:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/update-requests

Jason Albrecht

G'day Sean,

I know it's a few years since you posted the above question.

As an Aussie, I have a suspicion you can help me with an ABN dilemma, and would like to ask you a few questions.

I have also used the 'update request' solution and could possibly help with formatting the emailed (form) requests.

Hopefully you get this notification and are willing to chat offline.

Jason