0

I have created a form in Google Sheets:

  1. Row 5, Client, is a dropdown populated by existing clients.
  2. Rows 6 to 9, Street Address, City, State, and Post Code, use VLOOKUP formulas to retrieve data from a separate sheet based on the Client
  3. The Client dropdown is configured to "Show a warning" when invalid data is entered so that new clients can be added manually, as needed.
  4. The image below shows sample data with a manually entered new client, "BurgertTory (Southbank)":
    1. The validation warning shows on the Client dropdown cell (arrow in image).
    2. The VLOOKUP functions in the address rows each return a #N/A error because they fail to match the client name.

Form

Is there a way to create a toggle or checkbox so that the new client's address information can be entered in the address rows?

1 Answer 1

-1

Instead of including VLOOKUP formulas in the address rows, take a different approach.

Insert a row above Street Address (which will move it down to row 7.

Put an array formula in cell D6 that will only populate the address rows if the client name exists in the master sheet. Otherwise, it will do nothing leaving the rows available for data entry.

Example

  • For simplicity, the formula and the lookup data are in the same sheet in this example. You can separate them and adapt the formula to match your own lookup data.
  • The formula cell, c3 in the example` is always blank, however, you can add a string for new clients and/or existing clients.
  • If left blank, you may prefer to hide the row.

Lookup Data

F G H I J
1 Clients Street Address City State Post Code
2 Toys for Youz 12 Elm St. Yonkers NY 10470
3 Shake Cabin 13th St. W Manhattan NY 10036
4 BurgerTory (Northbank) 1208 Surf Ave Brooklyn NY 11224

Form

     A  B           C       D                                    
1
2 Client: (Dropdown) Shake Cabin
3 Formula
4 Street Address: 13th St. W
5 City: Manhattan
6 State: NY
7 Post Code: 10036

Formula in C3

=IFNA(IF(MATCH(C2,F:F,0),
  {"";TRANSPOSE(FILTER(G:J,F:F=C2))}))

Sample formula in C3 showing the effect of using a new client versus an existing client and also hiding the formula's row

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.