Vlookup on sheet using form submission

I am using a form to add a new row to a sheet with each submission. I have a field on the form that the user selects if this is a new contact or previous contact. If it is a previous contact, I want to pull information from another sheet for this contact.

To do this I have a Vlookup formula that I was able to get working and populates the fields pulling the desired information from another sheet when this particular option is chosen on the submission form. However, when the other option is chosen for that field on the form the data in those cells is overridden and Vlookup stops working for future entries.

Example of when it works:

=IF([Request Type]14 = "Prior Resource - See Outside Resources Sheet", (VLOOKUP([Full Name]14, {Resources Database (Demo) Range 1}, 9, false)))

Once form submission "[Request Type] = New Resource" the user will enter the data for the fields that the Vlookup is in for Prior Resource selection.

Then next entry will not use Vlookup formula. How do I get the entire sheet to use formula regardless of last entry?

Find more posts tagged with

Forms

Formulas

Accepted answers

All comments

Andrée Starå

Hi @Jwindels

Unfortunately, it's not possible at the moment to force the formula if it breaks the pattern, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could have two so-called helper rows with the formulas and then have the submission come in at the top and then move it down to another section. That would keep the formula working.

Another option might be to create helper columns where they add the new resource, so it doesn't affect the VLOOKUP formula. We could maybe combine it with the Form Logic feature.

Make sense?

Would any of these options work/help?

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!

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.