如何在不使用VBA或Excel 4.0宏的情况下创建动态工作表名称下拉菜单？

由于原生Excel函数没有直接枚举所有工作表的功能，同时要规避信任中心对Excel 4.0宏的限制，以下是适配共享工作簿的可行方案：

基础方案（半自动，零宏依赖） #

1. 创建辅助工作表 #

• 新建一个名为SheetList的工作表，专门用来维护需要纳入下拉菜单的工作表名称。
• 在A1单元格输入表头工作表名称，从A2开始手动填入当前所有目标工作表的名称（比如复制模板生成的「模板 (1)」「模板 (2)」等）。

2. 定义动态扩展名称 #

• 点击「公式」选项卡 → 「定义名称」：
  • 名称：DynamicSheetNames
  • 引用位置：=SheetList!$A$2:INDEX(SheetList!$A:$A,COUNTA(SheetList!$A:$A))
    这个名称会自动覆盖A列所有非空单元格，后续新增名称时无需修改定义。

3. 设置数据验证下拉菜单 #

• 选中需要添加下拉菜单的单元格（比如主表的B2）。
• 点击「数据」选项卡 → 「数据验证」：
  • 允许：选择「序列」
  • 来源：输入=DynamicSheetNames
  • 勾选「提供下拉箭头」后确定。

4. 更新操作 #

• 新增工作表：复制模板生成新表后，在SheetList的A列末尾粘贴新工作表的名称（复制后的表名会自动带序号，直接复制即可）。
• 修改表名：同步修改SheetList中对应的单元格内容，下拉菜单会自动同步更新。

进阶优化（近乎自动，减少手动输入） #

如果想进一步降低维护成本，可利用工作表链接自动同步名称变化：

• 在SheetList的B2单元格输入公式：

  =TEXTBEFORE(TEXTAFTER(CELL("filename",模板!A1),"["),"]")
  

  这个公式会自动提取「模板」工作表的名称。
• 复制B2的公式到B列其他行，将公式中的模板!A1替换为对应新工作表的A1单元格（比如B3改为模板 (1)!A1）。
• 设置A列单元格公式为=B2（向下填充），这样修改工作表名称时，B列公式会自动更新，A列同步变化，下拉菜单也会自动刷新。
• 新增工作表时，只需在B列新增一行，修改引用为新表的A1单元格即可。

这个方案完全避开了VBA和Excel 4.0宏，不会触发信任中心的限制，适配共享工作簿场景。

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