Суммирование одной ячейки на нескольких листах

Question

Я могу легко суммировать данную ячейку, скажем, B9, по нескольким листам с чем-то вроде:

=SUM(Sheet7:Sheet11!B9)

Это эквивалентно:

=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9

Который является суммой ячейки B9 во всех листах от Листа 7 до Листа 11.

Мне нужно создать что-то вроде первой формулы, но с именами листов в ячейках. Так что, если A1 содержит:

Sheet7

и ячейка A2 содержит:

Sheet11

Тогда формула в A3 будет использовать эти ячейки. Что я пробовал до сих пор:

=SUM(A1 & ":" & A2 & "!" & B9)

а также

=SUM(INDIRECT(A1 & ":" & A2 & "!" & B9))

Ни одна из этих работ.

Для этого я легко могу создать функцию VBA, но рабочая книга должна работать в среде без макросов.

Answer 1

Это ручной подход, который не может быть идеальным

=INDIRECT(A1&"!A1")+INDIRECT(A2&"!A1")

или же

=SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"))

Мой лист выглядел как

     A         B         C
1    sheet2
2    sheet3
3

И в Sheet2 A1 имел значение, как это делает Sheet3 A1

В вашем случае вам понадобится

   =SUM(INDIRECT(A1&"!A1"),INDIRECT(A2&"!A1"),INDIRECT(A3&"!A1"),INDIRECT(A4&"!A1")...etc)

Answer 2

На основании ответа @ Dave и вашего комментария But I don't know in advance what the contents of cells A1 and A2 will be.......so I don't know how many terms to include. Я бы предложил добавить столбец, включающий в себя сумму каждого листа и сумму этого столбца. Допустим, вы вводите имена листов в столбце A , а затем вводите в столбце B:

=IF(ISNUMBER(INDIRECT(A2&"!B9")),INDIRECT(A2&"!B9"),0) 

Чем вы можете сделать =Sum(B:B) чтобы получить сумму.

Обратите внимание, что я добавил функции IF и ISNUMBER чтобы избежать #REF! когда в столбце А отсутствует имя листа. Вы можете использовать вместо ISBLANK , как

=IF(ISBLANK(A2),0,INDIRECT(A2&"!B9"))

Если вы хотите получить сообщение об ошибке, когда столбец А имеет неправильное имя листа.

Еще один совет: если вы называете свои листы как Лист1, Лист2, вы можете легко перетащить, чтобы заполнить листы в столбце А