Externe Bezüge Zeile Variabel

[Tyler]

Guten Morgen zusammen, 

Ich stehe vor einem Problem bei meiner Formel.
Ich hoffe ihr habt eine Idee, und könnt mir helfen. 

Zu dem Problem:
Ich nutze in meiner Excel Externene Bezüge zu einer anderen Excel. 
Hier ein Beispiel 
=[Mappe1]Tabelle1!$C$3
   
In Mappe2 stehen nun auch benötigte Werte die ich gerne beziehen möchte. 
Nur nicht in der selben Zeile, allerdings der selben Spalte. 
Im Beispiel hier anstatt Zeile "3" in Zeile "1".
   
Ich möchte immer auf die Zeile mit dem Wert 100 in Spalte A zugreifen. 
Ich würde gerne wissen ob ich den Wert 100 in Spalte "A" suchen kann, 
um die benötigte Zeile zu finden, damit die Formel dann auf $C$1 zugreift. 

Ich hoffe es ist verständlich und ihr könnt mir helfen. 

Vielen Dank 
LG
Tyler

[neopa]

Hallo Tyler,

wenn ich Deine Angaben richtig interpretiere suchst Du in [Mappe2.xlsx]Tabelle1!B1 folgende Formel:

=SVERWEIS($A1;[Mappe1.xlsx]Tabelle1!$A:$E;SPALTE(B1);0)

Diese nach rechts und unten kopieren. Bei Bedarf auch noch mit WENNFEHLER() klammern.

Mehr zu SVERWEIS() sieh mal z.B. hier: http://www.online-excel.de/excel/singsel.php?f=9

[Tyler]

Hallo neopa, 

Danke für deine Antwort, ich bin nicht sicher ob ein SVERWEIS mir hilft. 
Ich habe es damit leider nicht geschafft. 
Hier mal meine Formel:
='C:\ZRO\Daily figure\201704\[2017-04-05.xlsx]Sheet1'!$E$3

Am 05-04-2017 (.xlsx Datei) ist der Wert den ich suche in "E3". 
Allerdings kann schon am 06-04- (.xlsx Datei) der gesuchte Wert in "E5" stehen, und am 10-04- in "E2". 
In Spalte "B" stehen immer Gruppen, (100, 200, 300, usw.) und ich würde mich gern an den Gruppen orientieren. 
Soll heißen wenn: am/in datei 07-04-2017.xlsx in Spalte "B" Gruppe 200 in Zeile 4 steht,(B4) möchte ich den Wert aus "E4" haben. 

Ich hoffe das ist irgendwie nachvollziehbar und umsetzbar  :)

Danke
MfG Tyler

[neopa]

Hallo Tyler,

ich hatte Dir einen Lösungsvorschlag für meine Interpretation Deiner nicht ganz eindeutigen Angaben aufgezeigt. Du hast diesen offensichtlich nicht an Deinen hier aufgezeigten Daten getestet.

Deine neuerlichen Angaben sind nun für mich noch nicht mal mehr interpretierbar.
Lade einen relevanten Auszug aus Deinen beiden Dateien (nicht als Bild- sondern als als xösx-Datei(en) hier hoch)  und erkläre daran Deine Zielstellung. Dann sehen wir weiter.

[Tyler]

Hallo Werner,

ich versuche das gerne nochmal zu verdeutlichen.
Hier nochmal die Formel auch wenn die wahrscheinlich klar ist.
   
Ich habe auch eine Excel angehangen, vielleicht macht es das deutlicher.
Einfach gesagt, möchte ich die Werte aus den Einzelnen Datumsdateien, in einer jeweiligen Gruppentabelle zusammenfügen zu den jeweiligen Datums.
Mein Problem dabei ist, dass in der jeweiligen Datei (2017-04-05.xlsx 2017-04-05.xlsx usw.) der benötigte Wert in einer anderen Zeile stehet.
Ich habe vesucht das in der Beispiel Datei abzubilden. Und in Orange für Gruppe 100 angemarkert, die Roten Zellen wären z.B. Formeln die auf Falsche Zellen zugreifen.

Ich hoffe es ist besser interpretierbar, und das du mir helfen kannst. Denn ich bin schon am verzweifeln

Danke
Tyler

[neopa]

Hallo Tyler,

jetzt ist Dein Anliegen verständlicher, allerdings so wie Deine Daten vorliegen, bräuchtest Du eine VBA-Lösung (aus solchen halte ich mich heraus) oder ein spez. Add-Ins um mit INDIREKT() auf Deine Datendateien zuzugreifen. Mit dem Add-In morefunc (mal danach googlen) soll dies möglich sein (selber setze ich es aber nicht ein, weil ich es nicht brauche) . Damit könntest Du dann über das Datum und den jeweiligen Tabellenblattnamen sowie der bereits aufgezeigten SVERWEIS()-Funktion, das von Dir erstrebte erreichen

Nachfolgend eine (teils händische) Zwischenlösung  (allerdings nur für nicht gar zu vielen Datendateien, weil es dann zu aufwendig wird)  könnte folgende sein.

In Deiner Datei sammelst Du in einem eigenständigen Tabellenblatt (ich hab es "Daten" genannt) durch (händische Kopie Deine Dateidaten zusammen.
mit einer zusätzlichen Hilfsspalte. Die dortige Formel B2 einfach dort nach unten kopieren. Dort den entsprechenden Datumswert in Spalte A erfassen.

Nun definierst Du die benannte Formel (ich hab sie Gr_Nr bezeichnet) im Namensmanager (mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=60 und ff)  Anschließend gruppierst Du Deine 5 Gruppen-Tabellenblätter (dazu "Gruppe100" im Tabellenblattreiter aktivieren und bei gedrückter linken Maustaste danach "Gruppe100")

Vorausgesetzt in Spalte A stehen überall die Datumswerte Deiner Datendateien, musst Du lediglich nachfolgende Formel in A2 einkopieren und diese nach rechts und unten kopieren (wenn die Daten im Datentabellenblatt noch nach Spalte aufwärts sortierst kann in der SVERWEIS()-Formel das letzte Argument anstelle 0 eine 1 sein oder wegfallen; beschleunigt die Ausgabe bei sehr vielen Daten).

Abschließend nicht vergessen, die Gruppierung der Tabellenblätter wieder aufheben (z.B. rechte Maustaste,, und da Funktion ganz unten).

Gruppe100

	A	B	C	D	E	F	G	H	I
1	Datum	A	B	C	D	E	F	G	H
2	03.04.2017	72	65	39	767	479	303	852	1905,7
3	04.04.2017	70	70	0	327	327	0	0	3997,7
4	05.04.2017	302	302	0	864	864	0	0	5310,6
5

Formeln der Tabelle

Zelle Formel B2 =SVERWEIS($A2&Gr_Nr;Daten!$B:$K;SPALTE(C1);0)

Namen in Formeln Zelle Name Bezieht sich auf B2 Gr_Nr =TEIL(ZELLE("Dateiname";!$A$1);FINDEN("]";ZELLE("Dateiname";!$A$1))+1;31) Namen verstehen

Daten

	A	B	C	D	E	F	G	H	I	J	K
1	Datum		Gruppe	A	B	C	D	E	F	G	H
2	03.04.2017	42828Gruppe400	400	68	63	20	615	361	262	628	1348
3	03.04.2017	42828Gruppe200	200	50	50	0	221	221	0	0	1126,67
4	03.04.2017	42828Gruppe300	300	145	145	0	756	756	0	0	3963,81
5	03.04.2017	42828Gruppe100	100	72	65	39	767	479	303	852	1905,68
6	03.04.2017	42828Gruppe500	500	4	1	4	175	3	172	741	54
7	04.04.2017	42829Gruppe200	200	4	1	4	175	3	172	741	54
8	04.04.2017	42829Gruppe400	400	2	2	0	5	5	0	0	10
9	04.04.2017	42829Gruppe500	500	16	16	0	146	146	0	0	632
10	04.04.2017	42829Gruppe300	300	3	0	3	180	0	180	1444	0
11	04.04.2017	42829Gruppe100	100	70	70	0	327	327	0	0	3997,67
12	05.04.2017	42830Gruppe300	300	63	63	0	155	155	0	0	920,73
13	05.04.2017	42830Gruppe100	100	302	302	0	864	864	0	0	5310,58
14	05.04.2017	42830Gruppe500	500	602	602	0	2841	2841	0	0	22136
15	05.04.2017	42830Gruppe400	400	3	3	0	16	16	0	0	1776
16	05.04.2017	42830Gruppe200	200	6	6	0	23	23	0	0	1479
17	usw

Formeln der Tabelle

Zelle Formel B2 =A2&"Gruppe"&C2

[Tyler]

Hallo Werner, 

 Vielen Dank für Deine Mühe, ich werde mal mein Glück versuchen. 

VIELEN DANK