Como criar uma lista personalizada com referência cruzada de folha?

Question

No final do mês recebo um arquivo excel com um número variável de folhas.

Cada folha tem um valor na mesma célula: A1

Eu quero criar uma nova planilha e exibir todos os valores de A1 em cada planilha e na soma.

Eu sei como obter o valor de uma célula de folha cruzada =Sheet1!A1 , mas se eu colocar uma nova folha =Sheet1!A1 e tentar extender como uma série, ela preencherá a fórmula com =Sheet1! B 1 em vez de =Sheet 2 !A1 , que é o resultado desejado.

Como posso criar uma lista personalizada com referência cruzada de planilha?

    

Answer 1

Sem saber como você gostaria de fazer isso, uma maneira simples seria usar uma indireta

Então, no seu resumo, se você começar na linha 1 -

=INDIRECT("Sheet"&ROW()&"!$A$1")

Isso mostrará A1 na Folha1

A linha 2 mostrará A1 na Folha2

etc.

Outra maneira de abordar isso seria com uma macro

Sub gather()
Dim wssrc As Worksheet
 'if this stays as activesheet, only run this when summary sheet is active
 'Otherwise, just change the activesheet to the summary sheet like -
 'Set wssrc = ThisWorkbook.Sheets("Sheet4") where "Sheet4" is whatever your summary sheet is
 Set wssrc = ActiveSheet
 Dim i As Integer
 i = 1

   For Each sh In ThisWorkbook.Sheets
      If Not sh.Name = wssrc.Name Then
       wssrc.Cells(i, 1) = sh.Cells(1, 1)
       i = i + 1
      End If
   Next
End Sub

Isso itera em todas as planilhas da pasta de trabalho, verifica se elas não são sua planilha de resumo e copia o valor A1 para a planilha de resumo na coluna A.