Office: (Office 2007) Excel 2003 nach Postleitzahleingabe Ort automatisch anzeigen

Hallo,
ich hab da mal wieder ne Frage, ich hatte vor einiger Zeit 01.05.06 zum Thema nach Eingabe der PLZ automatisch den passenden Ort anzeigen, wenn er schon in der Tabelle vorhanden ist von jinx mal eine sehr schöne Lösung bekommen siehe:


"kann man unter Zuhilfename von Funktionen (IstFehler und SVerweis) "basteln" oder per VBA durch Verwendung von Worksheet_Change-Ereignis und VLookup:
Tabelle1
A B
1 PLZ Ort
2 99999 Neunhausen
3 98765 Runtersausen
4 77777 Siebenhausen
5 11111 Einzigstadt
6 12345 Steil nach oben
7 11111 Einzigstadt
8 19191 Hügelighausen
9 kein Wert
10 kein Wert
11 kein Wert
Formeln der Tabelle
B7 : =WENN(ISTFEHLER(SVERWEIS(A7;$A$2:B6;2;FALSCH));"";SVERWEIS(A7;$A$2:B6;2;FALSCH))
B9 : =WENN(ISTFEHLER(SVERWEIS(A9;$A$2:B8;2;FALSCH));"kein Wert";SVERWEIS(A9;$A$2:B8;2;FALSCH))
B10 : =WENN(ISTFEHLER(SVERWEIS(A10;$A$2:B9;2;FALSCH));"kein Wert";SVERWEIS(A10;$A$2:B9;2;FALSCH))
B11 : =WENN(ISTFEHLER(SVERWEIS(A11;$A$2:B10;2;FALSCH));"kein Wert";SVERWEIS(A11;$A$2:B10;2;FALSCH))



__________________
cu
jinx

Nun hab ich das schon eine ganze Weile in Gebrauch und freue mich jedes mal wieder über die Zeitersparnis. Jetzt jedoch ist meine Chef auf die Idee gekommen, alle Adressdaten am Ende von A-Z zu sortieren! Und nun funktioniert das natürlich nicht mehr, da die Formel nur funzt wenn es so stehen bleibt wie eingegeben. Das Ergebnis ist ein Zirkelbezug und leere Zellen. Gibt es vielleicht, noch ne andere Lösung oder eine Möglichkeit, das Formelergebnis (Ort) sozusagen an die Zeile zu binden? 300-400 Adressen pro Woche von Hand alphabetisch vorsortieren ist nicht sehr praktikabel.
Vielen Dank vorab für die Bemühungen
Gruß gagag

:)

 

Hallo Hans Bärbel

,

ich habe Dein Beispiel nachgebaut und kann Dein Problem nicht nachvollziehen.
Der Nachbau sieht niemals so aus wie das Original.

 

Hallo Hajo,
danke für die Antwort. Wie jetzt bei Dir ist das Problem nicht? Heißt das ich hab nen Fehler eingebaut den ich bisher nicht bemerkt habe?Hm, wo ist der denn dann? bei mir sieht der Nachbau so aus:
2. Zeile

=WENN(ISTFEHLER(SVERWEIS(H4;$H$3:I3;2;FALSCH));"";SVERWEIS(H4;$H$3:I3;2;FALSCH))

3. Zeile
=WENN(ISTFEHLER(SVERWEIS(H5;$H$3:I4;2;FALSCH));"";SVERWEIS(H5;$H$3:I4;2;FALSCH))

u.s.w.

sorry übrigens wegen dem fehlendem Namen, bisher hat gagag immer gereicht.
Aber auch kein Problem.
Dann nochmal bitte um Hilfe und Danke
Alexandra

 

Hallo Alexanra,

Ja das würde ich schreiben. Da ich aber ablehne über das Internet auf fremde Rechner zu schauen. Kann ich dazu nichts genaues schreiben.
Ich verstehe Deinen SVerweis nicht. Ich habe eigentlich in jedem Sverweis die gleiche Matrix.
Zeile 3 ist die Matrix 1 Zeile groß
Zeile 4 ist die Matrix 2 Zeilen groß.

Irgendwo habe ich gelesen das es um Postleitzahlen geht. Bei mir ist die Matrix 25338 Zeilen lang.

 

hm, ich werds mir noch mal anschauen und meld mich dann noch mal.
Danke erst mal.
Gruß Alexandra

 

Hallo, ich muß doch noch mal fragen, ich bekomm es nicht hin. Ich kann an dem sverweis basteln und umstellen wie ich will irgendwas stimmt immer nicht. Entweder ich erhalte nach dem sortieren der Namen und Vornamen leere Zellen oder aber er zeigt alle Orte an, dann bekomm ich aber Fehlermeldungen wegen Zirkelbezug. Kann mir vielleicht noch mal jemand helfen wie der sverweis hier nun richtig sein muß? Ich kapier es nicht. Ich lade mal mein Beispiel mit hoch und wäre wirklich dankbar für nochmalige Hilfe.
Danke und liebe Grüße
Alexandra

 

Hallo Alexanra,

Der Dateiname sollte was mit dem Problem zu tun haben. Ich habe mir z.B. einen Ordner angelegt in dem ich alle Dateien aus dem Internet speichere. Bei Dateinamen wie Test., Mappe.., Beispiel wird eine vorhandene überschrieben. Ich lehne es ab solche Dateien runterzuladen.
Der Sinn und Zweck der Konstruktion ist mir nicht klar. Ich hätte für Sverweis irgendwo eine Matrix angelegt.

 

Hallo noch mal, also ich habe jetzt denk ich eine Lösung. Das Problem ist wohl das ich keine seperate Matrix anlegen kann sondern die beim ausfüllen der Datei auch erst gefüllt wird. Ich habe also in meiner Tabelle in Spalte H ab Zeile 3 die Postleitzahlen und in Spalte I den dazugehörigen Ort. Die Adressdatei ist als Mustervorlage vorhanden und wird jede Woche mit neuen Adressen ausgefüllt. Daher ist sie anfangs immer leer und somit die Matrix auch. Da ich mit Sverweis vorher nie was zu tun hatte fällt es mir wohl schwer das richtig zu machen.Meine erste Lösung sah so aus:
Spalte I Zeile 3: keine Formel, da eh überschrieben wird.
Spalte I Zeile 4 :=WENN(ISTFEHLER(SVERWEIS(H4;$H$3:I3;2;FALSCH));"";SVERWEIS(H4;$H$3:I3;2;FALSCH))
Zeile 5: :=WENN(ISTFEHLER(SVERWEIS(H5;$H$3:I4;2;FALSCH));"";SVERWEIS(H5;$H$3:I4;2;FALSCH)) u.s.w

Da hatte ich wie gesagt dann das Problem das nach der A-ZSortierung am Ende leere Felder entstanden sind.
Nun hab ich es so gemacht,
Spalte I:Zeile 3 =WENN(ISTFEHLER(SVERWEIS(H3;$H$3:$I$1000;2;FALSCH));" ";SVERWEIS(H3;$H$3:$I$1000;2;FALSCH))
Zeile : 4 =WENN(ISTFEHLER(SVERWEIS(H4;$H$3:$I$1000;2;FALSCH));" ";SVERWEIS(H4;$H$3:$I$1000;2;FALSCH))
und es funktioniert auch bis auf das ich nach dem Sortieren dann immer den Hinweis Zirkelverweis bekomme. Den hab ich jetzt mit dem anhaken von Iteration wegbekommen. Aber sicher ob das jetzt so richtig ist bin ich mir halt nicht.
Gruß Alex