Office: (Office 2010) Sverweis über mehrere Tabellen in anderer Datei

Hallo zusammen,

muss leider direkt nochmal um Hilfe bitte.

Diese 3 Sverweise verweisen jeweils auf je ein unterschiedliches Tabellenblatt in einer anderen Excel Datei (Datei ist die selbe nur Sheet ist unterschiedlich).

Code:

=WENN(ISTFEHLER(SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B][SIZE=5]Amberg[/SIZE][/B]'!$C$8:$H$27;1;FALSCH));"";SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B]Amberg[/B]'!$C$8:$H$27;1;FALSCH))

Code:

=WENN(ISTFEHLER(SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B][SIZE=5]Berg[/SIZE][/B]'!$C$8:$H$27;1;FALSCH));"";SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B]Berg[/B]'!$C$8:$H$27;1;FALSCH))

Code:

=WENN(ISTFEHLER(SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B][SIZE=5]Berching[/SIZE][/B]'!$C$8:$H$27;1;FALSCH));"";SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][B]Berching[/B]'!$C$8:$H$27;1;FALSCH))

Ich würde diese 3 Sverweise jetzt gerne nach folgendem Muster zusammenbauen:

Code:

=SVERWEIS(H4;INDIREKT("'"&INDEX({"[B][SIZE=4]Amberg";"Berg";"Berching[/SIZE][/B]"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"Amberg";"Berg";"Berching"}&"'!A:A");H4)>0)+0;0))&"'!$C$8:$H$27");1;0)

Jedoch weiß ich nicht wie ich in diese Formel den externen Dateipfad mit einbaue?!?!

Kann mir das jemand einmal exemplarisch vormachen?


vielen Dank

Mfg

 

Hola,

zum Einen gibt es ab Excel 2007 Wennfehler():

Code:

=WENNFEHLER([COLOR=#333333]SVERWEIS($H$4;'[NL 222; Prüfliste E+1 3374.xlsm][/COLOR][B][SIZE=4]Amberg[/SIZE][/B][COLOR=#333333]'!$C$8:$H$27;1;FALSCH);"")

Zum Anderen muss die Datei bei der Abfrage wegen Indirekt() immer geöffnet sein.

Was hast du denn genau vor?

Gruß,
steve1da[/COLOR]

 

Hey,

Diesen Sverweis habe ich, der macht auch was er soll! Nur sucht er eben nur auf dem einen Tabellenblatt "Neumarkt". Jetzt hätte ich gerne das er diese eine Abfrage eben auch auf allen anderen Tabellenblättern in dieser Datei macht. Das wollte ich daher irgendwie in einer Formel verschachteln.

Konnte ich das soweit einigermaßen rüberbringen?

Mfg

 

Hola,

Code:

=WENNFEHLER(SVERWEIS(H4;INDIREKT("'"&INDEX({"[NL 222Prüfliste E+1 3374.xlsx]Amberg";"[NL 222Prüfliste E+1 3374.xlsx]Berg";"[NL 222Prüfliste E+1 3374.xlsx]Berching"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"[NL 222Prüfliste E+1 3374.xlsx]Amberg";"[NL 222Prüfliste E+1 3374.xlsx]Berg";"[NL 222Prüfliste E+1 3374.xlsx]Berching"}&"'!C:C");H4)>0)+0;0))&"'!C8:H27");1;0);"")

Warum aber hat deine Suchmatrix 6 Spalten, wenn du dir eh immer nur den Wert aus Spalte C zurückgeben lässt?

Gruß,
steve1da

 

Hey,

Sie hat 6 Spalten weil noch 5 weitere Abfragen kommen die ich der einfachheit halber hier jetzt nicht mehr aufgeführt habe. Will euch nicht unnötig Arbeit machen, irgendwas (das leichte) muss ich dann schon selber machen. So lerne ich das dann hoffentlich auch irgendwann.

Vielen Dank bereits für das Umbauen der Formel! Sie funktioniert auch schon soweit...zumindest bekomme ich keinen Fehler. Bekomme leider grade gar nichts ausgegeben obwohl er etwas finden sollte. Kann es sein, dass der Sverweis an sich nach etwas falschem sucht?
verstehe zumindest gerade nicht warum er keinen Fehler bringt mir aber nichts ausgibt, obwohl er sollte

Edit:
Habe gerade mal getestet was passiert wenn ich einen absichtlich falschen Dateinamen angebe. Dann gibt er auch keinen Fehler aus. Vielleicht hilft das bei der Lösungsfindung.

Zur Erklärung. Die beiden Dateien befinden sich im selben Ordner. Muss man dennoch evtl. den ganzen pfad angeben? Also C:/Programme usw.?

Oder ist einfach nur die Abfrage in der Formel falsch gewählt von mir?


Mfg

 

Hola,

es gibt keine Fehlermeldung, weil ich die mit Wennfehler() verschwinden lasse.

Code:

=SVERWEIS(H4;INDIREKT("'"&INDEX({"[NL 222Prüfliste E+1 3374.xlsx]Amberg";"[NL 222Prüfliste E+1 3374.xlsx]Berg";"[NL 222Prüfliste E+1 3374.xlsx]Berching"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"[NL 222Prüfliste E+1 3374.xlsx]Amberg";"[NL 222Prüfliste E+1 3374.xlsx]Berg";"[NL 222Prüfliste E+1 3374.xlsx]Berching"}&"'!C:C");H4)>0)+0;0))&"'!C8:H27");1;0)

So lautet die Formel, die dann den Fehler anzeigt.

Ich habe allerdings in der Formel auf eine xlsx Datei verwiesen, du hast eine xlsm Datei, daran wird es liegen. Also in meiner Formel xlsx zu xlsm ändern.

Gruß,
steve1da

 

Der Dateiname war falsch! Genau das wars auch, wie du gesagt hattest!

Im Dateinamen war neben der endung auch noch ein Semikolon enthalten. Das war in der Formel nicht mit drin. Habe das jetzt berichtigt und siehe da, es geht!

Dir vielen vielen Dank für die Hilfe!
Jetzt habe ich nur noch eine Baustelle in Excel und dann gehts ins Wochenende!

Dir jetzt schon mal einen guten Start ins Wochenende.

Grüße

 

Hi steve1da,

ich hab mir jetzt am Wochenende weiterhin mit meiner Excel Datei beschäftigt und Sie funktioniert eigentlich schon wirklich gut....dachte ich.

Nur habe ich jetzt einen Fehler entdeckt den ich einfach nicht verstehe.....vielleicht könntest du mir da auf den springenden Punkt helfen?!?

Das hier ist mein kompletter Sverweis nach deiner Vorlage:

Code:

=WENNFEHLER(SVERWEIS($G$4;INDIREKT("'"&INDEX({"[NL 222 Prüfliste E+1 3374.xlsm]Amberg";"[NL 222 Prüfliste E+1 3374.xlsm]Berg";"[NL 222 Prüfliste E+1 3374.xlsm]Berching";"[NL 222 Prüfliste E+1 3374.xlsm]Ebermannsdorf";"[NL 222 Prüfliste E+1 3374.xlsm]Freystadt";"[NL 222 Prüfliste E+1 3374.xlsm]Kastl";"[NL 222 Prüfliste E+1 3374.xlsm]Dietfurt";"[NL 222 Prüfliste E+1 3374.xlsm]Neukirchen";"[NL 222 Prüfliste E+1 3374.xlsm]Neumarkt";"[NL 222 Prüfliste E+1 3374.xlsm]Parsberg";"[NL 222 Prüfliste E+1 3374.xlsm]Postbauer-Heng";"[NL 222 Prüfliste E+1 3374.xlsm]Sulzbach-Rosenberg";"[NL 222 Prüfliste E+1 3374.xlsm]Velburg"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"[NL 222 Prüfliste E+1 3374.xlsm]Amberg";"[NL 222 Prüfliste E+1 3374.xlsm]Berg";"[NL 222 Prüfliste E+1 3374.xlsm]Berching";"[NL 222 Prüfliste E+1 3374.xlsm]Ebermannsdorf";"[NL 222 Prüfliste E+1 3374.xlsm]Freystadt";"[NL 222 Prüfliste E+1 3374.xlsm]Kastl";"[NL 222 Prüfliste E+1 3374.xlsm]Dietfurt";"[NL 222 Prüfliste E+1 3374.xlsm]Neukirchen";"[NL 222 Prüfliste E+1 3374.xlsm]Neumarkt";"[NL 222 Prüfliste E+1 3374.xlsm]Parsberg";"[NL 222 Prüfliste E+1 3374.xlsm]Postbauer-Heng";"[NL 222 Prüfliste E+1 3374.xlsm]Sulzbach-Rosenberg";"[NL 222 Prüfliste E+1 3374.xlsm]Velburg"}&"'!C:C");$G$4)>0)+0;0))&"'!$C$8:$H$38");1;0);"")

Dieser Sverweis soll ja eigentlich nur im Bereich von C8 bis H38 suchen.

Davon gibt es 6 weitere, die alle einen anderen definierten Suchbereich haben.

Leider suchen aber irgendwie alle Sverweise in allen Bereichen und ich verstehe überhaupt nicht wieso sie das tun??? Da der Bereich bei jedem individuell Festgelegt ist mit der Zeile

Code:

$G$4)>0)+0;0))&"'!$C$8:$H$38");1;0);"")

Wo liegt hier der Fehler?? bzw. sucht dieser Sverweis auch wirklich nur in C8 bis C38????

Danke für deine Hilfe

 

Hola,

ja, nur in diesem Bereich wird gesucht.
Was bei dir falsch läuft, kann ich aus der Ferne leider nicht sehen.

Gruß,
steve1da

 

Hat sich erledigt. Problem beseitigt. War eine falsche Verknüpfung eines dynamischen Dropdown Feldes. Formel arbeitet Korrekt.

 

Muss den Thread leider nochmal hochholen.

Gleiche Ausgangssituation wie oben (suche Wert in einer anderen Datei) nur muss der "Sverweis" jetzt einen Wert Links vom gesuchten Wert ausgeben.

Die Musterformel hierfür wäre ja

Code:

=INDEX(A:A;VERGLEICH("Schulze";B:B;0))

Wie sieht das jetzt aus, wenn diese Formel wiederrum genau in den folgenden Blättern suchen soll und wenn kein Wert gefunden wird nichts ausgibt?

Code:

=WENNFEHLER(SVERWEIS($G$4;INDIREKT("'"&INDEX({"[NL 222 Prüfliste E+1 3374.xlsm]Amberg";"[NL 222 Prüfliste E+1 3374.xlsm]Berg";"[NL 222 Prüfliste E+1 3374.xlsm]Berching";"[NL 222 Prüfliste E+1 3374.xlsm]Ebermannsdorf";"[NL 222 Prüfliste E+1 3374.xlsm]Freystadt";"[NL 222 Prüfliste E+1 3374.xlsm]Kastl";"[NL 222 Prüfliste E+1 3374.xlsm]Dietfurt";"[NL 222 Prüfliste E+1 3374.xlsm]Neukirchen";"[NL 222 Prüfliste E+1 3374.xlsm]Neumarkt";"[NL 222 Prüfliste E+1 3374.xlsm]Parsberg";"[NL 222 Prüfliste E+1 3374.xlsm]Postbauer-Heng";"[NL 222 Prüfliste E+1 3374.xlsm]Sulzbach-Rosenberg";"[NL 222 Prüfliste E+1 3374.xlsm]Velburg"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"[NL 222 Prüfliste E+1 3374.xlsm]Amberg";"[NL 222 Prüfliste E+1 3374.xlsm]Berg";"[NL 222 Prüfliste E+1 3374.xlsm]Berching";"[NL 222 Prüfliste E+1 3374.xlsm]Ebermannsdorf";"[NL 222 Prüfliste E+1 3374.xlsm]Freystadt";"[NL 222 Prüfliste E+1 3374.xlsm]Kastl";"[NL 222 Prüfliste E+1 3374.xlsm]Dietfurt";"[NL 222 Prüfliste E+1 3374.xlsm]Neukirchen";"[NL 222 Prüfliste E+1 3374.xlsm]Neumarkt";"[NL 222 Prüfliste E+1 3374.xlsm]Parsberg";"[NL 222 Prüfliste E+1 3374.xlsm]Postbauer-Heng";"[NL 222 Prüfliste E+1 3374.xlsm]Sulzbach-Rosenberg";"[NL 222 Prüfliste E+1 3374.xlsm]Velburg"}&"'!C:C");$G$4)>0)+0;0))&"'!$C$8:$H$38");1;0);"")

Danke

 

Hola,

je länger ich drüber nachdenke würde ich vorschlagen, das Suchkriterium immer vor die zu suchenden Werte zu schreiben.

Aktuell habe ich keine Idee für den Sverweis nach links über mehrere Tabellen.

Gruß,
steve1da

 

Ok, schade drum. Wenn selbst du sagst das es nicht wirklich leicht zu machen ist dann lass ich das wohl besser.

Im Kopf wars so schön einfach :-)

Dann muss ich wohl oder übel die Spalten tauschen. Klingt jetzt nach weniger Arbeit als es dann schlussendlich ist :-)

Danke dir trotzdem.

Mfg

 

Hola,

wenn du links von den Werten die zurück gegeben werden sollen, noch eine freie Spalte hast, kann du die Daten doch einfach da hinein kopieren. Somit müsstest du nichts tauschen.

Gruß,
steve1da

 

das wäre mein Trick 17 gewesen. Leider ist da nichts mehr frei.....

Schade drum :-)