Zelladresse ermitteln mit Sverweis

[MundM]

Hallo Zusammen,

ich komme gerade leider nicht weiter.

folgende Formel hab ich eingegeben und diese gibt mir auch den gewünschten Werte wieder

=SVERWEIS(PLAN_VORLAGE!$B9;INDIREKT("DP!"&SVERWEIS($C$2;DP!$A$7:$N$627;2;FALSCH));2;FALSCH)

Jetzt würde ich gerne diese Formel mit =Zelle("adresse") kombinieren, damit ich nicht den Werte aus der Zelle erhalte, sondern die Adresse.

Ist dies möglich?

Vielen Dank schon mal im Voraus.

LG

MundM

[Gastlich]

Hallo,

eine Datei wäre nett.

[MundM]

Hi,

anbei die Datei.

die genannte Formel ist zum Test in Zelle(P4) eingegeben.

Danke

MundM

[snb]

PHP-Code:

="$B$" & match($C$2;DP!$A$1:$A$627;0) 


[MundM]

Hallo Snb,

danke für deine Antwort.

Jedoch erhalte ich damit nicht die Zelladresse die bei meiner Formel [TEST.xlsm]DP!$C$10 sein müsste.

Oder mache ich was falsch?

Grüße

MundM

[HKindler]

Hi,

ich hänge jetzt hier mal den Fiesling raus:

Hier eine Tabelle mit geschützten Blättern hoch zu laden, ist nicht gerade nett den Helfern gegenüber. Das erhöht die Motivation zu helfen unheimlich.

Im Code-Bereich von DieseArbeitsmappe haben Routinen wie Sub TageFiltern() nichts verloren.

Code sollte man vernünftig einrücken. Dann braucht man auch keine Leerzeilen darin.

Greife niemals nicht auf ActiveWorkbook zu. Du kannst dir nie sicher sein, ob du damit wirklich das richtige erwischst. Es mag in 99,999% aller Fälle gut gehen. Aber irgendwann geht es schief und du bearbeitest mit dem Makro die falsche Datei. 

Dann widerspricht der Aufbau deiner Datei jeglichem Prinzip der Datenerfassung. Eine ordentlich "Datenbank" hat pro Zeile genau einen (vollständigen) Datensatz. In deinem Fall wäre DP eine Tabelle mit den Spalten Ort, Datum, Dienst, Name, Öffnen, Schließen (wobei Öffnen und Schließen vernünftigerweise aus einer anderen Tabelle kommen, in der diese an den Ort gekoppelt sind). Damit lässt sich dann vernünftig arbeiten und man braucht keine verdeckten Inhalte, die den Aufbau der Datei beschreiben und das unsägliche INDIREKT und völlig verschachtelte Formeln brauchen. Dann braucht man auch so etwas wie ZELLE("Adresse") nicht!

Nach diesem rumgemotze: Wie kommst du jetzt an die Zelladresse von "Müller"? Ganz einfach :

PHP-Code:

=LET(ab;SVERWEIS($C$2;DP!$A$7:$N$627;2;FALSCH);am;"DP!"&ab;zm;VERGLEICH(PLAN_VORLAGE!$B9;INDEX(INDIREKT(am);0;1);FALSCH);zb;ZEILE(INDEX(INDIREKT(ab);1;1));z;zm-1+zb;ADRESSE(z;3;;;"DP")) 


Falls dein Excel noch kein LET kennt, musst du halt über Hilfszellen gehen oder die Formel weiter aufblähen.

Hatte ich schon erwähnt, dass dein Tabellenaufbau gelinde gesagt Scheiße ist?

[MundM]

Hi,

danke für deine Meinung zur Struktur der Datei..... auch wenn diese nicht erfragt wurde. (sorry aber ist halt so)

Die Datei ist nun mal so wie sie ist..... und ob sie einem anderen gefällt oder nicht spielt keine Rolle.

Es gibt Gründe für das "widersprechende Prinzip der Datenerfassung".
Die Struktur, mag sein, dass diese nicht so ist wie sie normalerweise sein sollte.

Doch meine Frage bezog sich lediglich darauf, ob sich mit einem Sverweis eine Zelladresse auslesen lässt und wenn ja wie ich diese anhand der Datei aussehen müsste.

Und nein! "LET" kann die Excelversion die ich verwenden muss leider nicht.

Aber trotzdem Danke

MundM

[schauan]

Hallöchen,

mit der Funktion VERGLEICH kannst Du die Zeile oder Spalte herausfinden. Die beiden Ergebnisse bzw. Funktionsaufrufe kannst Du in der Funktion ADRESSE verwenden.

[HKindler]

Hi,

ich verstehe ja, dass du leicht angesäuert bist, aber ich habe nur meine Meinung zum Ausdruck gebracht. Du hättest die Probleme nicht, wenn du eine ordentliche Struktur hättest. Das wollte ich damit zum Ausdruck bringen. Bei einer ordentlichen Struktur muss man auch nicht die Adresse einer Zelle, die über SVerweis gefunden wurde, kennen. Ist nun mal so.

Und glaub mir, mit einer ordentlichen Datenerfassung kannst du auch locker dein gewohntes Erscheinungsbild beibehalten. Die bisherigen Blätter werden dann per Formel aus der "Datenbank" gefüllt. Mal ganz davon abgesehen, dass deine Tabellenstruktur extrem pflegebedürftig und fehleranfällig ist, sollte sich einmal etwas daran ändern. Z.B. wenn du Zeilen oder Spalten einfügen musst, musst du daran denken, die Zellen DP!B7, DP!B30, DP!B52, ... anzupassen. Weißt du das in zwei Jahren noch?

Aber genug mit den Anmerkungen. Die Erfahrung zeigt: du wirst das so oder so nicht ändern.

Die Formel mit LET lässt sich übrigens leicht umstellen. LET definiert im Grunde nur Variablen, die im weiteren Verlauf verwendet werden können: =LET(Variable1;Wert1;Variable2;Wert2;...;FormelMitDenVariablen) Wobei bei der Definition von WertX bereits definierte Variablen schon verwendet werden können. Wenn du das also in einem alten Excel umsetzen willst, dann verwende entweder für jede Variable eine Hilfszelle mit dem Wert und diese Hilfszelle dann in der Formel, oder setz die Variablen händisch in die Formel ein. Dann solltest du so etwas erhalten:

PHP-Code:

=ADRESSE(VERGLEICH(PLAN_VORLAGE!$B9;INDEX(INDIREKT("DP!"&SVERWEIS($C$2;DP!$A$7:$N$627;2;FALSCH));0;1);FALSCH)-1+ZEILE(INDEX(INDIREKT(SVERWEIS($C$2;DP!$A$7:$N$627;2;FALSCH));1;1));3;;;"DP") 


Ach eines noch: Habe ich schon erwähnt, dass man eine Tabelle völlig anders aufbaut wenn man damit vernünftig arbeiten will? 

[Fred11]

Hallo MundM,

SVERWEIS kann (direkt) keine Zelladresse zurückliefern, sondern nur einen Wert aus einer Matrix.

Mit der Funktion VERGLEICH kannst du die Zeilennummer von Müller  ermitteln (siehe Antworten  #2 und #8)
zum Beispiel so:
=VERGLEICH($C$2;DP!A:A;0)+3

oder so:
=VERGLEICH($B9;INDEX(DP!B:B;VERGLEICH($C$2;DP!A:A;0)+2):INDEX(DP!B:B;VERGLEICH($C$2;DP!A:A;0)+22);0)+1+VERGLEICH($C$2;DP!A:A;0)
(diese Formel ließe sich mit LET noch deutlich verkürzen)

Und aus der Zeilennummer kannst du dir, sofern notwendig, dann die komplette Zelladresse basteln (z.B. mit der Funktion ADRESSE - siehe #8)

Oder du trägst in B9 auf dem Tabellenblatt 'PLAN_VORLAGE' die folgende Formel ein:
=WENN($A9>0;INDEX(DP!B:B;VERGLEICH($C$2;DP!A:A;0)+2+ZEILE(A2)/2);INDEX(DP!C:C;VERGLEICH($C$2;DP!A:A;0)+2+ZEILE(A2)/2))&""
und ziehst sie nach unten bis B48

Statt mit INDIREXT kannst du auch mit der Funktion INDEX arbeiten; INDEX kann nämlich nicht nur einen bestimmten Wert aus einer Matrix zurückgeben, sondern auch einen Zellbezug (so angewendet im zweiten Formelvorschlag)
Studiere dazu mal das hier:
https://www.online-excel.de/excel/singsel.php?f=180

Oder mit BEREICH.VERCHSIEBEN ….

Es gilt halt:
Je ungünstiger die Datenbasis, desto länger, komplexer, aufwendiger, fehleranfälliger…. die Formeln.
(Dem Hinweis von Helmut bzgl. der Änderungsfreundlichkeit deines Tebellenkonstruktes stimme ich voll und ganz zu)

Gruß
Fred