从 Excel 模板创建的新工作表上的同一位置提取值。
我目前正在创建一组用于质量保证 Excel 表的模板。目的是将每张纸的分数显示在 excel 2007 书的顶纸上。目前，我可以使用查找来提取数据，但我仅将其提取到同一单元格，并且仅适用于当前工作表。不适用于任何新添加的内容。
是否可以有一个公式来查找从模板创建的任何新工作表上的值并将其放置在我可以绘制图表的表格中？
I am currently creating a set of templates to be used in a quality assurance excel sheet. The aim is to have the scores from each sheet displayed on the top sheet of the excel 2007 book. Currently I can use a look up to pull the data, but I only pulls it to the same cell, and only for current sheets. Not for any new ones added.
Is it possible to have a formula that will look up a value on any new sheet created from a template and place it in a table that I can graph?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用 VBA 来完成此操作，但我认为这种 3 步 XLM（Excel4.0 宏）方法更清晰。该方法是设置一个包含工作簿中所有工作表名称的范围名称，然后根据数字列表提取它们。
（出自 david Hager http://spreadsheetpage.com/index.php/site/eee/issue_no_3_april_15_1999/)
使用 =IF(A2<=COUNTA(wsNames)+0*RAND() 等公式,INDEX(wsNames,A2),"") 根据编号列表提取摘要表上的所有名称。此公式只是检查 x 个工作表（例如 6 个）是否在实时文件中，如果是，则将工作表 x 名称（第 6 个工作表）与数字 x (6) 相对应。
使用 INDIRECT 与此工作表名称来检索值，即从每个工作表中获取 A1，=IF(B3<>"",INDIRECT("'" &B2&"'!A1"),"" )
应添加工作表 - 或删除 - 所有名称都会自动按顺序更新
You can do it with VBA, but I think this 3 step XLM (Excel4.0 Macro) approach is cleaner.The approach is to set a range name that contains all sheet names in the workbook, then extract them against a list of numbers.
(credit david Hager http://spreadsheetpage.com/index.php/site/eee/issue_no_3_april_15_1999/)
use a formula such as =IF(A2<=COUNTA(wsNames)+0*RAND(),INDEX(wsNames,A2),"") to extract all the names on your summary sheet against a numbered list. This formula simply checks whether x sheets (say 6) are in the live file, if so its put the sheet x name (the 6th sheet) against the number x (6).
Use INDIRECT with this sheet name to retrieve the values, ie to get A1 from each sheet, =IF(B3<>"",INDIRECT("'" &B2&"'!A1"),"")
Should add sheets be added - or deleted - all the names update in sequence automatically