使下拉菜单选择遵循特定文字的公式? [英] Formula to make dropdown menu choice follow specific text?

问题描述

我制作了一个任务跟踪器，该任务跟踪器从主要的Google Spreadsheet组中提取了任务，并按日期将其按顺序排列在跟踪器电子表格中.我还提供了一个下拉列表来标记每个任务的状态.

I made a task tracker which takes my tasks from the main group Google Spreadsheet and arranges them in order in my tracker spreadsheet by date. I also included a dropdown list to mark the status of each task.

除了状态选项外，其他所有方法都可以正常工作-组电子表格中的数据经常更改(任务被删除和添加)，并且发生这种情况时，电子表格中的列表会自动更新并转移基于新任务和日期，但状态列保持不变.

It's all working the way I hoped it would except for the status option - the data from the group spreadsheet changes frequently (tasks are removed and added) and when this happens, the list in my spreadsheet gets automatically updated and get shifted around based on the new tasks and dates, but the status column remains static.

例如，说我完成了电子表格上的第一个任务，因此我在跟踪器电子表格的第一行上将该任务标记为完成".我们的小组管理员认为我的工作已经完成，并从小组电子表格中删除了该任务，因此我的跟踪器电子表格会自动更新，并且第一个任务也将从我的电子表格中删除.

For example, say I finish the first task on my spreadsheet so I mark the task on the first row of my tracker spreadsheet "done". Our group admin sees my work is finished and deletes said task from the group spreadsheet, so my tracker spreadsheet automatically gets updated and the first task is also removed from my spreadsheet.

但是，下一个任务碰到了我的电子表格的第一行，该行仍标记为完成".-我希望状态也将被删除，或者下拉菜单恢复为空白初始状态.

However, the next task gets bumped to the top row of my spreadsheet, which is still marked "done" - I was hoping the status would get deleted as well or the dropdown menu is reverted to its blank initial state.

是否有一种方法可以使下拉选项注册/记住行中(选择时)的数据或文本，并在任务和计划更新时进行跟踪?我当时认为触发器可能是在状态列中的一个单元格被填充时(通过从下拉菜单中选择一个选项)，它会记住左侧四个单元格中的数据，即使在任务四处转移.

Is there a way to make the dropdown option register/remember which data or text were in its row (when it was chosen) and follow it when the tasks and schedules get updated? I was thinking the trigger could be when a cell in the status column gets filled in (by way of choosing an option from the dropdown menu), it remembers the data from the four cells to its left, then tracks it even when the list of tasks get shifted around.

推荐答案

我尝试将您的工作表的更新汇总在一起，以完成您想要的工作.查看我的样本表.由于在不同的选项卡上需要进行一些小的更改，因此我不想影响您的示例.

I've tried to put together an update to your sheet that may do what you want. Look at my sample sheet. Since several small changes were needed on different tabs, I didn't want to affect your sample.

关键是每个任务都有唯一的标识符.有很多方法可以做到这一点.我将工作表名称(例如task1或task2)添加到Task#.这需要在您从中提取任务的工作表中完成，例如.任务1，任务2和任务3.

The key point is having a unique identifier for each task. Lots of ways of doing this. I added the sheet name, eg tasks1, or tasks2 to the Task#. This would need to be done in the sheets that you pull your tasks from, eg. tasks1, tasks2, and tasks3.

跟踪器"选项卡列出了从这些工作表中拉出的所有任务.我为任务ID添加了一个隐藏列(E)，但不必将其隐藏.如果从任务ID"值可以明显看出，可能不需要显示任务#".

The Tracker tab lists all of your tasks, pulled from those sheets. I've added a hidden column (E) for the Task ID, but it doesn't have to be hidden. And possibly you don't need to show the Task#, if it is obvious from the Task ID value.

要输入状态更新，您只需在.在这里，您将需要以某种方式维护任务ID列表.当您分配了新任务时，可以将新任务ID手动添加到此列表中.或者可以使用宏/脚本来添加新的任务ID.

To enter status updates, you must only do that on the UpdateStatus tab. Here you will need to maintain a list of task IDs in some way. New Task IDs could be added to this list manually, when you get assigned a new task. Or a macro/script could be used to add new Task IDs.

您甚至可以为公司预定义所有可能的任务ID，并且使用过滤器仅显示为其分配任务的ID.请参阅标签 UpdateStatus#2 一个例子.这将取决于您的TaskID值的良好命名约定.

You could even pre-define all possible task IDs for your company, and, using filtering, only show the ones for which you have an assigned task. See tab UpdateStatus#2 for an example of this. This would depend on a good naming convention for your TaskID values.

让我知道这对您是否有意义，或者您有任何疑问.唯一必要的公式是一些VLOOKUP，这些VLOOKUP可以仅基于该行的任务ID值从其他位置提取数据.公式单元格以黄色突出显示.

Let me know if this makes sense to you or not, or if you have any questions. The only necessary formulas are a few VLOOKUPs to pull data from other locations, based solely on the Task ID value for that row. Formula cells are hightlighted in yellow.

这篇关于使下拉菜单选择遵循特定文字的公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！