Werte Großer Tabellen dateiübergreifend berechnen

[Patrick1981]

Guten Tag,

Da ich neu in diesem Forum bin, erst mal ein fröhliches Hallo an alle Mitglieder.

Nun zu meinem Problemchen.

Ich habe zwei Exceltabellen in zwei Dateien  
Datei Nr. 1 ist eine Anwesenheitsliste in dieser werden die Arbeitsorte (Touren) und die Arbeitszeiten der Mitarbeiter erfasst.
Datei Nr. 2 ist eine Übersicht wie viel Stunden Pro Woche ausgegeben wurden. Die Stunden sollen aus Datei Nr. 1 Übernommen werden

Das ganze Funktioniert super mit der Formel =SUMMEWENN('[Datei 1.xlsx]Juli'!$E:$E;"A1";('[Datei 1.xlsx]Juli'!$F:$F))

Nun ist es sehr mühselig für jeden Wochentag und Tour die Formeln anzupassen (kopieren und einfügen ergibt logischerweise immer den wert von A1 [Autotour 1] wieder).
Es müsste bei jeder Zelle pro Tour "A1" und bei jedem Wochentag $E:$E $F:$F geändert werden

KLeines Beispiel:

Tour A1
Montag
=SUMMEWENN('[Datei 1.xlsx]Juli'!$E:$E;"A1";('[Datei 1.xlsx]Juli'!$F:$F))
Dienstag
=SUMMEWENN('[Datei 1.xlsx]Juli'!$G:$G;"A1";('[Datei 1.xlsx]Juli'!$H:$H))
Mittwoch
=SUMMEWENN('[Datei 1.xlsx]Juli'!$i:$i;"A1";('[Datei 1.xlsx]Juli'!$J:$J))

Tour A2
Montag
=SUMMEWENN('[Datei 1.xlsx]Juli'!$E:$E;"A2";('[Datei 1.xlsx]Juli'!$F:$F))
Dienstag
=SUMMEWENN('[Datei 1.xlsx]Juli'!$G:$G;"A2";('[Datei 1.xlsx]Juli'!$H:$H))
Mittwoch
=SUMMEWENN('[Datei 1.xlsx]Juli'!$i:$i;"A2";('[Datei 1.xlsx]Juli'!$J:$J))

Hat jemand eine Idee wie man das lösen kann?   :16:

die beiden Dateien habe ich angehangen.

Danke schon mal im Voraus.

Gruß Patrick1981

[BoskoBiati]

Hi,

einfach so:

Code:

=SUMMEWENN('[Datei 1.xlsx]Juli'!$E:$E;A4;('[Datei 1.xlsx]Juli'!$F:$F))

bzw.:

Code:

=SUMMEWENN('K:\[Anwesenheit_111649 2018 test.xlsx]Juli'!$Q:$Q;$A4;('K:\[Anwesenheit_111649 2018 test.xlsx]Juli'!$R:$R))

[Patrick1981]

Hi,

einfach so:

Code:

=SUMMEWENN('[Datei 1.xlsx]Juli'!$E:$E;A4;('[Datei 1.xlsx]Juli'!$F:$F))

bzw.:

Code:

=SUMMEWENN('K:\[Anwesenheit_111649 2018 test.xlsx]Juli'!$Q:$Q;$A4;('K:\[Anwesenheit_111649 2018 test.xlsx]Juli'!$R:$R))

Danke für die schnelle Antwort, 

das funktioniert schon besser als meine Version ;)

Kopieren nach unten Funktioniert super aber leider nicht nach rechts.

[Frogger1986]

Oder mit Makro und Dropdown (Dropdown in Zelle E1 mit allen Monaten)

Das Makro funktioniert nur solange der Bereich von A1 bis F14 gleich bleibt! 

Dropdown in Zelle E1 mit allen Monaten dann Reiter Tabellenblatt Rechtsklick Code Anzeigen und dann das einfügen und danach Tabelle im format .xlsm speichern

Code:

Option Explicit

Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$E$1" Then
On Error Resume Next
ActiveSheet.Range("B4:B14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$E:$E;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$F:$F));"""")"
ActiveSheet.Range("C4:C14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$G:$G;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$H:$H));"""")"
ActiveSheet.Range("D4:D14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$I:$I;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$J:$J));"""")"
ActiveSheet.Range("E4:E14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$K:$K;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$L:$L));"""")"
ActiveSheet.Range("F4:F14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$M:$M;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$N:$N));"""")"

End If
End Sub

[BoskoBiati]

Hi,

dann so:

Code:

=SUMMEWENN(INDEX('[Datei 1.xlsx]Juli'!$D:$N;;SPALTE(A1)*2);$A4;INDEX('[Datei 1.xlsx]Juli'!$D:$N;;SPALTE(A1)*2+1))

[Patrick1981]

Danke.
Das ist genau das was ich gesucht habe.

[Patrick1981]

Oder mit Makro und Dropdown (Dropdown in Zelle E1 mit allen Monaten)

Das Makro funktioniert nur solange der Bereich von A1 bis F14 gleich bleibt! 

Dropdown in Zelle E1 mit allen Monaten dann Reiter Tabellenblatt Rechtsklick Code Anzeigen und dann das einfügen und danach Tabelle im format .xlsm speichern

Code:

Option Explicit

Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$E$1" Then
On Error Resume Next
ActiveSheet.Range("B4:B14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$E:$E;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$F:$F));"""")"
ActiveSheet.Range("C4:C14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$G:$G;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$H:$H));"""")"
ActiveSheet.Range("D4:D14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$I:$I;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$J:$J));"""")"
ActiveSheet.Range("E4:E14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$K:$K;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$L:$L));"""")"
ActiveSheet.Range("F4:F14").FormulaLocal = "=wennfehler(SUMMEWENN('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$M:$M;$A4;('Z:\Dokumente\Arbeit\111649\[Datei 1.xlsx]" & ActiveSheet.Range("E1") & "'!$N:$N));"""")"

End If
End Sub

Ist super Funktioniert auch. Bringt mich aber nur kurzfristig weiter. da sich die Anzahl der Touren ändern kann.