Frage von Ninininino, 54

Excel mit SVERWEIS Warnung?

Hallo zusammen,

ich möchte eine Excel-Tabelle mit den folgenden Anforderungen erstellen und komme nicht weiter..

  1. In der 1. Mappe sind 250 Namen aufgelistet.
  2. In der 2. Mappe gebe ich weitere und neue Namen ein.
  3. Ich möchte, dass ich gewarnt werde, wenn ich einen Namen in Mappe 2 eintragen möchte, der in Mappe 1 schon aufegführt ist.

Ist das möglich und wenn ja wie?

Danke und viele Grüße N

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 9

Es sollte mit der Matrixformel zählenwenn funktionieren, in die Tabelle, in der die neuen Namen erfasst werden in der ersten Zeile folgendes eintragen:

=ZÄHLENWENN(Tabelle1!A:B;Tabelle2!A1:B1)

dann STRG+SHIFT+ENTER um die Eingabe zu beenden. Das erzeugt {} um die Formel (nicht die geschwungenen Klammern manuell eingeben, das klappt nicht)

Tabelle1 sind hier die bereits eingetragenen Hausverbote, Tabelle2 die neue Liste mit Gästen.

Die Formel kannst Du dann soweit wie notwendig nach unten ziehen bzw. kopieren. Wenn nicht nur 0 und 1 als direktes Ergebnis des zählenwenn angezeigt werden sollen, kannst Du das Ganze in eine wenn-Abfrage aufnehmen, z.B.
=WENN(ZÄHLENWENN(Tabelle4!A:B;Tabelle5!A2:B2)>0;"RAUSWERFEN";"")
aber auch hier unbedingt mit Strg+Shift+Enter abschließen, sonst wird ein falsches Ergebnis angezeigt. Das ist ehrlich gesagt die Schwachstelle dieser Lösung, da reicht es aus, dass man in der Formel ist und einfach "normal" die Zelle verlässt. Vielleicht ratsam diese Spalte dann mit einem Bearbeitungsschutz zu versehen.

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 30

Du kannst mit ZÄHLENWENN schauen, ob es den Namen schon im Namensbereich der anderen Mappe gibt.

Kommentar von Ninininino ,

Das hilft mir zumindest theoretisch weiter. Nur wie gehe dann praktisch vor?

Kommentar von Suboptimierer ,
  1. Öffne beide Mappen.
  2. Tippe =ZÄHLENWENN(
  3. Wähle mit der Maus den Bereich in der anderen Mappe aus.
  4. Vervollständige die Formel
Kommentar von Ninininino ,

hm, okay...?

ich merke, dass ich es genauer brauche. "Vervollständige die Formel", was ist damit gemeint? Wie erstelle ich nun hinter =ZÄHLENWENN( eine Ergänzung, damit ich gewarnt werde, sobald ich den Namen eingebe, weil er in Tabelle 1 schon vorhanden ist?

Kommentar von Suboptimierer ,

Im zweiten Parameter von ZÄHLENWENN musst du noch den Namen eingeben. Da ich nicht weiß, in welcher Zelle bei dir was steht, kann ich nicht konkreter werden.

Kommentar von Ninininino ,

Ich glaube mich eingangs undeutlich oder unglücklich ausgedrückt zu haben..

In Tabelle 1 sind 250 Namen aufgeführt. Spalte 1 Nachnamen und Spalte 2 Vornamen.

Nun möchte ich eine Tabelle 2 anlegen, aber keine der Namen doppelt anlegen. Ich möchte aber nicht vor jeder Eingabe recherchieren müssen, ob der Name schon in Tabelle 1 steht und wünsche mir daher eine Warnung sobald ich den Nachnamen eingegeben habe.

Konkret geht es um eine Liste mit Hausverboten. Es gibt 250 Leute die keinen Zutritt haben. Täglich kommen ca 100 Besucher. Keiner darf als Besucher rein, wenn er schon Hausverbot hat. Somit möchte ich in der Liste, in der ich die Besucher eintrage, eine Möglichkeit gewarnt zu werden, wenn ich jemanden eintragen möchte, der schon ein Hausverbot hat.

Hoffe mich nun verständlicher ausgedrückt zu haben :)

Expertenantwort
von Iamiam, Community-Experte für Excel, 19

Wenn der Familienname reicht, gehts relativ einfach:schreibe

=Zählenwenn(Tabelle1!A:A;A2) i*wo neben Deine Einträge in Zeile 2. Formatiere die Formelzelle Benutzerdefiniert so:

0;[rot]-0;-;@ und in Schrift zB Arial Rounded MT bold, zusätzlich Fett.

Zieh die so formatierte Formel  runter über alle zu erwartenden Einträge.

Bei einem Doppel des Erstnamens (in A) erscheint eine dicke 1, ansonsten ein leicht unterscheidbares -

Willst du Fam- und Vornamen verknüpft haben, wirds ein wenig komplizierter: Entweder Du kreierst eine Hilfsspalte (zB in Spalte K wie Kombi) und schreibst da rein = A2&B2, (wenn in Tabelle1, ansonsten =Tabelle1!A2&Tabelle2!B2,

dann (Fall2: lange extern-Formel in der Hilfsspalte) hilft analoges Vorgehen mit dieser Formel:

=Zählenwenn(K:K;A2&B2)

oder Du schreibst folgende Formel in eine fixierte ("eingefrorene") Titelzeile (aber nicht die Eintragsspalten, sonst gäbs Zirkelbezug) des Neu-Eintrags-Blattes:

=Wennfehler(Summenprodukt((Tabelle1!A:A=A:A)*(Tabelle1!B:B=B:B)*Zeile(Tabelle1!A:A));"✓")

dann erhältst Du bei einem schon vorhandenen Eintrag die Zeile des Eintrags in der fixierten Zelle. Du musst beim ersten Doppel reagierenn ansonsten erhältst du die Zeilensumme zweier(mehrerer) Doppeleinträge, deshalb diese eine Zelle ebenso auffällig formatieren wie oben. (Letzteres war Trockenübung, sollte aber funktionieren)

Stören könnten evtl gleichartige Spaltenüberschriften, dagegen hilft einfaches anhängen eines Leerzeichens an einen der beiden Einträge, aber natürlich nur in einem Blatt!

Kommentar von Iamiam ,

Halt, zurück, die Summenprodukt-Formel wird nicht funktionieren, da die Einträge in beiden Blättern ja nicht in der gleichen Zeile stehen, hab jetzt aber für eine Alternative keine Zeit mehr, nimm die Variante mit der Hildsspalte!

Kommentar von Ninininino ,

OK, das sieht doch schonmal sehr interessant aus. Habe das mit der Hilfspalte

=Wennfehler(Summenprodukt((Tabelle1!A:A=A:A)*(Tabelle1!B:B=B:B)*Zeile(Tabelle1!A:A));"✓")

versucht. Funktioniert aber leider nicht. Und ich bin zu unerfahren den Fehler zu finden.

Kommentar von Iamiam ,

mit Hilfspalte K:K funktioniert die Formel
=Zählenwenn(K:K;A2&B2)

Die andere Formel müsste ich grundlegend neu aufbauen, kann ich in nächster Zeit nicht, da bräuchte ich längere Zeit am Stück für konzentriertes Denken.

Kommentar von Iamiam ,

Jetzt ist mir doch noch was Kurzes eingefallen, allerdings eine Matrixformel i'wo in Zeile2: Hat gegenüber zählenwenn nur den Vorteil, dass die Zeile des anderen Eintrags gleich mitgeliefert wird (diesmal hab ichs ausprobiert!):

{=Wennfehler(VERGLEICH(A2&B2;Tabelle1!A:A&Tabelle1!B:B;0);"✓")}

Die Formel runterziehen über alle infragekommenden Zeilen

Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!!!). Die {Klammern} entstehen dann automatisch.

Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!  - - -
Noch ein Tipp: Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen (oder rückgängig machen), Enter würde das gezeigte Ergebnis festschreiben! Vorläufig kurze Bereiche festlegen! (also nicht A:A, sondern zB A$1:A$22 etc. Zum Erweitern dann mindestens 2 Zellen markieren, Strg+h, A$1:A$22 ersetzen durch A:A) --

Kommentar von Iamiam ,

Das Gleiche ohne Matrixformeleingabe sollte das hier leisten (wieder i'wo in Zeile2 und dann runterziehen):

=SUMMENPRODUKT((A2=Tabelle1!A:A)*(B2=Tabelle1!B:B)*ZEILE(A:A))

Erklärung der Formel: Erste Klammer liefert nur bei Übereinstimmung ein Wahr, ebenso die zweite. WAHR*WAHR=1, alles andere 0. Zeile() multipliziert die 1 mit der Zeilennummer des übereinstimmenden Eintrags.

Hier bekommst Du aber anstelle des Hakens nur eine 0 (weil Summenprodukt beim Nichtvorhandensein keinen Fehler liefert), wenn Dir daran liegt, kannst Du aber auch den erhalten, wenn Du diese BENUTZERDEFINIERTE ZELLFORMATIERUNG einstellst:

[Strg+<1>, Karte Zahlen, Kategorie Benutzerdefiniert)]

[Farbe7]0;[rot]-0;[Farbe10]"";@

Bei einer (glatten) Null erscheint dann ein dunkelgrüner Haken, beim Auftauchen eines "Bekannten" dessen magenta-farbene Zeilenzahl in Tabelle1. 

[grün] als Standard-hellgrün ginge auch, entspricht [Farbe4]

kurze Erklärung des Formats: erster Abschnitt = positive Zahl, 2.negative, 3.Null, 4.Text in eingestellter Zellfarbe[ohne Farbvorgabe=eingestellte Zellfarbe], das @ heißt Übernahme des vorhandenen Textes. Der letzte Abschnitt kann samt Semicolon weggelassen werden.

Antwort
von Ingoberta, 32

Zunächst mal eine Frage: Handelt es sich 

a) um zwei (Arbeits-)Mappen (also getrennte Dateien) oder

 b) um eine Date (Mappe)i mit zwei Tabellenblättern? 

Wenn es zwei unterschiedliche Mappen sind, dann wirst du vermutlich mit einem vba-Makro/Lösung am weitesten kommen.Wenn es b) ist, dann kann man über eine Lösung wie einen Verweis nachdenken.

Kommentar von Ninininino ,

Es handelt sich um eine Datei.

Kommentar von Iamiam ,

das ist leider immer noch nicht eoindeutig:

Handelt es sich um eine Datei (mit zwei Blättern)

oder um jeweils eine Datei

Kommentar von Ninininino ,

Tschuldige, aber ich dachte es sie eindeutig. Es handelt sich um eine Datei mit zwei Tabellenblättern.

Kommentar von Ingoberta ,

Also wenn das eine "lebende" Tabelle sein soll, die regelmäßig in Bearbeitung ist solltest du dich vielleicht wirklich mit vba auseinandersetzen. 

Aber ich habe auch noch eine andere schnelle Möglichkeit gefunden. Schau dir mal diese Seite an. 

http://fasteasy.at/tippshop/index.php?mp=1&show=excel&id=1&show2=for...

Kommentar von Iamiam ,

Wenn Groß/klein eine Rolle spielt oder ß<>ss erkannt werden soll, ja. Ansonsten ist Zählenwenn ohne Matrix-Eingabe vorzuziehen. Ausserdem geht es ja um 2 zu verknüpfende Spalten(Vor-und Zuname) auf beiden Blättern. Da Matrixformeln m.W. keine Textfunktionen unterstützen (wenn doch, wäre das neu) braucht man auch hier eine Hilfsspalte.

eine  Matrixformel schreibe ich grade in meiner Antwort.

Antwort
von Ninininino, 3

Vielen Dank an alle!

Habe das Problem gelöst und jeder hatte seinen Anteil.

Danke!

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten