Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.10.2025 13:18:36
Übersicht von Excel Konstanten
16.10.2025 11:48:44
Makro von Excel in Word aufrufen
16.10.2025 11:16:26
SUMMEWENN erweitern
16.10.2025 09:40:53
Mehrere Konferenzen ohne Personenüberschneidung terminieren
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1972to1976
1972to1976
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Koordinaten/Adresse mit Tabellenblattnamen

Forumthread: Koordinaten/Adresse mit Tabellenblattnamen

Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 13:53:49
Norman
Hallo,

ich benötige mal wieder Eure Hilfe.
Ich habe viele Blätter mit langen Listen. Um nicht jedes Blatt durchsuchen zu müssen, hätte ich gerne in einem separaten Tabellenblatt eine Abfrage. Diese Abfrage sollte mir die Koordinaten von den eingegebenen Daten ausspucken. Das bekomme ich soweit hin, aber nur mit der Angabe der Spalten-/Zeilennummer.
Ich benötige allerdings noch, in welchem Tabellenblatt ich die Daten finde.
Ich habe mal eine hofffentlich verständliche Datei erstellt.

https://www.herber.de/bbs/user/169140.xlsx

Jetzt schonmal vielen Dank für Eure Ideen!
LG
Norman
Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 15:00:05
schauan
Hallöchen,

Du hast doch schon die beiden Tabellen verarbeitet. Dann nutze das., z.B.

WENNFEHLER(ADRESSE((Daten=A3)*ZEILE(Daten[Daten]);(Daten=A3)*SPALTE(Daten[#Kopfzeilen]);4;1);"")

baust Du um, im Prinzip zu

=WENN(WENNFEHLER(ADRESSE((Daten=A3)*ZEILE(Daten[Daten]);(Daten=A3)*SPALTE(Daten[#Kopfzeilen]);4;1);"")="";"B";"A")
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 16:58:45
Piet
Hallo

mit der Tastenkombi Alt+F1 öffnest du den VBA Editor, dort musst du auf Projekt Ansicht gehen. Dann siehst du die Sheets.
Wenn du meine Beispieldatei öffnest, und den Ordner Module öffnest, kannst du die Module in deine Datei -rüberziehen-
Deine Datei muss danach aber bitte als .xlsx gespeichert werden, sonst gehen die Module wieder verloren!

Die Buttons kannst du aus meiner Datei durch Kopie und Paste mit Str+V in deine Datei kopieren.
Danach bitte meine Datei schliessen, und mit der rechten Maustaste die Makros neu zuweisen.
Einfach im Listenfeld den richtigen Makro Namen anklicken und mit Okay abklicken. Das ist alles.
Wenn du die Makros nicht neu zuweist öffnet Excel immer meine Beispieldatei, weil sie von da kopiert wurden!

mfg Piet
Anzeige
AW: Freut mich das es klappt oWt
01.05.2024 15:58:09
Piet
...
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 19:26:49
schauan
Kleine Korrektur - xlsm und nicht xlsx
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 15:44:01
Norman
Hi,

danke erstmal. Funktioniert leider nur teilweise. Wenn ich die Formel so bei Spalte "Tabellenblatt" einfüge, erzeugt er nach unten Ergebnisse. Wenn die Tabelle größer wird, dann erzeugt es einen Überlauf.
Ich habe ja bei "Wo zu finden" die Abfrage der Koordinaten mit 2 Formeln verbunden. Würde das auch mit einer gehen. In der richtigen Datei kommen auch noch weitere Tabellenblätter hinzu.

Mit Deiner Formel beziehst Du dich auf das Blatt A? Ich verstehe leider nicht, was die Wenn-Abfrage da macht?
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 16:06:21
schauan
Hallöchen,

Du holst mit "Formelteil1"
WENNFEHLER(ADRESSE((Daten=A3)*ZEILE(Daten[Daten]);(Daten=A3)*SPALTE(Daten[#Kopfzeilen]);4;1);"")
einen Wert aus der Tabelle oder es wird bei keinem Treffer, also einem Fehler, nix ausgegeben.

Mit WENN wird nun geprüft, ob da nix kommt oder etwas. Wenn davon auszugehen, das auf jeden Fall ein Treffer kommt, also entweder aus A oder B, kann man es relativ einfach prüfen. Kommt nix, muss es in der anderen Tabelle stehen, kommt was, dann in der einen.

Sollte es auch die Variante geben, dass beide Tabellen keinen Treffer hergeben, musst Du nicht auf nix sondern auf etwas verschieden von nix prüfen und wie in Deiner Originalformel beides.
Gleiches gilt, wenn Du 3 oder mehr Tabellen hast. Dann kann es, wenn es nicht in A steht, ja auch in C stehen und nicht nur in B. Im Beispiel waren es ja nur zwei ...

Also im Prinzip
=WENN(Formelteil1 > ""; "A";WENN(Formelteil2>"";"B";"")
usw. bei mehr Tabellen.

Du kannst auch mal überlegen, ob die Daten wirklich auf verschiedene Blätter und in verschiedene Tebellen müssen.
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 16:25:39
Norman
Danke für die super Erklärung. Das funktioniert super, unter der Annahme, dass ich die Werte wenn wahr immer so benenne, wie auch das Blatt heißt. Aber in echt sind die Tabellenblätter nach Städten benannt. Kann man den Namen anhand des Namen der Tabellenblätter erzeugen lassen?
Also statt A nun B u.s.w., z.Bsp. München, Osnabrück?

Ich kann die Daten leider nicht in eine Tabelle packen. Öffentlicher Dienst.....

LG
Norman
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 16:45:27
schauan
Hallöchen,

Du kannst z.B. eine Übersicht der Blätter erstellen, im Prinzip
- in Spalte A ein Bezug nach A1 (oder andere Zelle) des jeweiligen Blattes, z.B. =Tabelle2!A1
- in Spalte B dann =WECHSELN(TEXTVOR(FORMELTEXT(A1);"!");"=";"")
--> falls TEXTVOR nicht geht, könnte man auch mit FINDEN nach "!" suchen und LINKS nehmen oder TEIL (statt WECHSELN) oder was auch immer

und hier dann
=WENN(Formelteil1 > ""; b1;WENN(Formelteil2>"";b2;"")
usw.
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 18:00:15
Piet
Hallo

öffentlicher Dienst. Ist da VBA erlaubt? - Man könnte auch mit einem Makro nach den Daten suchen.

mfg Piet
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 18:14:42
Norman
Hi Piet,

VBA oder Makro könnte ich nutzen, habe ich aber leider keine Ahnung von.

@schauan:
ich habe mal eine Auflistung versucht. Siehe Testdatei. Aber so richtig bekomme ich das nicht hin.
Wenn noch mehr Tabellenblätter hinzukommen, wird meine Formel ganz schön lang oder?

https://www.herber.de/bbs/user/169149.xlsx
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 18:58:41
Norman
ich glaube so könnte es gehen. Habt Ihr eine Idee wie es kürzer geht?
Vielen Dank bis hierher.

https://www.herber.de/bbs/user/169150.xlsx

LG
Norman
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 20:15:54
schauan
Hallöchen,

also, ich würde die Formel etwas abwandeln:

=WENN(WENNFEHLER(@ZELLE("adresse";(INDEX(Daten[[Daten]:[Daten]];VERGLEICH(A3;Daten[[Daten]:[Daten]];0))));"")>"";$B$16;WENN(WENNFEHLER(@ZELLE("adresse";(INDEX(Daten1[[Daten]:[Daten]];VERGLEICH(A3;Daten1[[Daten]:[Daten]];0))));"")>"";$B$17;""))

Mit dieser Funktion würdest Du auch die Position komplett bekommen - hier mal in anderer Anordnung.
In Zeile 15 hätte ich die Werte von oben aus Spalte A, darunter dann pro Blatt eine Zeile mit dem entsprechenden Ergebnis.
Anschließend wird mit TEIL... usw. das Blatt extrahiert - auch wieder pro Blatt eine Zeile, und wegen der Mehrzeiligkeit mit TEXTVERKETTEN eine draus gemacht ...
Aus dieser "Nebenrechnung" könntest Du dann Deine Darstellung erstellen.

Kann man natürlich auch anders verformeln, so ist's aber vielleicht auch ganz gut für die Nachvollziehbarkeit. Mit VBA kann man sich natürlich den Formelwust sparen :-)
Die Formeln in A16 und 17 haben übrigens den Vorteil, dass man die Blätter ohne weitere Änderungen umbenennen kann.

[html]
 ABCDEF
15Bezug TabellenblattFormel11223456123
16DatenAA[169149.xlsx]AA!$A$2[169149.xlsx]AA!$A$3 [169149.xlsx]AA!$A$4
170B  [169149.xlsx]B!$C$9 
18      
19  AAAA AA
20    B 
21      
22  AAAABAA

ZelleFormel
A16=AA!A1
B16=TEIL(FORMELTEXT(A16);2;FINDEN("!";FORMELTEXT(A16))-2)
C16=WENNFEHLER(ZELLE("adresse";(INDEX(Daten[[Daten]:[Daten]];VERGLEICH(C$15;Daten[[Daten]:[Daten]];0))));"")
A17=B!A1
B17=TEIL(FORMELTEXT(A17);2;FINDEN("!";FORMELTEXT(A17))-2)
C17=WENNFEHLER(ZELLE("adresse";(INDEX(Daten1[[Daten]:[Daten]];VERGLEICH(C$15;Daten1[[Daten]:[Daten]];0))));"")
C19=WENNFEHLER(TEIL(LINKS(C16;FINDEN("!";C16)-1);FINDEN("]";C16)+1;99);"")
C20=WENNFEHLER(TEIL(LINKS(C17;FINDEN("!";C17)-1);FINDEN("]";C17)+1;99);"")
C22=TEXTVERKETTEN(;WAHR;C19:C20)
[/html]
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
29.04.2024 23:19:17
Piet
Hallo

anbei eine Beispieldatei mit VBA Lösung. Ganz schön ausgefüfftelt.
Es werden alle Daten in allen Tabellen gefunden, auch mehrfach Daten.
Mit zwei weiteren Button (Show+Close) kann man sich die Daten in der Tabelle ansehen.
Diese zusätzliche Funktion ist mit Formeln nicht zu erreichen! Das geht nur mit VBA.
https://www.herber.de/bbs/user/169154.xls

mfg Piet
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 14:13:53
Norman
Hallo schauan,

ich habe das jetzt mal so nachgebaut, aber leider passiert in C nichts. Was sollte da passieren.
Ich habe auch noch ein Problem gefunden: Wenn ich einen Wert in 2 Tabellenblättern einfüge, wird nur 1 Blatt angezeigt. Hier mal der Wert 999

Das mit den Hyperlinks klingt gut, aber das geht wohl weit über meine Kenntnisse.
https://www.herber.de/bbs/user/169165.xlsx

@Piet:
das mit den VBA funktioniert super. Wie bekomme ich die in eine andere Datei?

Vielen Dank für Euren Hirnschmalz - Ihr seid meine Helden 💪
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 16:04:48
schauan
Hallöchen,

also, mit der VBA-Lösung bist Du ja gut versorgt :-)
Die Formellösung bringt Dir auch schon mehrere Treffer. Ich habe das alles wieder ab Zeile 15 - hier mal nur mit der 1 als erstem Suchwert. Fehlten für Deinen Test natürlich die Formeln für das neue Blatt - hier jetzt in Zeile 21 und die Erweiterung vom TEXTVERKETTEN auf diese Zeile. Und es sieht mit einem Trennzeichen natürlich besser aus :-)

Die Werte aus Spalte A oben könntest Du ja per Formel in Zeile 15 ziehen - MTRANS kennst Du? Oder mit INDEX ....
Die Formeln aus Spalte C kannst Du nach rechts ziehen.

Bei eindeutigen Werte hätte man meinen Ansatz auch recht einfach neben Deiner Tabelle platzieren und optimieren können, Dann würde das mit den Hyperlinks auch funktionieren. Die Formel dazu wäre einfach - hier bei dem Tabellenauszug =HYPERLINK(C16;C19) In der Zelle erscheint AA - das Ergebnis von C19 - und Du kommst dann auf das Blatt AA in die Zelle A2 - das Ergebnis aus C16
Bei mehreren Treffern geht die Verlinkung nicht über nur eine Zelle.

 ABC
15Bezug TabellenblattFormel1
16DatenAA[169149.xlsx]AA!$A$2
170B 
180Tabelle1[169149.xlsx]Tabelle1!$B$5
19  AA
20   
21  Tabelle1
22Ergebnisse AA;Tabelle1

ZelleFormel
A16=AA!A1
B16=TEIL(FORMELTEXT(A16);2;FINDEN("!";FORMELTEXT(A16))-2)
C16=WENNFEHLER(ZELLE("adresse";(INDEX(Daten[[Daten]:[Daten]];VERGLEICH(C$15;Daten[[Daten]:[Daten]];0))));"")
A17=B!A1
B17=TEIL(FORMELTEXT(A17);2;FINDEN("!";FORMELTEXT(A17))-2)
C17=WENNFEHLER(ZELLE("adresse";(INDEX(Daten1[[Daten]:[Daten]];VERGLEICH(C$15;Daten1[[Daten]:[Daten]];0))));"")
A18=Tabelle1!A1
B18=TEIL(FORMELTEXT(A18);2;FINDEN("!";FORMELTEXT(A18))-2)
C18=WENNFEHLER(ZELLE("adresse";(INDEX(Tabelle3[[Daten]:[Daten]];VERGLEICH(C$15;Tabelle3[[Daten]:[Daten]];0))));"")
C19=WENNFEHLER(TEIL(LINKS(C16;FINDEN("!";C16)-1);FINDEN("]";C16)+1;99);"")
C20=WENNFEHLER(TEIL(LINKS(C17;FINDEN("!";C17)-1);FINDEN("]";C17)+1;99);"")
C21=WENNFEHLER(TEIL(LINKS(C18;FINDEN("!";C18)-1);FINDEN("]";C18)+1;99);"")
C22=TEXTVERKETTEN(";";WAHR;C19:C21)
Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 17:00:38
Piet
Hallo

Schauan - ich bewundere eure Fähigkeiten mit Formeln umzugehen, da fehlt mir jegliches Fachwissen.

mfg Piet
AW: Koordinaten/Adresse mit Tabellenblattnamen
01.05.2024 14:02:26
Norman
sorry, für die lange 'Antwortzeit.
Das mit den Formel funktioniert super. Nur wenn ich statt c15 mehrere suchen lasse (A3 - A13) dann bringt er nen Überlauf.

@Piet:
Das mit dem Makro in der neuen Datei funktioniert so halb. Ich konnte es übertragen. Mit dem zuweisen ging so lala :-)
Meist hat er mit Rechtsklick angezeigt - hierher kopieren. Das Tabellenblatt muss den selben Namen haben, wie das Blatt in der Testdatei? Also "Adresse"?
Wenn die Tabelle ,it den Daten in A1 beginnt, sucht er erst ab A3. Wenn es den zu suchenden Wert nicht gibt, bringt Excel eine Fehlermeldung- dann kann ich debuggen :-)
er findet auch Teilergebnisse. Wenn die Zahl 8200600040700005 ist und ich gebe im Suchfenster 200 ein, findet er die 8200600040700005 und weitere ähnlich aufgebaute.

Ansonsten funktioniert alles super.
Ich kann euch gar nicht genug danken

Anzeige
AW: Koordinaten/Adresse mit Tabellenblattnamen
01.05.2024 14:42:08
Norman
Schauan,

das mit dem Überlauf hat sich erledigt. Mit Mtrans hat alles super funktioniert.
Jetzt muss ich nur noch schauen, wie ich das ganze komprimiere und bei neuen Tabellenblättern muss ich das ganze erweitern.

Vielen Dank Euch beiden!!!!!!!!!!!!!
schönen Feiertag
AW: Koordinaten/Adresse mit Tabellenblattnamen
30.04.2024 06:26:11
schauan
Hallo Piet,
... Daten in Tabelle ansehen...
man könnte aber per Formel mit den berechneten Daten einen Hyperlink erzeugen und in die Tabelle gehen und auf jedem Blatt einen Link "zurück zur Übersicht", auch gerne per Formel.
@TE,
Man kann Formeln auch in Namen auslagern und dadurch selbige im Blatt kürzer machen.
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige