Usando o Excel Indirect com nomes de planilha

Question

Eu tenho uma pasta de trabalho com várias planilhas e desenvolvi em algumas das planilhas uma consulta / referência a uma linha em uma das planilhas.

Percebi que quando adiciono uma linha à planilha referenciada, a consulta / referência não percebe que a nova linha foi adicionada. Eu não entendo por que, mas seja como for, eu encontrei algumas referências a uma função chamada INDIRECT que parece resolver esse problema. Isso?

Eu tentei descobrir como referenciar uma planilha separada na fórmula, mas não consigo fazer com que ela funcione. A referência atual é: ='B&A'!P41 em que B&A é o nome da planilha e P41 é a célula referenciada.

Então, aqui estão duas perguntas:

1. O INDIRECT resolverá meu problema quando eu adicionar uma nova linha a B&A ?
2. Em caso afirmativo, qual é a sintaxe correta para a função INDIRETA?

Answer 1

Sim, INDIRECT irá aplicá-lo sempre olhando para a mesma célula, independentemente de outras células serem adicionadas / removidas.

INDIRECT usa uma string, que será usada como um endereço de célula. Assim, sua fórmula atual de:

='B&A'!P41

se tornaria

=INDIRECT("'B&A'!P41")

Usando isso, ele deve referenciar a célula estaticamente e só atualizar se a célula referenciar mudanças.

    

Answer 2

Se você precisar adicionar NOVAS linhas / células / colunas na planilha referenciada, você deve criar um meio para que as fórmulas de referência reconheçam isto.

Eu criaria uma célula na planilha de referência para conter uma referência de tabela / matriz como texto - por exemplo, $A1:$B200 em uma célula nomeada, por exemplo table_ref -
que então pode ser retirado e desreferenciado (de outras planilhas) com um INDIRECT extra () como em INDIRECT( INDIRECT(table_ref) )

Ao criar o conteúdo de table_ref , talvez seja necessário usar CELLS() , COUNTIF() , SUBTOTAL() , OFFSET() , ROWS() ou funções semelhantes e concatenar os resultados de vários deles - acredito existem algumas funções mais semelhantes que não me lembro agora.