英文:
How to Reverse Dropdown Box - Google Sheets
问题
Sure, here are the translated parts of your text:
我有一个网站上的表单，用户填写他们的姓名和所有信息。当他们提交表单时，数据会发送到Google电子表格。
然后，我有另一个工作表，其中有一个下拉框，可以自动从第一个工作表中填充姓名（和ID＃）。如果您点击下拉框，它会列出所有姓名。当您选择一个姓名时，表格的其余部分会填充他们的信息。
这最初效果很好，但随着我收到数百（也许是数千）个提交，我的下拉框中有很多姓名，每次我要选择一个姓名时，我都必须滚动到下拉列表底部以获取最近的提交。通常，我只需要访问最近的提交。
我看到可能有三种处理方法，但我不知道如何做到这些：
-
我可以颠倒下拉框数据，以便首先从原始数据表中提取最近的姓名。或者
-
另一种方法是使某人提交新提交时，将新行添加到数据表的顶部，而不是底部。或者
-
每个姓名都有一个ID＃（在不同的列中）。我可以按字母顺序对下拉框进行排序吗？
英文:
I have a form on my website that users fill out their name, and all their information. When they submit the form, the data goes to a google spreadsheet.
I then have another sheet that has a dropdown box that auto-populates the names (and ID #) from the first sheet. If you click the dropdown box, it lists all the names. When you select a name, the rest of the sheet populates with their info.
This initially worked great, however as I am getting hundreds (maybe thousands) of submissions, my dropdown is getting lots of names and every time I go to select a name, I have to scroll to the bottom of the dropdown to get the recent submissions. I normally only need to access the most recent submissions.
I see potentially three ways of handling this but I do not know how to do any of these:
-
Can I reverse the dropdown data so that it pulls the most recent names from the original data sheet first. OR
-
Another way of doing this is to make so that when someone submits a new submissions, that it adds the new line to the top of the data sheet, not at the bottom. OR
-
I have an ID # with each name (in a different Column). Can I sort the dropdown box by alphabetical order?
答案1
得分: 3
你最好选择选项1或选项3
为此，您应该创建一个辅助列，使用
选项1：反转下拉数据，以便它首先从原始数据表中提取最近的名称。
=SORT(G3:G18,ROW(G3:G18)*N(G3:G18<>""),0)
选项3：按字母顺序排序下拉框
=SORT(G3:G18)
（根据您的范围和区域设置调整公式）
然后，您可以将辅助列用作下拉范围。
英文:
Your best bet would be to use either option 1 or 3
For this you should create a helper column using
Option 1: Reverse the dropdown data so that it pulls the most recent names from the original data sheet first.
=SORT(G3:G18,ROW(G3:G18)*N(G3:G18<>""),0)
Option 3: Sort the dropdown box by alphabetical order
`=SORT(G3:G18)`
(Do adjust the formula according to your ranges and locale)
You can then use the helper column as your drop-down range
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论