Externer Bezug bei geschlossener Arbeitsmappe

[DocMarley]

Guten Tag zusammen,

ich bin noch ganz frisch in diesem Forum und meine Suche nach entsprechenden Beiträgen zu meinem Problem hat keine hilfreichen Treffer ergeben. Man möge es mir bitte nachsehen, falls es einen ähnlichen Thread bereits gibt. Nun zu meinem Problem:

Ich bin grade dabei neue Rechnungseingangs- und Ausgangsbücher für das Jahr 2017 zu erstellen. Um es dem Anwender möglichst leicht zu machen, möchte ich neben dem Rechnungseingangs- und dem Rechnungsausgangsbuch noch eine dritte Exceldatei anlegen in der Ergebnisse berechnet werden (Berechnung 2017). In dieser möchte ich einen Bezug zu den anderen beiden Dateien erstellen, sodass gewisse Werte direkt übernommen und summiert werden. Bei geöffneter Arbeitsmappe funktioniert dies mit Hilfe der SUMMEWENN-Funktion auch hervorragend. Sind die Rechnungsbücher aber nun geschlossen meldet die Zieldatei immer den Fehler #BEZUG!. Ich konnte mich nun schon durch die Weiten der Hilfeforen kämpfen und feststellen, dass die SUMMEWENN-Funktion bei geschlossener Quelldatei nicht hilfreich ist und man es mit einer SUMME(WENN....) realisieren könnte. Nun scheitere ich aber an der Umsetzung.

Quelldatei: Rechnungseingangsbuch2017
Zieldatei: Berechnung2017

Meine SUMMEWENN-Funktion sah so aus:

=SUMMEWENN('Rechnungseingangsbuch2017.xlsx'!Tabelle2[Typ];A5;'Rechnungseingangsbuch2017.xlsx'!Tabelle2[brutto])

Diese funktioniert auch wunderbar, bei geöffnetem Rechnungseingangsbuch. Sobald diese allerdings geschlossen wird, ersetzt Excel den Namen "Rechnungseingangsbuch2017.xlsx" durch den kompletten Pfad hin zur Quelldatei und die Formel wirft als Ergebnis eben #BEZUG! aus.

Könnte mir jemand nützliche Hinweise geben mit welcher Formel ich mein Vorhaben realisieren kann?

Vorab schon einmal besten Dank an alle die, die sich mein wirres Geschreibe an einem Samstagvormittag "antun" ;)

PS: Falls benötigt, der Pfad zur Quelldatei lautet: D:\Eigene Dateien\EIGENE DATEIEN\Auftragsbearbeitung\Buchhaltung\RA+RE\Rechnungseingangsbuch2017.xlsx

[Peter]

Hallo,

Summenwenn() kann nicht auf geschlossene Dateien zugreifen.

Ich würde mit Summenprodukt oder einer Pivottabelle arbeiten. Ich habe hier im Moment kein Zugriff auf Excel, deswegen nur ein Tipp:

Beide Dateien öffnen - Summenprodukt einrichten und die Quelldatei schließen. XL ergänzt dann die Formel automatisch um den Pfad.

[DocMarley]

Bin für jeden Tipp dankbar. Werde es direkt mal ausprobieren.

edit: Nach kurzer Überlegung komme ich zu der Einsicht, dass SUMMENPRODUKT für meine Absicht nicht hilfreich ist, oder ich nicht weiß wie ich sie mir richtig zusammenbauen kann.

Ich möchte, dass die Formel folgendes für mich macht:

Prüfen ob ein Wert (beispielsweise aus Zelle A2 der Zieldatei) in einer Spalte einer Tabelle der Quelldatei vorkommt. Immer wenn dieser Wert vorkommt soll ein zugehöriger Wert aus einer anderen Spalte der Quelldatei genommen und mit allen anderen Werten die die Kriterien erfüllen summiert werden.

[shift-del]

=SUMMEWENN('Rechnungseingangsbuch2017.xlsx'!Tabelle2[Typ];A5;'Rechnungseingangsbuch2017.xlsx'!Tabelle2[brutto])

In der Formel hast du zwei Probleme bezüglich Zugriff auf geschlossene Dateien.
Ein mal wegen SUMMEWENN() und ein mal wegen der Verwendung von Tabellen.

Warum packst du nicht alles in eine Mappe?
Alternativ könntest auf die anderen Mappen auch mit Power Query zugreifen.

[DocMarley]

Hi,

das habe ich mir auch schon überlegt. Da die "Nutzer" der Dateien allerdings relativ unerfahren sind, wollte ich es aufteilen, um es übersichtlicher zu machen.
Nach dem Motto: Wenn eine Rechnung kommt dahin, wenn du eine Rechnung schreibst dahin und was dabei rauskommt siehst du da.
Habe eine wenig Angst, dass mir die Dame bei der Verwendung alles kaputthaut :)

Power Query sagt mir leider garnichts. Meine "Kenntnisse" reichen nur selten über den Excelhorizont hinaus...

[DocMarley]

Habe jetzt selber noch etwas rumgebastelt. Meine Formel sieht nun so aus:

{=SUMME(WENN('D:\Eigene Dateien\EIGENE DATEIEN\Auftragsbearbeitung\Buchhaltung\RA+RE\Rechnungseingangsbuch 2017.xlsx'!Tabelle2[Typ];A2;'D:\Eigene Dateien\EIGENE DATEIEN\Auftragsbearbeitung\Buchhaltung\RA+RE\Rechnungseingangsbuch 2017.xlsx'!Tabelle2[brutto]))}

Nach Strg+Shift+Enter wird der Pfad bei geöffnter Datei durch Namen ersetzt und es kommt als Ergebnis: #WERT! mit der Info: Ein in der Formel verwendeter Wert ist vom falschen Datentyp
Bei geschlossener Datei kommt wieder #BEZUG! mit der Info: Durch das Verschieben oder Löschen von Zellen ist ein ungültiger Zellbezug entstanden, oder die Funktion gibt einen ungültigen Bezug zurück


Ich glaube der Ansatz ist schon nicht schlecht, allerdings beginne ich langsam zu verzweifeln

[Peter]

Hallo,

kannst du mal eine Beispielsdatei mit anonymisierten Daten aber korrektem Tabellenaufbau hochladen?

[shift-del]

{=SUMME(WENN('D:\Eigene Dateien\EIGENE DATEIEN\Auftragsbearbeitung\Buchhaltung\RA+RE\Rechnungseingangsbuch 2017.xlsx'!Tabelle2[Typ];A2;'D:\Eigene Dateien\EIGENE DATEIEN\Auftragsbearbeitung\Buchhaltung\RA+RE\Rechnungseingangsbuch 2017.xlsx'!Tabelle2[brutto]))}
[...]
Bei geschlossener Datei kommt wieder #BEZUG!

Du hast meine Antwort offenbar nicht komplett gelesen.

[DocMarley]

Du hast meine Antwort offenbar nicht komplett gelesen.

Meinst du die Tabellen? Wie umgehe ich das denn? Ich habe pro Datei eh nur eine benannte Tabelle drin. Diesen Vorschlag (also den Bezug Tabelle2) hat Excel bei der Erstellung der SUMMEWENN-Funktion selbst so angegeben...

[DocMarley]

Hallo,

kannst du mal eine Beispielsdatei mit anonymisierten Daten aber korrektem Tabellenaufbau hochladen?

Mein erstes Mal. Also stark vereinfacht sieht die Quelldatei so aus.
Nun möchte ich halt in einer zweiten Datei bei geschlossener Quelldatei Summen der einzelnen Positionen (bank, personal, usw.) haben ;)