Autopopulate Cells Else New Entry

I am trying to create a New Order Entry form. I have a dropdown list that references our customer sheet. If the customer is not listed, you can choose "(NEW CUSTOMER)", which allows more fields to open up to add the new customer info.

If the customer is listed, can I have the address autopopulate into the appropriate fields so that when I generate a document, all of the customer's information is there.

I know how to VLOOKUP or INDEX/MATCH, but if there is no match, it remains blank.

Find more posts tagged with

Forms

Sheets

Formulas

Automations

Cell linking

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

Heald33

Paul Newcome

Try wrapping your VLOOKUP in an IF statement so that

IF the cell equals "(NEW CUSTOMER)" then output the new customer info and the "value if false" portion would be the VLOOKUP.

Heald33

I understand that, but if new information is entered into the form, will that information override the VLOOKUP function? What value do I enter into the formula so it will ignore the formula if the VLOOKUP is true?

Paul Newcome

It will only override if "New Customer" is selected. If you would rather do the opposite, you can still use the IF statement that says something along the lines of

IF the VLOOKUP <> "" (is not blank) then output the VLOOKUP, otherwise output the entered data.

Heald33

Can you please give me an example of the formula for if the cell is not "New Customer"?

Paul Newcome

It would look something like this:

=IF(…………, …………….., IF([Billing Customer]@row <> "(NEW CUSTOMER)", VLOOKUP(………………..), "output if it is a new customer"))

Heald33

The "output if it is a new customer" part is where I'm hanging up. If it is a new customer, I want the values in the intake form to override this.

Let me change it up a bit…

If the checkbox for "Billing same as Shipping" box is checked, it copies all of the shipping info into the billing fields, else you can add the different shipping info.

Paul Newcome

If you want the submitted values to override, you would use a cell reference to whatever cell has the input data.

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.