A referência da planilha de atualização do Excel (como acontece com a célula) quando eu coleto o conteúdo de uma planilha a outra?

Question

Eu tenho um documento onde eu crio uma nova planilha que representa dias (tem que ser assim). Cada folha tem uma referência à mesma célula na folha que representa o dia anterior. Assim, a planilha "Terça-feira" extrai alguns valores da planilha "Segunda-feira" e assim por diante. A fórmula é:

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33

e o resultado que vejo é algo como Saldo from 1 June 2016, Amount $55.00

Se eu criar uma nova planilha e nomear Day05 e colar o conteúdo da planilha Day04 , existe uma maneira de atualizar a fórmula que faz referência a Day03 a Day04 ? No momento, o conteúdo ainda extrai dados da planilha Day03 .

Em resumo, quando copio o conteúdo da planilha Day04 , quero ver a atualização da fórmula em:

            Description                              Amount

="Saldo from "&TEXT($'Day04'.C4,"DD.MM.YYYY.")        =$'Day04'.F33

Neste momento, a referência do nome da planilha não é atualizada e minha planilha Day05 ainda extrai dados de Day03

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33 //<-- WRONG is 03 ref!

    

Answer 1

O problema é criar uma nova planilha, copiar o conteúdo de uma planilha existente e alterar as referências a células para uma segunda planilha existente. Como diz Satya Mishra Satya Mishra , isso pode ser feito manualmente alterando 'Day03'! para 'Day04'! após a cópia para uma nova planilha, Day05 .

Para fazer a alteração automaticamente quando você copia da planilha anterior para uma nova, vamos reafirmar o problema: Crie uma nova planilha e copie o conteúdo da planilha anterior. Use o nome da nova planilha para fazer referências à planilha nomeada anterior.

Aqui está uma fórmula na planilha atual que obtém o conteúdo da célula F33 da planilha nomeada anterior. Por exemplo, se a planilha atual for Day05 , a fórmula obterá F33 da planilha Day04 .

=INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33"))

Como a fórmula funciona

A fórmula usa INDIRECT para obter um valor de célula da planilha anterior. Se a planilha atual for Day05 e desejarmos que a célula F33 , INDIRECT precise de uma sequência que faça referência à célula F33 na planilha Day04 . A string que precisamos é de 'Day04'!F33 .

1. Obter o nome da planilha para a planilha atual.

Day05         =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)

2. Modifique a fórmula para obter o número no final do nome da planilha, alterando de +1 para +4. Isso pula os 3 caracteres "Dia".

05            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)

3. Subtraia 1 para obter a parte do número da planilha anterior.

4            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1

4. Use TEXT para preencher o número com um zero inicial.

04           =TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#")

5. Use CONCATENATE para criar o novo nome da planilha com uma referência à célula F33 .

'Day04'!F33  =CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33")

Usando a fórmula

Sempre que você quiser um valor de célula da planilha anterior, use a fórmula no lugar da referência de célula e altere a última cadeia na fórmula para a referência de célula. Por exemplo, para usar a célula C4 para mostrar a descrição (observe C4 na fórmula):

="Saldo from "&TEXT(INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","C4")),"DD.MM.YYYY.")

O resultado é Saldo from 01.06.2016.

Condições e advertências

• Cada nome de planilha deve terminar com dois números, por exemplo, Day02 , Day03 , etc.
• A fórmula não funcionará até que a nova planilha tenha seu nome correto. Um nome de folha incorreto causa #VALUE! de erros.

Answer 2

Não encontrei nenhuma maneira de fazer isso automaticamente. Mas um "Pesquisar e substituir" (Ctrl + H) parece funcionar.

    

Answer 3

Eu encontrei a solução graças ao comentário de Scott Craner. Eu te aviso que vai ser uma fórmula longa.

Em todos os lugares em que você recebeu o 'DayXX' com o número XX, você pode alterar o x XX para

&MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("y";MID(CE("filename",A1),FIND("]";CEL("filename";A1))+1,255))+1,2)-1

Isto encontra o nome do arquivo e procura o y do dia e extrai o nummer do nome do arquivo e subtrai 1 dele. Eu posso explicar mais se você quiser, basta adicionar um comentário.

    

Answer 4

O Excel adapta as referências a uma planilha quando alteramos o nome dessa planilha. Todas as referências são renomeadas automaticamente. Vamos tentar resolver o problema com esse recurso: em vez de renomear as referências, renomearemos a planilha .

1. Abra sua pasta de trabalho com as folhas dos dias.
2. Crie a planilha com os dados do novo dia (digamos, "Day05") como você estava fazendo anteriormente. No final, copie as fórmulas da planilha Day04, independentemente de elas ainda se referirem ao Day03 após a cópia, e salve-as. Não feche a pasta de trabalho ainda!
3. Abra outra pasta de trabalho, que pode estar vazia, para manter uma referência temporária e mova a planilha Day05 (para isso, clique com o botão direito do mouse em Day05, escolha 'Mover ou Copiar ...' e selecione a outra pasta como destino).
4. Excluir a planilha "Day04" e alterar o nome da planilha "Day03" para "Day04" . Essa é a parte complicada!

Agora você pode ver que a planilha "Day05", na nova pasta de trabalho, terá suas referências definidas como Day04 (da primeira pasta de trabalho), em vez de Day03, devido à renomeação. Em seguida, vamos mover essa folha de volta para a pasta de trabalho original.

5. Feche a pasta de trabalho original, descartando as alterações feitas após o último salvamento.
6. Reabra a pasta de trabalho novamente e mova de volta a planilha Day05 que está na pasta de trabalho temporária. As referências ainda estão no Day04 (da pasta de trabalho atual), porque descartamos as alterações em vez de renomear a folha de volta.

Voilà! Este é um truque para fazer o Excel renomear as referências para nós.

Pode não parecer fácil, à primeira vista, mas eu acho que é mais fácil de usar do que usando fórmulas ou VBA, porque você pode controlar as etapas sem conhecimento de programação. Além disso, é mais fácil do que renomear cada referência.

Espero que ajude!

EDIT 1

Claro, você pode gravar uma macro com essa atividade e até mesmo adaptá-la para poder chamar mais rapidamente.