Excel Formel, für Wert aus anderer Arbeitsmappe, Dateiname bezogen aus versch. Zellen

deadletters · 28. November 2017

Moin,

ich verzweifle. Ich bin auf der Suche nach einer Formel, die mir einen Wert ausgibt =[2017_Q3_Barbetrag.xlsx]Übersicht!$F$54 .

allerdings sollen die Einzelteile von 2017_Q3_Barbetrag.xlsx aus aktuellen Arbeitsmappe aus den Zellen geholt werden. 2017 soll aus H4 kommen und 3 soll aus H3-1 kommen. In H3 und H4 werden jeweils Eingaben gemacht.

Arbeitsmappe 1 Dateiname: 2017_Q3_Barbetrag.xlsx, Arbeitsmappe 2 Dateiname: 2017_Q4_Barbetrag.xlsx

Die aktuelle Tabelle soll sich automatisch einen Wert aus der älteren Arbeitsmappe holen. INDIREKT ist glaub ich keine Option, da ja da alle Arbeitsmappen, auf die Bezug genommen wird geöffnet sein müssen.

Danke im Voraus für die Hilfe.

Greez
dead

DeusoftheWired · 28. November 2017

Öffne alle Dateien, aus denen Werte geholt werden sollen, außerdem die 2017_Q3_Barbetrag.xlsx

Klick in der 2017_Q3_Barbetrag.xlsx in die Zelle, in der Bezug auf eine andere Datei genommen werden soll. Füge ein = ein und klick dann in der fremden Datei die Zelle an, die den Bezug darstellt, Eingabe mit Enter abschließen.

Wenn das nicht hilft: https://support.office.com/de-de/ar...itsmappe-c98d1803-dd75-4668-ac6a-d7cca2a9b95f

deadletters · 28. November 2017

DeusoftheWired schrieb:
Öffne alle Dateien, aus denen Werte geholt werden sollen, außerdem die 2017_Q3_Barbetrag.xlsx

Klick in der 2017_Q3_Barbetrag.xlsx in die Zelle, in der Bezug auf eine andere Datei genommen werden soll. Füge ein = ein und klick dann in der fremden Datei die Zelle an, die den Bezug darstellt, Eingabe mit Enter abschließen.

Wenn das nicht hilft: https://support.office.com/de-de/ar...itsmappe-c98d1803-dd75-4668-ac6a-d7cca2a9b95f

das hilft mir nicht, denn das reicht nicht aus. die Formel dazu wäre kein Problem. das ganze muss durch die Bezüge aus den Zellen H3 und H4 den Dateinamen im Pfad in der Zelle automatisch ändern.

Der Pfad an sich ist fix, aber für den Dateiname, der Arbeitsmappe, aus der der Wert geholt werden soll, muss aus anderen Zellen der aktuellen Tabelle bezogen werden.

Also nochmal klar:

Arbeitsmappe 1: Dateiname: 2017_Q3_Barbetrag.xlsx
Arbeitsmappe 2: Dateiname: 2017_Q4_Barbetrag.xlsx

In Arbeitsmappe 2 soll in H51 der Wert = "H4"_Q"H3-1"_Barbetrag.xlsx erscheinen
Zellen immer in "" geschrieben

Es ist eine Abrechnungsdatei, welche Quartalsweise angelegt wird. Die neue Arbeitsmappe soll sich aus der vorhergehenden den letzten Rechnungsstand holen.
Um dies zu ermöglichen soll sich die aktuelle Tabelle die Daten aus den entsprechenden Zellen holen.

Perfekt wäre es, würde es so ablaufen:
2018_Q1_Barbetrag.xlsx holt sich den Wert aus
2017_Q4_Barbetrag.xlsx holt sich den Wert aus
2017_Q3_Barbetrag.xlsx holt sich den Wert ...

Verständlich?

halwe · 29. November 2017

Normalerweise kannst du die "INDIREKT(Bezug)" Funktion nutzen, um einen variablen Zellbezug aufzubauen.
Allerdings steht in der Hilfe:

Verweist Bezug auf eine andere Arbeitsmappe (ein externer Bezug) muss diese Arbeitsmappe geöffnet sein. Ist die Quellarbeitsmappe nicht geöffnet, gibt die INDIREKT-Funktion den Fehlerwert #BEZUG! zurück.

Du müsstest also die Vorquartalsdatei jeweils geöffnet haben.

Als einfachsten Workaround könnte man alle Quartale als extra Arbeitsblatt in einer gemeinsamen Arbeitsmappe unterbringen (also für ein neues Quartal jeweils nur ein neues Arbeitsblatt erstellen bzw. kopieren).

Oder du baust eine Hilfstabelle, die nichts anderes tut, als die betreffenden Werte über feste Bezüge aus allen relevanten (und ggf. noch nicht existierenden) Quartalstabellen abzurufen. Das ergibt teilweise Bezugsfehler, aber auf diese Werte kannst du dann per INDIREKT verweisen.

deadletters · 29. November 2017

Für mich wäre das alles kein Problem, aber für meine Kollegen, die einfach rein gar nichts mit Computern zutun haben und schon daran verzweifeln, dass sich der Computer aufgehängt hat, was er nicht hat, sondern nur die Batterie der Maus leer ist.

Richtig, das einfachste wäre die Quartale in einer Arbeitsmappe zu vereinen, da gibt es nur ein Aber:
Jedes Quartal enthält 10 zusätzliche Tabellenblätter, aus denen Informationen für das erste Tabellenblatt - die Übersicht geholt werden.

Ist es nicht möglich einen variablen Pfad zu haben, der sich an den Ordner anpasst, in dem die Dateien liegen, und der Dateiname, der Datei aus der der Wert kommt, den die Zelle zeigen soll, wird aus verschiedenen Zelleninhalten zusammen gesetzt?

halwe · 29. November 2017

Was ich mit der Hilfstabelle meine ist, dass du da tabellarisch die Werte aus allen (auch künftigen) Quartalsarbeitsmappen und jeweils aus den verschiedenen Tabellenblättern zusammensuchst (eine Zeile pro Quartal, eine Spalte je Einzelwert (aus verschiedenen Tabellen). Optional könntest du über "ISTFEHLER" oder "ISTBEZUG" noch die "#BEZUG!"- Fehlermeldungen durch ein "Ist noch nicht verfügbar" ersetzen.

deadletters · 29. November 2017

Kann ich mir gerade alles noch nicht vorstellen. Ich bin zwar nicht ganz Excelblöd, aber hab auch keine tiefgründigen Kenntnisse davon.
Wenn ich eine Hilfstabelle erstelle muss ja Excel dennoch jedes Quartal 'wissen', dass es auf die nächste Zelle zugreifen muss um seine Infos zu bekommen.
die Zelle soll einfach den Wert von Datei: "H4"_Q"H3-1"_Barbetrag.xlsx auswerten, dass muss doch irgendwie gehen, dass Excel den 1. Wert für den Dateinamen aus H4 holt, dann einen Teil Text, den nächsten Wert -1 aus einer Zelle und dann den Rest des Dateinamens, so dass am Ende der Wert aus z.B. 2017_Q3_Barbetrag.xlsx und Blatt Übersicht Zelle H54 angezeigt wird.

Dispo · 29. November 2017

Du kommst nicht weiter, solange die andere Tabelle geschlossen bleibt.

Was hälst, du von einem Makro, welches die entsprechende Tabelle öffnet und den gesuchten Wert übernimmt?

Code:

Workbooks.Open Filename:=Range("H4").Value & "_Q" & Range("H3").Value & "_Barbetrag.xlsx"
Sheets("Übersicht").Select
Wert = Range("F54").Value
ActiveWorkbook.Close

usw.

Das könnte man leicht mit einem Button einbinden. Eine Auslösung bei Änderung von H4 und H3 wäre auch möglich, aber dafür fehlen mir die Kenntnisse.

halwe · 30. November 2017

deadletters schrieb:
Wenn ich eine Hilfstabelle erstelle muss ja Excel dennoch jedes Quartal 'wissen', dass es auf die nächste Zelle zugreifen muss um seine Infos zu bekommen.

Deine Hilfstabelle könnte einfach auf alle Quartalsdateien vorab verweisen. Wenn du da 100 Zeilen drin hast, wären das 25 Jahre, auf deren Quartale du mit einem festen Verweis kommst. Damit brauchst du keine externen variablen Verweise, die, wie ich dir aus der Hilfe zitierte, nicht funktionieren.

Die Variabilität (Verweis auf das gerade benötigte Quartal) erzeugst du innerhalb der Arbeitsmappe, wo du ja mit den Verweisfunktionen auf eine beliebige Zeile (=Quartal) und eine beliebige Spalte (=Wert aus der jeweiligen Quartalsdatei) verweisen kannst.

Und die Fehlermeldungen (Verweis der Hilfstabelle auf eine noch nicht existierende Quartalsdatei) kannst du wie gesagt auch unterdrücken.

deadletters · 30. November 2017

Kann sich die Tabelle nicht automatisch den Dateipfad, in dem sie liegt auslesen, und diesen in einen direkten Pfad schreiben und dann für den Dateinamen die Infos aus den Zellen holen? ich möchte nicht, dass weitere Dateien offen sein müssen, sonst sind das irgendwann 25 Dateien, die offen sein müssen.

halwe · 30. November 2017

Die Datei aus dem aktuellen Pfad holen - das klappt praktisch von selbst. Du könntest also die jeweilige Vorquartalsdatei unter immer dem gleichen Namen dort reinspeichern und es würde klappen.
Aber - auch wenn du noch so oft fragst - variable Dateinamensverweise kriegst du nur zu geöffneten Tabellen hin.

Workaround 1 wäre sie wie oben beschrieben durch die Hilfstabelle feste Verweise zu ersetzen. Dann brauchen auch nicht alle 25 Dateien geöffnet zu sein.
Workaround 2 wäre ein Makro, welches die Verweise ändert, sobald du in der betreffenden Zelle etwas änderst. Aber das wäre dann eben ein Makro, welches manchen Nutzer irritieren könnte (Dateityp XLSM statt XLSX).

deadletters · 30. November 2017

halwe schrieb:
Workaround 1 wäre sie wie oben beschrieben durch die Hilfstabelle feste Verweise zu ersetzen. Dann brauchen auch nicht alle 25 Dateien geöffnet zu sein.

OK, und wie sieht dazu die Formel aus, dass die Zelle weiss, dass sie bei einem neuen Quartal die Info immer aus der nächsten Zeile holen muss?

halwe · 30. November 2017

Schau dir mal die Verweisfunktionen an. In deiner Hilfstabelle stehen die Quartalskennzeichnungen in der ersten Spalte und dahinter die einzelnen aus den Dateien gewonnen Werte. Die Verweisfunktion schaut in die erste Spalte um die richtige Zeile zu ermitteln und nimmt dann den Wert aus der gewünschten Spalte.
Ich habe den Syntax im Moment auch nicht Kopf, aber damit geht so was...
Gibt's wahrscheinlich hier auch schon Threads, aber letztlich kann ich es auch nicht besser erklären als die Excel Hilfe.

Suche

Excel Formel, für Wert aus anderer Arbeitsmappe, Dateiname bezogen aus versch. Zellen

deadletters

Cadet 4th Year

DeusoftheWired

Fleet Admiral

deadletters

Cadet 4th Year

halwe

Lt. Commander

deadletters

Cadet 4th Year

halwe

Lt. Commander

deadletters

Cadet 4th Year

Dispo

Lt. Junior Grade

halwe

Lt. Commander

deadletters

Cadet 4th Year

halwe

Lt. Commander

deadletters

Cadet 4th Year

halwe

Lt. Commander