Im Sverweis die Matrix auf eine Zelle beziehen

[rabbit07]

Hallo,

ich möchte in einer Excel-Tabelle die Verkäufe (Stückzahl) von bestimmten Tagen (bsp. nur Dienstage) erfassen.

Die Verkäufe des Tages sind jeweils in einer separaten Excel-Tabelle aufgelistet die als Name das Datum haben (bsp."06.08.2013")

Mit meiner Sverweis-Funktion funktioniert das auch, ich muss die aber bei jedem neuen Tag in die nächste Spalte kopieren und dann das Datum im Dateipfad der Sverweis-Funktion ändern.

Meine Frage lautet, ob es möglich ist die Sverweis-Funktion so anzupassen das ich nur das Datum eintragen muss und die Sverweis-Funktion ändert den Dateipfad automatisch ?

Im Anhang schicke ich zum bessern Verständnis ein Beispiel mit.

vielen Dank

PS. dachte da evtl. an einen Hyperlink oder den Sverweis mit einer weiteren Funktion zu verschachteln.

 

[dreamy_betty]

Das geht, ohne VBA jedoch mit Einschränkungen. Dafür benötigst du die Formel INDIREKT(), welche allerdings nur auf offene Arbeitsmappen zugreifen kann. Die Excel Datei mit dem Namen 06082013.xlsx (oder 13.08.2013.xlsx?) muss also im Hintergrund geöffnet sein.

Eine funktionierende Formel wie folgt aussehen:

=SVERWEIS($A4;INDIREKT("'["&B3&".xlsx]Summe'!$A$4:$F$80");3;0)

Wenn das Datum in B3 entweder nicht im korrekten Format vorhanden ist - wie bereits erwähnt, schreibst du einmal von 06082013 und einmal im Format 13.08.2013 - und/oder in Zelle B3 nicht als Text formatiert wurde, wirst du mit einer eigenen Textformatierung arbeiten müssen:

=TEXT(HEUTE();"TT.MM.JJJJ") => 19.08.2013
=TEXT("19.8.13";"TT.MM.JJJJ") => 19.8.2013
=TEXT(HEUTE();"TTMMJJJJ") => 19082013

Auf deine Formel angepasst könnte dies beispielsweise wie folgt aussehen. Dafür musst du aber die Anführungszeichen aus B3 (etc) entfernen und die Zelle als Datum formatieren.

=SVERWEIS($A4;INDIREKT("'["&TEXT(B3;"TTMMJJJJ")&".xlsx]Summe'!$A$4:$F$80");3;0) => '[13082013.xlsx]Summe'!$A$4:$F$80

 

[rabbit07]

Hallo dreamy-betty,

vielen Dank für deine Hilfe. Die Formel funktioniert so wie Du sie beschrieben hast - allerdings auch nur mit der von dir genannten Einschränkung.

Ich bin im Moment dabei, mich mit VBA vertraut zu machen, stehe aber noch ganz am Anfang. Hättest du evtl. ein paar nützliche Tips und Tricks für mich, gerade auch in Bezug auf meine konkrete Problemstellung ?

 

[dreamy_betty]

Wenn ich vor diesem Problem stehen würde, wäre mein erster Gedanke, die benötigten Arbeitsmappen bei Bedarf versteckt im Hintergrund zu laden. Ob das in der Praxis umsetzbar ist, hängt natürlich von der Anzahl der so geöffneten Dateien ab - schließlich verschlingt jede weitere Excel-Instanz zusätzlichen Arbeitsspeicher.

Außerdem muss bedacht werden, dass diese Dateien versteckt geöffnet wurden und nach Beenden des sichtbaren Excel-Programms nicht automatisch geschlossen werden. Man benötigt also Code zur Verwaltung, der beim Schließen (idealerweise in Sub Workbook_BeforeClose()) auch alle versteckten Instanzen schließt.


Eine andere Herangehensweise wäre es, das Datum in Zeile 3 in eine selbst erstellte Formel/Funktion zu geben. Diese VBA-Funktion schreibt dann in Zeilen 4 bis x die vollständigen Formeln, sodass man sich den Umweg via INDIREKT() sparen kann und nicht ständig die anderen Arbeitsmappen geöffnet haben muss. Das ist nicht so schwierig wie man denkt, da man sogar die Formeln des deutschen Excels im VBA-Code verwenden kann (.FormulaLocal beziehungsweise .FormulaR1C1Local zum einfacheren Erstellen von Formeln in Zellbereichen).