Creating a dropdown menu in Google Sheets using Data Validation is easy: you simply select a range of cells (like A1:A10) and the dropdown populates with those ten items. However, what happens when you add an 11th item to cell A11? The dropdown menu will not see it, because it is hardcoded to stop at A10. You have to manually edit the Data Validation rules every single time your list grows.
You can fix this by setting the range to the entire column (A:A), but that introduces a new problem: your dropdown menu will now contain 990 blank, empty spaces at the bottom, making it incredibly annoying to scroll through.
To create a truly professional, “Dynamic” dropdown menu—one that perfectly expands when you add a new item, but never shows blank spaces—you must use the advanced OFFSET function combined with the COUNTA function.
Understanding the Logic
The OFFSET function allows you to tell Google Sheets to grab a range of cells, but instead of giving it a hardcoded endpoint, you use COUNTA to mathematically count exactly how many cells actually have text in them. If 12 cells have text, the OFFSET range dynamically shrinks to exactly 12 cells. If you add a word, it grows to 13.
Step 1: Set Up Your Source List
Assume you have a list of “Active Projects” on Sheet2 in Column A.
- A1: (Header) “Projects”
- A2: Alpha
- A3: Beta
- A4: Gamma
Step 2: Create a Named Range (The Trick)
You cannot paste an OFFSET formula directly into the Data Validation menu; Google Sheets will throw an error. You must wrap the formula in a Named Range first.
- Look at the top menu bar and click Data > Named ranges.
- A sidebar will open on the right. Click + Add a range.
- In the name box, type
DynamicProjectList(no spaces allowed). - In the data range box, paste the following exact formula:
=OFFSET(Sheet2!$A$2, 0, 0, COUNTA(Sheet2!$A:$A)-1, 1) - Click Done.
How the Formula Works
OFFSET(Sheet2!$A$2: Start looking at cell A2 (skipping the header).0, 0: Do not move any rows or columns away from the starting point.COUNTA(Sheet2!$A:$A)-1: Count every single non-empty cell in the entire column A. Subtract 1 to account for the header. This number becomes the dynamic Height of the range.1: The width of the range is exactly 1 column.
Step 3: Build the Dropdown
- Go to your main dashboard (Sheet1) and click the cell where you want the actual dropdown menu to appear.
- Click Data > Data validation.
- Click + Add rule.
- Under the “Criteria” dropdown, select Dropdown (from a range).
- In the exact text box below it, type the name of your Named Range exactly as you created it, preceded by an equals sign:
=DynamicProjectList - Click Done.
The Result
Click your new dropdown menu. It will perfectly display “Alpha, Beta, Gamma” with zero blank spaces at the bottom. Now, go to Sheet2 and type “Delta” into cell A5. Go back to your dropdown menu and click it again. “Delta” is instantly there. Your dashboard is now completely automated and infinitely scalable.