Adicionando valores de tabela entre uma lista indefinida de planilhas que mantêm associações de chaves de dados

Question

Vamos imaginar que você tenha uma planilha de totais e cinco outras planilhas que consistam em dados para a planilha de totais. Vamos também dizer que toda planilha tem a mesma estrutura de tabela definida nela. Por exemplo

ID | # played

1  | 4

2  | 1

3  | 11

...

Como faço uma fórmula para a planilha de totais para que eu saiba a soma # played para o ID 1 entre todas as outras planilhas sem precisar alterar a fórmula toda vez que uma nova planilha for adicionada?

    

Answer 1

A solução a seguir não requer que você altere a fórmula existente, mas exige alguma duplicação no sentido de que você terá que inserir o nome da pasta que deseja referenciar em uma célula e quando alterar o nome de uma planilha você também terá que alterar o valor correspondente na célula acima mencionada.

Atualmente, não sei como fazer referência a nomes de planilhas de forma mais dinâmica com apenas as fórmulas básicas do Excel à sua disposição.

Tente isto:

Prepare suas planilhas com informações, por exemplo nomeie a primeira folha "01" e insira seus dados nas colunas A e B, por exemplo:

**Sheet 01**

A1 = 1   B1 = 4
A2 = 2   B2 = 1
A3 = 3   B3 = 11

Continue com esse formato nas outras planilhas, por exemplo:

**Sheet 02**

A1 = 1   B1 = 5
A2 = 2   B2 = 6
A3 = 3   B3 = 7

e

**Sheet 03**

A1 = 1   B1 = 8
A2 = 2   B2 = 9
A3 = 3   B3 = 10

Em seguida, crie a planilha na qual você deseja exibir todas essas informações. Para o argumento, vamos chamar esta folha "Resumo".

Em seguida, na folha Resumo, digite o seguinte:

**Summary**

A1 =     B1 = 01                                            C1 = 02   D1 = 03
A2 = 1   B2 = =VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,FALSE)    C1 =      D1 =   
A3 = 2   B3 =                                               C1 =      D1 =   
A4 = 3   B4 =                                               C1 =      D1 =   

Em seguida, basta copiar as fórmulas em B2 para o restante das células em que você deseja exibir seus valores.

Note que neste exemplo você terá que formatar as células em B1: D1 como texto, senão o Excel removerá automaticamente os 0s iniciais e a fórmula será quebrada. Você provavelmente estará usando nomes de texto para suas planilhas de qualquer maneira.

As desvantagens desta solução são:

1. Como já foi mencionado, você precisa inserir os nomes das planilhas manualmente na planilha Resumo e, assim que o nome de uma planilha for alterado, será necessário modificá-lo na planilha Resumo.
2. Essa solução não é dinâmica, levando em conta o ponto (1.) e o fato de você precisar copiar a fórmula para novas células sempre que desejar expandir o intervalo dos valores resumidos.

Alternativamente, você também pode considerar substituir a fórmula em B2 na planilha Resumo acima por

=IFERROR(VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,FALSE),"")

Você pode simplesmente preencher o máximo da planilha Resumo que desejar com essa fórmula, o que economizará trabalho adicional na próxima vez que adicionar mais planilhas à sua pasta de trabalho.

Se os nomes das planilhas seguirem um padrão ou se você souber os nomes das planilhas futuras de antemão, você pode até preencher a linha 1 com esses nomes e a última fórmula ainda funcionará porque não exibirá nada no caso da planilha não existe e exibirá o valor relevante quando a planilha com o nome da planilha pré-conhecida tiver sido adicionada à sua pasta de trabalho.