Let's break down your questions with practical, actionable formulas tailored to Google Sheets (since it's the most common data source for Google Forms dropdowns):

1. Query to Get Last Names Starting with A-F #

To filter your list and only return entries where the last name starts with A-F (case-insensitive), use this QUERY function in Google Sheets:

=QUERY(YourDataRange, "WHERE UPPER(LEFT(YourLastNameColumn, 1)) BETWEEN 'A' AND 'F' ORDER BY YourLastNameColumn", 1)

• Replace YourDataRange with the full range of your data (e.g., A:B if you have names and associated IDs)
• Replace YourLastNameColumn with the column letter containing last names (e.g., A)
• The 1 at the end tells the function your range has a header row; remove it if there's no header.

This ensures you capture all entries regardless of whether the first letter is uppercase or lowercase, and sorts them alphabetically for cleaner dropdown use.

2. Split Results into 2-3 Columns (Max 850 Rows Each) #

Since Google Forms has a strict dropdown option limit, splitting your filtered list into chunks of 850 rows per column is straightforward with QUERY's LIMIT and OFFSET parameters:

Column 1 (First 850 Entries) #

=QUERY(YourDataRange, "WHERE UPPER(LEFT(YourLastNameColumn, 1)) BETWEEN 'A' AND 'F' ORDER BY YourLastNameColumn LIMIT 850", 1)

Column 2 (Next 850 Entries) #

=QUERY(YourDataRange, "WHERE UPPER(LEFT(YourLastNameColumn, 1)) BETWEEN 'A' AND 'F' ORDER BY YourLastNameColumn LIMIT 850 OFFSET 850", 1)

Column 3 (If Needed, Next 850 Entries) #

=QUERY(YourDataRange, "WHERE UPPER(LEFT(YourLastNameColumn, 1)) BETWEEN 'A' AND 'F' ORDER BY YourLastNameColumn LIMIT 850 OFFSET 1700", 1)

Dynamic Auto-Filling Alternative #

If you prefer a more dynamic setup that auto-fills without manual adjustments, use INDEX + FILTER with IFERROR to hide empty errors:

// Column 1 (C1:C850)
=IFERROR(INDEX(FILTER(YourLastNameColumn, UPPER(LEFT(YourLastNameColumn,1)) BETWEEN "A" AND "F"), ROW(C1:C850)-ROW(C1)+1), "")

// Column 2 (E1:E850)
=IFERROR(INDEX(FILTER(YourLastNameColumn, UPPER(LEFT(YourLastNameColumn,1)) BETWEEN "A" AND "F"), ROW(E1:E850)-ROW(E1)+1 + 850), "")

Quick Tip #

Once you have these columns set up, you can link each column to a separate Google Form's dropdown field. This way, you'll have multiple forms split by letter ranges, which is a reliable workaround for the dropdown limit.

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