Google Sheets中结合Array Formula使用下拉列表求助

Hey there! Let's fix this dropdown list issue you're having with Google Sheets and Form submissions. I see you tried using an ARRAYFORMULA, but it's only filling text values instead of setting up the actual dropdown (data validation) functionality you need. Let's break down the correct solutions to handle form-submitted new rows automatically:

Method 1: Set Up Auto-Extending Data Validation (No Formula Needed) #

This is the simplest way to ensure every new row added by your form gets the Open/Closed dropdown:

• Select the entire column where you want the dropdown (e.g., E2:E—start at row 2 to skip your header)
• Go to Data > Data validation
• In the popup:
  • Under Criteria, choose List from range
  • Enter the range with your options: Sheet3!$B$3:$B$4 (the dollar signs lock the range so it doesn't shift)
  • Pick your preferred On invalid data behavior (like "Reject input" to enforce only Open/Closed)
  • Check the box for Apply to all other cells in range with same settings
  • Hit Save
• To make sure new form rows inherit this validation, go to File > Settings > Edit and check Automatically extend ranges when new data is added—this will push the data validation down to every new row your form creates.

Method 2: Combine ARRAYFORMULA with Data Validation (For Default Values) #

If you want the dropdown to default to "Open" when a new form row is added (but still let users switch to "Closed"), use this approach:

1. First set up the data validation exactly as described in Method 1
2. In cell E2, paste this array formula:

   =ARRAYFORMULA(IF(LEN(D2:D), IF(E2:E="", "Open", E2:E), ""))
   

   Here's what it does:
   • If column D has content (which it will for form-submitted rows), it checks if column E is empty. If yes, it fills "Open" as the default. If E already has a value (like someone edited it to "Closed"), it keeps that value.
   • If column D is empty, it clears column E.

Why Your Original Formula Didn't Work #

Your original formula =ARRAYFORMULA(IF(LEN(D2:D300),E2:E300,Sheet3!B3:B4)) was trying to populate text from your options range, but it wasn't creating a selectable dropdown. Data validation is the separate setting that adds the dropdown functionality, and combining it with an array formula lets you handle defaults while supporting form-generated rows.

内容的提问来源于stack exchange，提问作者LavaSlam1989