alterar a fórmula da célula de link entre as folhas

Question

Eu tenho uma pasta de trabalho chamada "stu", que tem de 1 a 30 folhas. Na célula H4 , vinculei uma fórmula:

=[sub.xlsx]Sheet1!$B$2

em sheet1 : célula H4 a fórmula na célula H4 deve ser =[sub.xlsx]Sheet1!$B$2
em sheet2 : célula H4 a fórmula na célula H4 deve ser =[sub.xlsx]Sheet1!$B$3
em sheet3 : célula H4 a fórmula na célula H4 deve ser =[sub.xlsx]Sheet1!$B$4
em sheet4 : célula H4 a fórmula na célula H4 deve ser =[sub.xlsx]Sheet1!$B$5
em sheet5 : célula H4 a fórmula na célula H4 deve ser =[sub.xlsx]Sheet1!$B$6
..e assim por diante ..

..................................

Portanto, em cada folha, a célula não deve mudar automaticamente. Eu não vou precisar pressionar = entrar e entrar de novo e de novo.

    

Answer 1

No Excel, digite Alt + F11 para abrir a janela do Visual Basic. À esquerda, clique com o botão direito do mouse no nome da sua planilha e Insert → Module . Uma sub-janela Module1 - Code será aberta à direita. Insira o seguinte nessa janela:

Function SHEET_NUM()
    SHEET_NUM = Application.Caller.Parent.Index
End Function

Agora volte para a planilha. Apenas por diversão, para ver como isso funciona, digite =SHEET_NUM() em qualquer célula. Ele deve exibir o número da planilha em que está. Então, entre

=INDIRECT("[sub.xlsx]Sheet1!$B$" & (SHEET_NUM()+1))

na célula H4 em cada folha. A subexpressão SHEET_NUM()+1 será avaliada como 2 em Sheet1 , 3 em Sheet2 , etc… - em outras palavras, o número da linha que você deseja em sua referência. & é o operador de concatenação; ele combinará a string constante "[sub.xlsx]Sheet1!$B$" com o número da linha computada, resultando na referência que você deseja. A função INDIRECT usa uma string parecida com uma referência e desmarca-a; isto é, ele busca o valor que é referenciado.

Alguns avisos:

• Se você estiver lidando com uma pasta de trabalho ( stu.xlsx ) fazendo referência a outra ( sub.xlsx ), você precisará ter o segundo livro aberto sempre que trabalhar com o primeiro livro –– embora o Excel possa fazer referência a dados em um livro fechado, a função INDIRECT não pode. Se isso for um problema, você pode fazer uma manobra Copiar e Colar Valores.
• Você precisará salvar sua pasta de trabalho como um arquivo .xlsm e ativar as macros toda vez que abri-la. (Ou faça a manobra Copiar e Colar Valores acima).
• AFAICT, as planilhas recebem um número quando são criadas e é difícil mudar. Em particular, se você mover suas planilhas, elas manterão seus números originais , o que será confuso. (Mas, por outro lado, você pode renomeá-los sem se preocupar em mudar seus números.)

Answer 2

As etapas a seguir devem obter precisamente a fórmula que você está procurando, sem o VBA:

1. Selecione todas as planilhas que você deseja que a fórmula apareça (você pode adicionar cada planilha à seleção segurando "Ctrl" e clicando em sua guia)
2. Insira a seguinte fórmula em outra célula em branco, talvez H3: = SE (ESQUERDA (DIREITA (CELULAR ("nome do arquivo", $ A $ 1), 2), 1)="t", DIREITA (CELULAR ("nome do arquivo", $ A $ 1), 1), DIREITA (CELULAR ", $ A $ 1), 2))
3. Insira a seguinte fórmula na célula H4 (substituindo H3 pela célula usada na etapa 2): = "= [sub.xlsx] Folha1! $ B $" & H3 + 1
4. Selecione a célula H4, copie e cole os valores
5. Limpe a fórmula criada na etapa 2
6. Localize e substitua "=" por "=" em toda a pasta de trabalho (Ctrl + H para localizar e substituir)
7. Cancele a seleção das outras folhas (você pode remover cada folha da seleção segurando "Ctrl" e clicando na guia)

Não faça mais nada entre as etapas 1 e 7, a menos que saiba como trabalhar com várias planilhas selecionadas.

    

Answer 3

Sua melhor aposta pode ser abrir sub.xlsx, agrupar todas as 30 folhas no stu, inserir uma versão da fórmula (por exemplo, = [sub.xlsx] Folha1! $ B $ 2 em H4 de folha1 de stu), desagrupar e em seguida, ajuste manualmente H4 em cada folha de stu (exceto aquela em que a fórmula foi inserida, por exemplo, na folha2, substitua 2 por 3, etc.).