☰

Excel-Offset aus zwei verschiedenen Arbeitsmappen, Erstellen einer Kopie einer, ohne die andere zu beeinträchtigen

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
Excel-Offset aus zwei verschiedenen Arbeitsmappen, Erstellen einer Kopie einer, ohne die andere zu beeinträchtigen

Ich verwende zwei verschiedene Arbeitsmappen, die erste [Nur Excel-Daten] wurde als Datenbank erstellt und die zweite [Rechnungsvorlage] wurde zum Erstellen einer Rechnung erstellt.

Beim Erstellen einer Rechnung verwende ich Offset, um beispielsweise die Standortliste der Kunden zu lesen.

=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)

Und es funktioniert einwandfrei.

Das Problem ist, wenn ich eine Kopie der Arbeitsmappe [Rechnungsvorlage] erstellen möchte, die einen anderen Pfad hat, z. B. auf dem Desktop ändert sich der Offset zu

=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)

Daher hat sich der Pfad der Arbeitsmappe [Nur Excel-Daten] automatisch geändert und ist nicht mehr erreichbar, da ich nur den Pfad der [Rechnungsvorlage] geändert habe und nicht den des anderen.

Wie kann ich diese Formel verwalten, sodass sie den Pfad der [Nur Excel-Daten] nicht beeinflusst, wenn ich den Pfad der [Rechnungsvorlage] ändere?

Danke fürs Helfen!

Antwort1

Wenn Sie eine Excel-Datei mit externen Referenzen öffnen (und ihr erlauben, Updates durchzuführen, wenn Sie die Sicherheitswarnung erhalten), scheint Excel einige Automatismen auszuführen, um die Referenzen aufzulösen. Es scheint für Fälle optimiert zu sein, in denen Sie sowohl Quelle als auch Ziel verschieben, insbesondere für das Ändern von Laufwerksbuchstaben (vielleicht ein Relikt aus Diskettenzeiten?) .

Leider kann dies zu Störungen führen, wenn Sie die Zieldatei ( Invoice Template.xlsxin Ihrem Beispiel) alleine verschieben.

Der wohl schnellste Weg, das Problem zu beheben, ist über den edit linksDialog, den Sie im -Ribbon finden Data. Dort haben Sie eine Tabelle mit allen externen Dateien und können ändern, wohin diese verweisen ( change source). Wenn Sie den Pfad dorthin korrigieren Excel Data Only.xlsx, sollten alle Verweise auf diese Datei in Ihrer Tabelle auf einmal aktualisiert werden.

Antwort2

Wenn das Problem bei einer großen Menge von Dateien auftritt, die stapelweise aktualisiert werden müssen (oder Sie einfach abenteuerlustig sind, und vergessen Sie nicht, vorher ein Backup zu erstellen!), können Sie versuchen, die Rohdatei zu bearbeiten *.xlsx. Es handelt sich eigentlich um ein Zip-Archiv.

Wenn Sie es mit einem Archiv-Explorer öffnen (7-Reißverschluss(Beispiel:) finden Sie die externen Referenzen im Unterverzeichnis \xl\externalLinks\_rels\. Dort befindet sich wahrscheinlich eine Datei mit dem Namen externalLink1.xml.rels(Nummer kann variieren). Es handelt sich um eine XML-Datei, die einen (relativen) Dateipfad und -namen enthält. Sie können sie dort aktualisieren. Sie wird wie folgt eingegeben:.../externalLinkPathMarkieren Sie es als relativ zum Speicherort Ihrer Datei. Das Ändern dieses Typs http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissingkann das Problem ebenfalls lösen.

Ihre externalLink1.xml.rels könnten so aussehen. Ich habe diesen Pfad nicht speziell getestet :

 <?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)!!!!\0000%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

PS: Ich würde von der Verwendung von Sonderzeichen (Klammern, Ausrufezeichen usw.) in Excel-Dateien oder -Pfaden abraten, da dies ebenfalls Probleme verursachen könnte.

verwandte Informationen