Excel deslocado de duas pastas de trabalho diferentes, crie uma cópia de uma sem afetar a outra

Question

Estou usando duas pastas de trabalho diferentes, a primeira [somente dados do Excel], criada como uma base de dados, e a segunda, [modelo de fatura], criada para criar uma fatura.

Ao criar uma fatura, estou usando o offset para ler, por exemplo, a lista de locais dos clientes.

=OFFSET('[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

E está funcionando corretamente.

O problema é quando eu quero criar uma cópia da pasta de trabalho [Invoice Template] que terá um caminho diferente, por exemplo, na área de trabalho, o deslocamento muda para

=OFFSET('C:\Users\anthony\Desktop\[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

Então, automaticamente, o caminho da pasta de trabalho [Excel Data Only] foi alterado e não está mais acessível, já que eu alterei o caminho do [Invoice Template] e não o outro.

Então, como posso gerenciar essa fórmula para que ela não afete o caminho do [Excel Data Only] ao alterar o caminho do [Invoice Template].

Obrigado por ajudar!

    

Answer 1

Quando você abre um arquivo excel com referências externas (e permite que ele execute Atualizações, quando você recebe o aviso de segurança), o Excel parece executar algum automatismo para resolver as referências. Parece ser otimizado para os casos em que você move fonte e destino, especialmente para alterar as letras de unidade ^{(talvez um relict de tempos de disquete?)} .

Infelizmente isso pode frear coisas, se você mover o arquivo de destino ( Invoice Template.xlsx em seu exemplo) sozinho.

Provavelmente, a maneira mais rápida de consertar as coisas é através do diálogo edit links que você pode encontrar no Data -Ribbon. Lá você tem uma tabela de todos os arquivos externos, e você pode mudar para onde eles estão apontando ( change source ). Quando você corrigir o caminho para Excel Data Only.xlsx , deverá atualizar todas as referências a esse arquivo em sua planilha de uma só vez.

    

Answer 2

Se você está encontrando o problema em uma enorme quantidade de arquivos que precisam ser atualizados em lote (ou você está se sentindo aventureiro, oh, e não se esqueça de fazer backup primeiro!), você pode tentar editar o% bruto*.xlsx file. Na verdade, é um arquivo zip.

Se você abri-lo com um explorador de arquivos ( 7-zip por exemplo), você encontrará as referências externas no arquivo subdiretório \xl\externalLinks\_rels\ . Provavelmente existe um arquivo chamado externalLink1.xml.rels (o número pode variar). É um arquivo XML que contém um caminho e nome de arquivo (relativo). Você pode atualizá-lo lá. Ela será digitada como .../externalLinkPath marcando-o como relativo à localização do seu arquivo. Alterar esse tipo para http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing também pode resolver o problema.

Seus externalLink1.xml.rels podem parecer com isso _{^{Eu não testei esse caminho especificamente}} :

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!! <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!!%pre%00%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

00%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx " TargetMode="External" /> </Relationships>

PS: Eu desencorajaria o uso de Caracteres Especiais (Colchetes, Marcas de Exclamação, etc.) em Arquivos ou Caminhos do Excel, que podem causar problemas também.