如何在excel中使用INDIRECT（）[或其他函数]来组合名称表引用和单元格引用？

问题背景：

我有一个excel电子表格，其中包含一组命名表格：

[1] "S10000_R3.3.2_201703301839"  "S10000_T4.3.0_201703301843" 
 [3] "S20000_R3.3.2_201703301826"  "S20000_T4.3.0_201703301832" 
 [5] "S30000_R3.3.2_201703301803"  "S30000_T4.3.0_201703301817" 
 [7] "S40000_R3.3.2_201703301738"  "S40000_T4.3.0_201703301752" 
 [9] "S50000_R3.3.2_201703301707"  "S50000_T4.3.0_201703301724" 
[11] "S60000_R3.3.2_201703301624"  "S60000_T4.3.0_201703301647" 
[13] "S70000_R3.3.2_201703301535"  "S70000_T4.3.0_201703301602" 
[15] "S80000_R3.3.2_201703301430"  "S80000_T4.3.0_201703301508" 
[17] "S90000_R3.3.2_201703301324"  "S90000_T4.3.0_201703301400" 
[19] "S260000_R3.3.2_201704021725" "S260000_T4.3.0_201704021929"
[21] "S270000_R3.3.2_201704021241" "S270000_T4.3.0_201704021529"
[23] "S280000_R3.3.2_201704020847" "S280000_T4.3.0_201704021100"
[25] "S290000_R3.3.2_201704020447" "S290000_T4.3.0_201704020702"
[27] "S310000_R3.3.2_201704012331" "S310000_T4.3.0_201704020242"
[29] "S320000_R3.3.2_201704011827" "S320000_T4.3.0_201704012128"
[31] "S330000_R3.3.2_201704011304" "S330000_T4.3.0_201704011546"
[33] "S340000_R3.3.2_201704010652" "S340000_T4.3.0_201704011010"
[35] "S350000_R3.3.2_201704010020" "S350000_T4.3.0_201704010404"
[37] "S360000_R3.3.2_201703311819" "S360000_T4.3.0_201703312134"
[39] "S370000_R3.3.2_201703310914" "S370000_T4.3.0_201703311301"
[41] "S380000_R3.3.2_201703310134" "S380000_T4.3.0_201703310509"
[43] "S390000_R3.3.2_201703301846" "S390000_T4.3.0_201703302252"

每张表都包含一组计时数据......

function.   user    system  elapsed
DT read input   2.144   0.63    12.274
DT Convert Date to Date Format  0.263   0.065   1.104
DT select via scan  0.024   0.001   0.047

我还有一个名为hidden的工作表，其中包含工作表名称列表 .

目标

我希望能够使用单元格引用从每个工作表填充摘要表 . I would love to be able to enter one formula on the summary spreadsheet and drag right so that the sheet references increment...

示例：

我希望能够将工作表名称与返回引用工作表上的值的单元格引用相结合 .

例如：

=S10000_R3.3.2_201703301839!A1 将根据上面的示例数据返回'function' . 即工作表 S10000_R3.3.2_201703301839 上单元格A1引用的返回值

现在我可以使用隐藏的工作表来返回工作表名称...

=Hidden!A1 将返回 S10000_R3.3.2_201703301839

问题：

I cannot seem to combine the sheet reference with a cell reference where the cell reference will increment when I pull down or right. 我似乎在创建单个字符串，而不是工作表名称和单元格引用的组合 .

=INDIRECT("'[Analysis.xlsx]hidden'!A1") 将返回 S10000_R3.3.2_201703301839

我想现在将该值返回与单元格引用相结合......

S10000_R3.3.2_201703301839 ！A1

问题：

我如何制定配方？

我应该使用嵌套的 INDIRECT （隐藏'！A1 INDIRECT 或其他什么？

硬部分似乎是如何允许单元格引用递增，因为它是字符串连接和单元格引用的组合 .