Den Pfad einer Datei darzustellen, kennen die meisten womöglich mittels der Kopf- und Fusszeilen-Funktion. Ich zeige Dir in diesem Beitrag auf, wie Du dies auch mittels einer Formel machen kannst, damit Du den Pfad der aktuellen Datei in einer beliebigen Zelle in Excel ausgeben kannst.
Kopf- und Fusszeile
Bekannter ist die Vorgehensweise, den aktuellen Dateipfad in die Kopf- oder Fusszeile zu integrieren. Dazu gibt es eine vorgefertigte Auswahlmöglichkeit in den Kopf- und Fusszeilentools, die folgendes Resultat liefert:
Dies bedeutet jedoch, dass Du den Pfad immer an einer bestimmten Stelle (eben zu oberst oder zu unterst im Dokument) angezeigt haben wirst. Im nächsten Abschnitt findest Du, mithilfe welcher Formel Du diese Information in jeder beliebigen Zelle hinterlegen kannst!
Die Formel =ZELLE
Wie die Überschrift bereits andeutet, bietet die Formel "=ZELLE" die entsprechende Möglichkeit. Wendet man die Formel an, kommt eine Auswahlmöglichkeit:
Wähle hier "dateiname" und schliesse die Formel ab. Vollständig in einer beliebigen Zelle steht also:
=ZELLE("dateiname")
Und das Resultat ist der Pfad (inklusive Dateiname/Tabellenblatt). Beachte: Es wird nur etwas angezeigt, wenn Deine Excel-Datei auch einen Speicherort hat. Öffnest Du einfach die Applikation und versuchst dies aus (Mappe1), wird es nicht funktionieren. Speichere die Datei ab, aktualisiere die Formel und Du erhältst den Output (bspw.: "C:\Users\Roman\Desktop\[MeineDatei.xlsx]Tabelle1". Möchtest Du nun NUR den Dateipfad angezeigt bekommen, braucht es einige Ergänzungen. Die fertige Formel lautet:
=LINKS(ZELLE("dateiname");SUCHEN("[";ZELLE("dateiname"))-2)
Dies ergibt: "C:\Users\Roman\Desktop". Wir suchen also einerseits nach dem Zeichen "[" - dieses steht für den Dateinamen. Vom gesamten Konstrukt nehmen wir alle Zeichen von links bis zu dieser eckigen Klammer (abzüglich 2: Eben die Klammer und den letzten Backslash "\").
P.S.: Hängst Du hinter "dateiname" noch einen Bezug in eine andere Datei/Zelle, dann wird Dir davon der Pfad/Dateiname angezeigt!
Hallo,
ich suche eine Lösung für:
- im Ordner liegen die Dateien 2021-1-Name-Vorname.xlsx bis 2021-12-Name-Vorname.xlsx und eine Datei für die Auswertungen.
- je nach Kollege ist der Dateipfad unterschiedlich und Name und Vorname
- wie kann ich in der Auswertungsdatei einzelne Zellen aus den Dateien Jan - Dez. auslesen?
- die Tabellenblätter sind alle identisch beschriftet z.B. Monatsstatistik
- gedacht ist, dass eine Zip-Datei mit den Monats- und der Auswertungsdatei verschickt wird.
- der Nutzer trägt in B3 das Jahr; in B4 den Monat (aus dem evtl. die Dateinamen erzeugt werden); in B5 Name; B6 Vorname ein. Daraus soll die Formel erzeugt werden, in die dann die jeweilige Zelle der Monatsdatei ergänzt wird um die Werte auszulesen.
Bisher habe ich die Pfade über Hyperlink bzw. Verknüpfen zusammengebaut. Aber dann steht nur der Pfad bis zur Zellbezeichnung in der Zelle und nicht der Wert der Zelle.
Bei manuellem Anklicken der Zelle in beiden geöffneten Tabellen steht der selbe Pfad, aber in der Zelle wird der Wert ausgegeben. Das manuelle Anklicken funktioniert aber nicht für die Weitergabe an Kollegen.
Über eine Hilfe wäre ich sehr dankbar. Wir sind ein spendenfinanziertes Werk und können uns leider keine teuren Excel-Profis leisten.
Hallo Alber
Besten Dank für Deinen Kommentar. Um mit den ändernden Dateipfaden zwischen Euch Arbeitskollegen umzugehen, wäre ein separates Tabellenblatt "Config" sinnvoll. In einer Konfigurationsdatei (oder Tabelle) werden Parameter hinterlegt und sie lassen sich zentral pflegen. Ein Dateipfad (Ordner- / Projektverzeichnis, wie Du es beschreibst) ist besonders geeignet, in einer solchen Tabelle abzulegen.
Ich habe Dir hier eine Beispieldatei. Auf einem Tabellenblatt ist ein normaler Excel-Bericht enthalten, auf dem "Config"-Blatt werden unterschiedliche Varianten des Dateipfads ausgelesen. Du findest die Datei unter diesem Link:
https://excelblog.ch/wp-content/uploads/Config-Datei.xlsx
Sobald jemand die Datei öffnet, wird der Pfad basierend auf dem Speicherort neu hinterlegt.
Du kannst auf nun auf dieser Basis ein Link auf andere Dateien und Zellen herstellen. Ein Bezug auf eine andere Datei hat jeweils dieses Format:
='PFAD\[Dateiname.xlsx]Tabelle1'!$A$1
Konkret wäre Dein Bezug in etwa (mit der Kombination aus Jahr-Monat-Nachname-Vorname) wie folgt:
="'"&Config!B4"&"\["&B3&"-"&B4&"-"&B5&"-"&B6&".xlsx]Tabelle1'!$A$1"
Bitte achte auf die notwendigen Sonderzeichen [ ' und die Kombination aus Textbausteinen (in Gänsefüsschen) und effektive Zellenbezüge.
Liebe Grüsse
Roman
Hallo Roman,
derweil arbeite ich an einem ähnlichen Problem. Dein Tipp hat mir da sehr weitergeholfen, allerdings bekomme ich mit
="'"&Config!B4"&"\["&B3&"-"&B4&"-"&B5&"-"&B6&".xlsx]Tabelle1'!$A$1"
lediglich den kompletten Pfad angezeigt, der zum richtigen Wert führt, aber nicht den Wert selber. Wie komme ich dazu, dass mir der Wert, der in der Zelle steht und nicht der Pfad zur Zelle angezeigt wird? Wenn ich das "hart codiere" und =DAS_WAS_ICH_MIT_DER_FORMEL_OBEN_ALS_ERGEBNIS_HERAUSBEKOMME hinschreibe, bekomme ich den Wert der Zelle. Grundsätzlich scheint das also richtig zu sein.
Viele Grüße
Heidi
Hallo Heidi
Hat es mittlerweile geklappt mit Deiner Formel? Aus Deinem Kommentar konnte ich den Fehler nicht erkennen, es scheint für mich richtig zu sein.
Liebe Grüsse
Roman
Hallo,
ich möchte in meiner Urlaubsdatei Werte aus der Urlaubsdatei des jeweiligen Vorjahres übernehmen. Mein bisheriger Pfad lautet =SVERWEIS(B18;'U:\Dispo\Urlaubspläne\[Urlaubsplan 150 2023.xlsx]Jahresübersicht'!$B$11:$CA$200;7;0)
Jedoch müsste ich im kommenden Jahr alle Zellen mit dem Bezug auf den Urlaubsplan von 2024 ändern. Kennt jemand eine Lösung, wobei ich immer das Ergebnis aus dem Vorjahr erhalte ohne alle Formeln manuell ändern zu müssen?
Hallo André
Mit einer Kombination von Formeln und der Verwendung von "=INDIREKT()" solltest Du eine Lösung erarbeiten können. Das aktuelle Jahr kannst Du zum Beispiel via den Formeln "=JAHR(HEUTE())" ermitteln.
Die vollständige Formel mit dynamischer Jahreszahl-Ermittlung sollte somit lauten:
=SVERWEIS(B18;INDIREKT("'U:\Dispo\Urlaubspläne\[Urlaubsplan 150 " & JAHR(HEUTE()) & ".xlsx]" & "Jahresübersicht " & JAHR(HEUTE()) & "'") & "!$B$11:$CA$200";7;0)
Liebe Grüsse
Roman
Hallo Roman,
vielen Dank für deine Hilfe. Ich denke das die Formel perfekt ist, jedoch zeigt Excel mir nicht den Wert an, sondern #BEZUG!.
Ich habe keine Ahnung warum.
Was denkst Du, was mache ich falsch.
Liebe Grüsse
André
Hallo Roman,
Suchkriterium = gefunden, ok
Matrix = Veränderlich (vorher war konkrete Pfadangabe)
Spaltenindex= 7, ok
Bereich_Verweis ist 0 = Falsch (vorher auch so)
Formelergebnis = Veränderlich
Liebe Grüsse
André
Hallo André
Könnte es allenfalls mit dieser Formel funktionieren?
=SVERWEIS(B18;VERKETTEN("U:\Dispo\Urlaubspläne\[Urlaubsplan 150 ";JAHR(HEUTE());".xlsx]Jahresübersicht'!$B$11:$CA$200");7;0)
Liebe Grüsse
Roman
Hallo Roman,
statt Bezug zeigt er jetzt #WERT! an.
Das Zellenformat ist in beiden Tabellen ein Zahlenformat.
Das Formelergebnis ist weiterhin =Veränderlich
Schade,
Liebe Grüße
André