Erstellt man eine Datenüberprüfung (beispielsweise nur ganze Zahlen) und trägt etwas ein, dass dieser Regel widerspricht (beispielsweise einen Text), wird der Text zwar eingetragen und die Fehlermeldungen in Excel angezeigt, jedoch wird der Wert nicht übernommen. Die Datenüberprüfung muss abgebrochen werden und der Spuk verschwindet wieder:
Das bedeutet: kopiert (oder verschiebt) man das Diagramm auf ein anderes Tabellenblatt ist es resistent gegenüber der Verschiebeaktion und greift auf die gleichen Daten zu.
Erstellt man eine Datenüberprüfung mit Liste und einer Quelle, lautet der Bereich ohne Blattname
Kopiert man die Datenüberprüfung, verweist der Bereich auf das andere, aktuelle Tabellenblatt. Ist meistens nicht gewollt!
Selbst das Eintragen des Blattnames nützt nichts – der Tabellenblattname wird aktualisiert!
ich hoffe, dass es dir so weit gut geht und frage heute nach, ob du mir eine EXCEL-Lsg verkaufen kannst, die du bestimmt schon zigmal implementiert hast:
Thema: Dropdownliste mit Datenquelle
Bei der der Datenbereich gefüllt ist, aber z.B. keine Daten in den letzten 10 Zeilen hat.
Effekt, den du kennst:
Unter dem letzten Eintrag existieren viele, viele leere Zeilen. Man scheint das Problem der leeren Zellen beheben zu können, in dem man die Zellfunktionalität BEREICH.VERSCHIEBEN nutzt.
Gestern in der Excelschulung fragte eine Teilnehmerin, ob man die Daten für die Datenüberprüfung auch auslagern können. So, dass man von jeder Excelmappe darauf zugreifen könne und sie verwenden könne. Das geht leider nicht, war meine Antwort. Die Daten müssen innerhalb der Mappe liegen.
Sie war enttäuscht.
Aber dann zeigte ich ihr, dass man Daten als benutzerdefinierte Liste (in den Optionen) ablegen kann. Diese werden in Excel gespeichert – nicht in einer Arbeitsmappe. Und damit kann man schnell die gewünschte Liste erzeugen, die man in jeder Datei verwenden kann. Beispielsweise für die Datenüberprüfung.
Eine Teilnehmerin in der Excelschulung fragt, wie man Dropdownlisten der Datenüberprüfung konfigurieren kann, damit eine Mehrfachauswahl möglich ist.
Ich erkläre ihr, dass das nicht möglich sei – für Mehrfachauswahl benötige sie ein Steuerelement. Und/oder VBA-Programmiercode.
Als ich nachfrage, warum sie mehrere Elemente auswählen möchte und was mit den ausgewählten Daten passiere, kommt sie ins Schleudern. Also vielleicht doch zwei oder drei Spalten mit solchen Datenüberprüfungslisten.
ich arbeite gerade an einer Excel-Datei (zum Üben).
In der Spalte A ab A2 bis A31 habe ich eine Liste hinterlegt, die sich auf die Nachnamen im Arbeitsblatt Belegung bezieht.
Nun würde ich gern die Matrix im Arbeitsblatt Belegung in eine formatierte Tabelle umwandeln. (Das ist kein Problem.) Nachdem ich dies getan, möchte ich gern folgendes erreichen:
wenn ich in die formatierte Tabelle einen neuen Namen einfüge, alle Nachnamen nochmals alphabetisch sortiere, soll der neu eingetragene Name in der Liste (Spalte A ab A2 bis A31) auftauchen.
Ist das möglich? Wenn ja, wie?
Vielen Dank im Voraus für Ihre Hilfe.
Mit freundlichen Grüßen
####
Hallo Herr F.,
der Trick ist, dass Sie dem Bereich der intelligenten Tabelle einen Namen geben müssen. Dann können Sie den Namen in der Dropdownliste verwenden.
Wird die Liste erweitert (oder verringert), passt sich die Liste an.
Mit PowerQuery wurde eine Abfrage erstellt. Diese enthält eine Liste. Wie kann man eine Dropdownliste mit einer Datenüberprüfung auf diese Liste erstellen, ohne dass die Daten nach Excel geladen werden?
Nicht aufgepasst. In einer Liste existiert eine Datenüberprüfung. Die Quelle der Liste ist jedoch keine „feste Liste“, sondern liegt in einem Zellbereich:
Per VBA kopiere ich diese Liste auf ein anderes Tabellenblatt und wundere mich, warum die Datenüberprüfung nicht mehr funktioniert. Die Antwort: der Verweis wird auf das neue Blatt gesetzt …
Eine Schulung. Ein Grafikprogramm. Und es kommt – wie so häufig – die Frage auf: „Ach, wo ich mich gerade mit Ihnen unterhalte – ich habe da mal eine Excelfrage. Wir haben vom Kunden eine Datei erhalten, da ist ein Schutz drauf. Können Sie den wegmachen?“
Ich lasse mir die Datei zeigen:
Und dann: sehen Sie, wenn ich nun den Tippfehler in Zelle B11 ändern will, dann darf ich das nicht. Da ist ein Schutz drauf. Sie zeigt es mir:
Ich lache und bitte sie in der Datenüberprüfung nachzusehen. Wir werden fündig: die Werte werden von einem ausgeblendeten Tabellenblatt „geholt“:
Das Tabellenblatt „Daten“ kann man einblenden – tatsächlich: dort finden sich in A2:A4 die Daten:
Wir ändern den Tippfehler, blenden das Tabellenblatt wieder aus. Natürlich ist sie traurig, dass die Fehler auf dem anderen Blatt nicht sofort geändert wurden. Aber mit dem Befehl „Ersetzen“ funktioniert das schnell.
Was mich DANN sehr amüsiert hat, war ihre Erzählung (sie zeigte mit den Chatverlauf in teams), dass sie bei der IT Ihrer Firma nachfragte, ob diese nicht die Ursache des Problems kenne. Sie hatte gebeten, man möge die Originaldatei ohne Schutz kommen lassen. Oder: die Datei noch einmal erstellen!?! Ich war schon sehr irritiert und habe einen Grundkurs Excel in Ihrer Firma angeboten.
Excelschulung. Eine Teilnehmerin möchte eine Dropdownliste durch eine Datenüberprüfung haben, in der Smileys angezeigt werden. Ich überlege: in der Schriftart Wingdings gibt es drei Smileys. Man kann sie über Einfügen / Symbol einfügen, oder indem man die Buchstaben J, K und L mit der Schriftart Wingdings formatiert.
Fügt man eine Datenüberprüfung ein, werden jedoch nur die drei Buchstaben dargestellt – auch das Formatieren der Zelle nutzt nichts:
Ich überlege: vielleicht werde ich in den nicht druckbaren Zeichen fündig, die man mit [ALT] + [1], [ALT] + [2], … erzeugen kann. Jedoch finden sich nur zwei Smileys hinter den Nummern 1 und 2:
Mourad hat eine Idee und hilft mir. Im Unicode-Zeichensatz (beispielsweise der Schriftart Calibri) finden sich Smileys:
Wenn ich Word die Unicode-Zahl eintrage (beispielsweise U+1F600) und anschließend [ALT] + [C] erhalte ich das dahinter liegende Symbol:
In Excel funktioniert das leider nicht. Muss ich die Zeichen von Word nach Excel kopieren? Quatsch, meint Mourad – du kannst sie doch direkt von der Internetseite nach Excel kopieren:
Oder mit der Funktion UNIZEICHEN umwandeln, also beispielsweise:
=UNIZEICHEN(128512)
Klappt! Und so können wir eine lustige Auswahlliste erstellen:
Auch mein Add-In [Strg] + [Q] funktioniert:
Eine großes Dankeschön an Mourad Louha für die Hilfe.
Excelschulung. Wir erstellen ein Eingabeformular. In einer Zelle steht eine Prozentzahl – sie wird auf Eingabewerte zwischen 0% und 10% festgelegt – also in der Datenüberprüfung werden die Grenzwerte 0 und 0,1 eingetragen:
Ein Teilnehmer probiert aus und trägt die Zahl 25 ein. Das Prozentzeichen bleibt stehen:
Er erhält – wie erwartet – einen Fehlerwert. Die Zahl MIT Prozenzwert werden markiert:
Der Teilnehmer versucht es ein zweites Mal – diesmal trägt er 7,5 ein:
Da er das Prozentzeichen zuvor markiert hatte, wurde es gelöscht – 7,5 ist nun 7,5 und nicht 7,5%. Also erfolgt wieder eine Fehlermeldung!
Hum. Das heißt: man muss schon ganz genau hinschauen, was man einträgt und welche Mechanismen Excel verwendet …
Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.
Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.
Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet). Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.
Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung. Interessiert? Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].
Ich hätte da nochmal eine Frage zu den Matrixfunktionen.
Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der
auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den
Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn
zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln.
Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich
nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine
Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen
benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und
die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in
einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben,
innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die
Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher
auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
Mourad Louha hat darauf hingewiesen, dass Namen eine maximale Länge von 255 Zeichen haben dürfen. Während man Namen mit einer Länge bis zu 244 Zeichen problemlos verwenden kann, produziert Excel bei einer Buchstabenstabenanzahl von 255 Zeichen Probleme. 256 Zeichen und mehr ist nicht zugelassen.
Test?
Schnell einen langen Text erzeugen – Kleist liefert sehr viele. Die Funktion LÄNGE ermittelt die Anzahl der Zeichen. Und diese kann man in den Namensmanager kopieren und so die Namen erzeugen:
Ein Name mit einer Länge von 273 funktioniert nicht.
Und dann: die Auswahl über das Namensfeld funktioniert nicht:
Die Datenüberprüfung kann den 255er-Namen mit [F3] nicht verwenden:
Und auch nicht die Diagramme:
Also: Finger weg von einem Namen mit 255 Buchstaben! Beschränken wir uns auf Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch – das sind 58 Buchstaben.
Hallo Rene, merci für den Screen und deinen Text. Kurz der Hintergrund zu meiner Frage: Ein Kunde fragte vorletzte Woche: „Wenn er das Dropdown-Dreieck sehe, so würde er dann mindestens zwei Optionen erwarten. x und ein leerer Eintrag.“ Vermutlich nehme ich deine Lösung: Somit nur das „X“ auswählen, ansonsten muss er den Eintrag eben löschen. Es wäre überaus nett gewesen, wenn das ganze über das Zellenfeature gegangen wäre … … ich wollte ggf. auf den Name verzichten. Dann sind wir ‚mal gespannt, was uns nächste Woche so erwartet. Ich wünsche dir also einen guten Wochenstart Jürgen
Hallo Jürgen, der Kunde ist König! Meiner gibt sich mit DIESER Lösung zufrieden.
Man könnte aber auch eine Auswahl machen. Datenüberprüfung: keine Angabe;x
Und dann mit einer Bedingten Formatierung das „keine Angabe“ ausblenden (benutzerdefiniertes Zahlenformat: ;;;
sei gegrüßt in den Abendstunden. Ich hoffe, dass es dir soweit gut geht und dein EXCEL-Kurs für Einsteiger bald abgehalten werden kann.
Mit dieser Mail kommt nur eine fachliche Frage zu EXCEL:
Ich realisiere auf einer Zelle ein Dropdown mit einer Datenauswahl bzw. Überprüfung. Als Datengrundlage nutze ich ein x und ein Leerzeichen und möchte auf die Verwndung eines Namens verzichten. D.h.: Im WWW finde ich eine Lösung (http://www.office-loesung.de/ftopic332490_0_0_asc.php) über die Schritte:
Einstellungen / Listung / Quelle
den
Vorschlag:
‚; x
Gibt es noch andere Lösungen?
Dankeschön & Gruß Jürgen
Hallo Jürgen
willst du wirklich als Auswahl ein
Leerzeichen und ein x haben?
Ich habe so etwas schon gemacht – nur mit x. Wenn der Anwender es nicht haben will, kann man es löschen (Taste [entf]).
Wie auf office-loesung beschrieben – elegant
ist das ‚ nicht gerade.
Alternative: Namen – das willst du nicht.
Alternative: Wähle zwei Zellen aus. Im
unteren steht ein „x“, die obere Zelle ist leer. Oder mit einem Leerzeichen
gefüllt.
Da bin ich über meine eigenen Füße gestolpert. Man (ich!) sollte sich (mir!) merken, was man (ich) gemacht habe.
Ich habe ein Formular für einen Kunden erstellt. Die Prozessnummer soll nun von fünf Zeichen auf zwölf erweitert werden:
Also schnell die Notiz (den Kommentar) anpassen. Ich stutze! Ich finde sie nicht. Weder über die Registerkarte „Überprüfen“:
noch über die Suche – in der Registerkarte „Start“:
Mit dämmert es: kein rotes Eck – vielleicht ist das gar keine Notiz! Richtig: es handelt sich um die Eingabemeldung einer Datenüberprüfung. Und DORT kann ich sie auch ändern – ebenso wie die Datenüberprüfung selbst:
Wenn einen Computer mit Windows 10 und Office 365 *pardon* Microsoft 365 hat, hat keine Langeweile. Jeden Tag etwas Neues. Ich sehe ein neues Symbol in meiner Taskleiste:
Wenn ich mit der Maus darüberfahre
wird mit erklärt, dass Microsoft Edge nun so aussieht. Aha. Lustig – mal ein neues Logo. Warum nicht jeden Tag ein neues? Dann ist die Welt nicht mehr so langweilig …
Und so schreibt mir Dominic:
Hallo Rene,
mir ist heute ein merkwürdiges Verhalten bei der
Datenüberprüfung / Dropdown aufgefallen, hier hat sich scheinbar etwas in Excel
verändert.
Ich habe eine Datei mit mehreren Tabellenblättern. In einer Spalte davon sind über Datenüberprüfung à Wert aus Liste als Dropdown nur Werte aus einer Liste zugelassen. Diese Liste steht auf einem gesonderten Tabellenblatt.
Jetzt möchte ich aus jedem der Tabellenblätter eine neue Datei machen. Das Tabellenblatt „Dropdown“ soll dabei stets mitkopiert werden. Ich wähle also mit gedrückter Strg-Taste die Tabellenblätter „Region1“ und „Dropdown“ -> Rechtsklick -> Verschieben oder kopieren -> Häkchen bei „Kopie erstellen“ und (neue Arbeitsmappe).
Mein Problem: Jetzt ist die Datengültigkeit in der Spalte
„Länderkürzel“ weg. Das war doch vor kurzem noch anders. Ich habe das für einen
Kunden ursprünglich per Makro gemacht, also das Erstellen der neuen Dateien und
dabei ist die Datengültigkeit stets erhalten geblieben. Plötzlich war sie immer
weg.
Hast du das schon mal gesehen? Und hast du vielleicht irgendwo eine ältere Excel-Version, mit der sich nachprüfen lässt, ob das vor kurzem wirklich anders war? Habe Version 2004
Stimmt, Dominic,
ich habe mich vor Kurzem noch mit dem Problem rumgeschlagen,
wenn ich nur das Blatt „Region1“ per VBA in eine neue Datei kopiere, verweist
die Datenüberprüfung auf das Blatt der alten Datei.
Das Perfide: es wird nicht angezeigt.
Wenn ich die neue Datei speichere, schließe und wieder
öffne, dann kommt die Meldung „Verknüpfung zu anderer Datei“. Und wenn ich die
Datenüberprüfung ändere, wird in „Information“ mir immer angezeigt, dass eine
Verknüpfung da ist. Man musste erneut speichern, schließen und öffnen, damit
dieser Hinweis weg war.
Ich habe darüber geschrieben (Dez. 2019):
Und: ich habe hier einen Laptop 2001 – DAMIT ging es – bei meiner aktuellen 2004er und 2005er-Maschine passiert das Gleiche wie bei dir.
Lustig?!? danke an Dominic Dauphin für den Hinweis.
Moin zusammen, habe eine Excel geerbt und möchte diese für einen schnelleren Start bereinigen. Es waren ne Menge Datenverbindungen drin, die ich bis auf eine auflösen konnte. Eine letze weigert sich hartnäckig, von mir gefunden zu werden. Habe bisher mit Strg+F alle (bis auf die eine) gefunden. Auch die Buttons habe ich untersucht, die Makros aus externen Datenquellen aufgerufen haben. Nun habe ich noch „Daten mit Hilfe einer Verbindung abrufen) probiert, leider erfolglos.
Hat noch jemand eine Idee?
besten Dank
Wie kann so etwas passieren? Angenommen man hat eine Datenüberprüfung oder eine Bedingte Formatierung mit ausgelagerten Werten auf einem anderen Tabellenblatt. Wird dieses Blatt nun verschoben, passiert Folgendes:
Und in Datei / Information kann man sehen, aber nicht löschen:
Wenn Sie die Verknüpfungen endgültig löschen, werden die Formeln und externen Bezüge in Werte umgewandelt. Da dies nicht rückgängig gemacht werden kann, sollten Sie Ihre Datei unter einem anderen Namen speichern. Sind Sie sicher, dass Sie die Verknüpfungen endgültig löschen möchten?
Und was passiert? – Nichts!
Neben Formeln, Datenüberprüfung oder eine Bedingter Formatierung sind auch Namen eine Quelle von Verknüpfungen auf externe Dateien.
Martin Weiß (der tabellenexperte) listet sie alle auf:
ich habe auf einem Recht neuen Mac ein aufwändiges xls Dokument erstellt und habe auch häufig die Dropdown Funktion verwendet. Bei mir funktioniert diese Funktion in jedem Tabellenblatt. Jetzt muss mein Chef mit einem Microsoft Computer diese xls auch regelmäßig benutzen. Alle Funktionen sind in seinem xls zu sehen, aber alle Dropdown Funktionen sind bei ihm alle nicht sichtbar. Woran kann es liegen? Mac und Microsoft sind ja an sich kompatibel. Liegt es an seiner alten Version (2007)?
Vielen lieben Dank,
Anne
Kennst jemand von euch die Antwort? Ich bin leider überfragt …
Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100
Klappt.
Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.
Man müsste die Formel erweitern, beispielsweise so:
Boah – ist das mies! Zwei Stunden lang habe ich gesucht. Und dann gefunden.
Ich habe eine Datei mit zwei Tabellenblättern. Eines enthält eine Datenliste, ein zweites eine Datenüberprüfung mit einer Liste, die diese Daten aus dem anderen Blatt holt:
Per VBA ziehe ich nun diese beiden Blätter (einzeln!) in eine Masterdatei (man kann es auch per Hand machen. Die Verknüpfung verweist nun auf die alte Datei:
Der Code:
Dim xlFremdeDatei As Workbook
Dim xlEigeneDatei As Workbook
Dim xlFremdesBlattDaten As Worksheet
Dim xlFremdesBlattDatenüberprüfung As Worksheet
Set xlEigeneDatei = ThisWorkbook
Set xlFremdeDatei = Application.Workbooks.Open("D:\Eigene Dateien\Excel\Beispieltabellen\3Musketiere.xlsx")
xlFremdeDatei.Worksheets(2).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDatenüberprüfung = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Worksheets(1).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDaten = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Close SaveChanges:=False
xlEigeneDatei.Save
Beide Dateien werden geschlossen, die Masterdatei wird geöffnet. Unter Datei / Informationen wird angezeigt, dass sich in dieser Datei eine Verknüpfung (auf eine andere Datei) befindet, die man hier nicht löschen kann. Klar!
Nun setze ich in der Zelle mit der Datenüberprüfung per Hand oder per VBA die Verknüpfung auf die eigene Datei:
Excel zeigt noch immer (unter Datei / Informationen) an, dass sich in der Datei eine Verknüpfung befindet. Diesen Eintrag kann ich nicht löschen! Erst durch das Schließen und wieder Öffnen der Datei ist er verschwunden.
Das Erstaunliche: werden die Tabellenblätter gelöscht, wird die Verknüpfung nicht angezeigt. Werden die Verknüpfungen „nur“ behoben, bleibt der Eintrag noch in den Informationen stehen.
Zwei Stunden habe ich benötigt, um das herauszufinden. Mies!
Excelschulung. Wir üben die Datenüberprüfung (Gültigkeit).
Eine Teilnehmerin fragt, warum sie keine Datenüberprüfung einschalten darf:
Des Rätsels Lösung: sie hat nicht nur die (intelligente/formatierte) Tabelle markiert und Bereich außerhalb der Tabelle – sie hat auch die Ergebniszeile der Tabelle eingeschaltet. DORT kann man keine Datenüberprüfung einschalten:
Und schon wieder bin ich reingefallen. Ich möchte in einem Excel-Formular per VBA eine Datenübrprüfung einfügen. Referenzspalte ist Spalte A. Steht dort kein Wert wird eine Datenüberprüfung generiert:
For j = 11 To ThisWorkbook.Worksheets(i).Range(„A1“).SpecialCells(xlCellTypeLastCell).Row If ThisWorkbook.Worksheets(i).Range(„A“ & j).Value = „“ Then ‚ — Datenüberprüfung End If Next
Und natürlich erhalte ich einen Fehler: G36 ist „leer“ (weil verbunden mit G35), aber in G36 kann man keine Datenüberprüfung einschalten ( weil verbunden mit G35). Die Lösung: RAUS MIT DEN VERBUNDENEN ZELLEN:
Ich habe den Fehler nicht einkreisen können. Aber er ist da:
In einem Excelformular werden Daten eingegeben. Dort werden Datenüberprüfungen verwendet, die Daten aus einem anderen Tabellenblatt holen. Die Liste verwendet einen Bezug auf das Blatt =tbl_Basisdaten!…
Ich kopiere über eine Schaltfläche ein Tabellenblatt „Interview BIA“ von einer anderen Datei in die aktuelle Datei.
Dadurch wird der Bezug auf die alte Datei hergestellt =[Alte Datei.xlsm]tbl_Basisdaten!…
Das ist mir leider nicht aufgefallen, weil die Dateien auf meiner Festplatte liegen und es deshalb zu keiner Fehlermeldung kommt.
Das muss raus! Okay – wir ändern das:
Ich generiere die Datenüberprüfungen aufgrund der Basisdaten auf dem Interviewformular. Ich verwende keinen Bezug, schreibe per Programmierung „Auftragsabwicklung;Arbeitsvorbereitung und Einkauf;Produktrealisierung Individual;Produktrealisierung maschinelle Fertigung;Wartung / Instandhaltung;Lager / Logistik;EDV-Systembetreuung:
For i = 2 To intZeilen
strZellinhalt = ThisWorkbook.Worksheets(Blatt).Range(strSpalte & i).Value
strListeDatenüberprüfung = strListeDatenüberprüfung & "," & strZellinhalt
Next
If strListeDatenüberprüfung Like "*,*" Then
strListeDatenüberprüfung = VBA.Mid(strListeDatenüberprüfung, 2)
End If
If strListeDatenüberprüfung <> "" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=strListeDatenüberprüfung ' -- geändert, weil Interviewblatt nun importiert wird
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End If
Das Ergebnis:
Klappt:
Ich speichere die Datei, schließe sie und öffne sie:
„Wir haben ein Problem bei einigen Inhalten erkannt. Sollen wir so viel wie möglich wiederherstellen? Wenn Sie der Quelle dieser Arbeitsmappe vertrauen, klicken Sie auf ‚Ja‘.“ Die Datei ist kaputt! Ich finde den Fehler nicht!
Ich überarbeite für eine Firma ein Formular. Auf diesem Formular befinden sich eine Reihe an Dropdownfeldern (Datenüberprüfungen).
Ich kopiere das Tabellenblatt in eine andere Arbeitsmappe:
In der anderen Datei stelle ich fest, dass ich die Liste nicht mehr öffnen kann:
Ein Blick in Daten / Datenüberprüfung verrät mir warum: eine Liste wird zugelassen. Die Quelle der Liste verweist auf die erste Datei:
Klar: eine Datenüberprüfung (und eine bedingte Formatierung) können keinen Bezug auf andere Dateien nehmen. Also kopiere ich auch das fehlende Blatt in meine Datei. Was passiert? Nichts! Die Quelle verweist noch immer auf die erste Datei.
Also noch einmal: ich kopiere das Formularblatt und alle Blätter, auf denen die Daten für die Datenüberprüfungen liegen, in die zweite Datei. Dann funktioniert es.
In der Excelschulung zeige und erkläre ich den Manager für Regeln zur Bedingten Formatierung.
Später erläutere ich die Datenüberprüfung. Da kommt die Frage einer Teilnehmerin, ob Excel für die Datenüberprüfung auch einen solchen Manager zur Verfügung stellt. Finden kann man sie ja – mit Hilfe der Schaltfläche „Suchen und Auswählen“. Leider nein, lautet meine Antwort.
Ich verstehe es nicht. In einer (großen) Datei befinden sich mehrere Verknüpfungen. Ich lösche sie. Alle – bis auf eine. Sie ist störrisch und lässt sich nicht löschen:
Die Suche in der Datei liefert keinen Erfolg:
Es gibt auch keinen Namen, keine bedingte Formatierung, keine Datenüberprüfung, in der die Verknüpfung zu finden wäre.
Ich öffne das XML-Archiv und suche dort. DORT werde ich fündig:
Den Knoten löschen, das Archiv zippen – schon ist die Verknüpfung weg. Ich wüsste ja gerne wie so etwas passieren kann. Und – gibt es nicht einen bequemeren Weg die Verknüpfungen zu entfernen?
Heute in der Excelschulung bemängelte ein Teilnehmer, dass man Datenüberprüfungen nicht über die ganze Arbeitsmappe suchen kann.
Er hat recht: Während man Texte, Kommentare und Bedingte Formatierungen dateiweit suchen kann, funktioniert dies leider für die Datenüberprüfung nicht. Schade!
Ich zeige – wie immer – ein paar (wie ich finde) wichtige Tastenkombinationen:
[Strg] + [.]: aktuelles Datum
[⇑] + [Leertaste]: markiert die Zeile
[Strg] + [*]: der aktuelle Bereich wird markiert
[⇑] + [Strg] + [6]: Zahlenformat: Standard
und so weiter. Damit mache ich Menschen immer glücklich – nützliche Sachen, die man brauchen kann. Einem Teilnehmer war dies nicht genug: er wollte eine Tastenkombination für:
Sortieren
Blatt schützen
Datenüberprüfung, Bedingte Formatierung
Inhalte einfügen als Werte (okay: man kann die Zellen am Rand mit der RECHTEN Maustaste verschieben und auf den gleichen Ort zurückfallen lassen. Dann bietet das Kontextmenü „Hierhin nur als Werte kopieren“ an. Bei einer Zelle kann man eine Formeln mit [F2] und anschließend [F9] in einen Wert umwandeln.)
nach rechts „ziehen“ (Reihe ausfüllen). Auch hierzu ist mir nichts Intelligentes eingefallen: Wenn bereits Werte in der Zeile stehen kann man sie mit [⇑] + [Strg] + [→] markieren. Wenn die Zeile leer ist, kann man sie schnell über das Namensfeld selektieren: Dort trägt man beispielsweise A2:Z2 ein. Und dann den Wert der ersten Zelle mit [Strg] + [R] oder [F2] und [Strg] + [Enter] beenden.
Ich habe mich amüsiert und war froh, als die Schulung zu Ende war, weil ich befürchtet hatte, dass er noch mehr Tasten haben wollte für Dinge, hinter denen keine Tastenkombination liegen. Ich habe Werbung für meinen VBA-Kurs gemacht und auf die Möglichkeit hingewiesen, dass man die Befehle des Menübandes per [Alt] + [Buchstabe erreichen kann.
Schade eigentlich. Microsoft verwendet in Excel immer stärker Tabellen (wir nennen sie „intelligente Tabellen“, „Formatierte Tabellen“ oder „dynamische Tabellen“). Leider kann man sie nicht auflisten lassen. Das wäre praktisch – denn wenn man beispielsweise mit der Datenüberprüfung auf eine solche Tabelle zugreift, muss man dies mit
=INDIREKT(„Tabellenname“)
machen. Hier wäre – ebenso wie beim Namensmanager – eine Auswahlliste praktisch.
Eine interessante Frage einer Schulungsteilnehmerin:
Man kann Formate löschen:
Man kann bedingte Formatierungen löschen:
Man kann Kommentare löschen:
Man kann Hyperlinks löschen:
Aber – wie löscht man eine Datenüberprüfung?
Ich zeigte ihr, dass man in der Datenüberprüfung auf „Jeden Wert“ zurücksetzen kann:
Dann allerdings bleibt die Eingabemeldung:
Oder man löscht alles – dann löscht man allerdings die Inhalte:
Eine Lösung, wenn man mehrere Datenüberprüfungen mit mehreren Eingabemeldungen (und Fehlermeldungen) hat: Start / Bearbeiten / Suchen und Auswählen / Datenüberprüfung). Dann erhält man die Meldung „Die Auswahl enthält mehr als eine Prüfungsart. Sollen die aktuellen Einstellungen gelöscht und dann fortgefahren werden?“ Diese kann man bejahen.
Eine andere Lösung wäre: man kopiert eine leere Zelle und fügt über „Inhalte einfügen / Gültigkeit“ die Datenüberprüfung ein.
Aber explizit löschen kann man Datenüberprüfungen nicht.
Versuchen Sie einmal Folgendes: erstellen Sie eine oder mehrere Listen in einer gespeicherten Excelmappe.
Wechseln Sie auf ein anderes Tabellenblatt und erstellen dort eine bedingte Formatierung und/oder eine Datenüberprüfung mit einem Bezug auf das erste Blatt:
Kopieren Sie das Blatt in eine neue Arbeitsmappe. Speichern Sie die neue Mappe, schließen alles und öffnen die neue Zieldatei. Excel weist darauf hin, dass sich in der Arbeitsmappe eine Verknüpfung befindet:
Über Datei / Informationen sieht man diese Verknüpfung, aber:
Man kann sie nicht löschen! Auch Datei / Informationen / Auf Probleme überprüfen weist nur darauf hin, dass es eine Verknüpfung gibt. Verrät weder wo, noch hilft der Assistent diese Verknüpfung zu finden:
Man muss wissen so sie stecken – zum Glück kann man ja Bedingte Formatierung und Datenüberprüfung suchen. Und dort findet man dann die korrupten Verknüpfungen:
Ein Dankeschön an Bettina, die sich mit diesem Problem herumgeärgert hat, für diesen wertvollen Hinweis.
ich habe mir irgendwo ein Video angesehen, wo man z. B. bei Postleitzahlen festlegen kann, daß nur 5 Zahlen verwendet werden sollen:
Gebe ich aber eine PLZ ein, die mit einer Null beginnt, erscheint eine Fehlermeldung.
Ich möchte zum einen diese Datenüberprüfung ermöglichen, denn aber eine PLZ schreiben können, die mit einer Null beginnt. Wie muß diese Einstellung in der Datenüberprüfung aussehen?
Es wäre sehr nett, wenn Sie mir einen Hinweis geben würden.
Herzliche Grüße
F.
Sehr geehrter Herr F.,
wenn Sie die Zahl 01157 in eine (unformatierte) Zelle eingeben, dann wandelt Excel diese Zahl in die vierstellige Zahl 1157 um. Damit die 0 „stehen“ bleibt, müssen Sie die Zelle(n) vorher als Text formatieren.
Und das ist auch die Antwort: als Text formatieren UND die Datenüberprüfung einstellen.
Ein Mitarbeiter einer Firma erstellt in Excel 2007 eine Liste mit Bildern und Dropdownlisten, die über eine Datenüberprüfung gefüllt werden. Über 60 Namen „suchen“ mit der Funktion INDIREKT den Wert der Dropdownliste in einem anderen Bereich und geben die Zelle zurück, in der sich ein Bild befindet.
Diese Namen werden auf die Bilder angewendet: über die Bearbeitungszeile wird der Name mit bspw. =Bild24 zugewiesen.
Das Problem: Es funktioniert prima in Excel 2007. Öffnet man diese Datei jedoch in Excel 2010 oder höher, sind die Verweise weg. Alle! Das heißt: noch einmal die 60 Verweise setzen.
Von Version zu Version werden die Dinge ein klein wenig anderes beschriftet. Über den Sinn könnte man reflektieren.
Jedoch: warum beschriftet Microsoft nicht ALLE Stellen? Konsequent? Gleich?
So laufen noch immer „Datenüberprüfung“ und „Gültigkeit“ (im Dialog Inhalte einfügen) rum, der „Textumbruch“ heißt noch immer „Zeilenumbruch“ (im Dialog „Zelle formatieren / Ausrichtung“) und auf „Link“ statt „Hyperlink“ hat man sich auch noch nicht ganz festlegen können …
Heute kam die Frage, ob man nicht die Pfeilchen der Zellen, die eine Datenüberprüfung besitzen, permanent sichtbar machen kann. Und – nein! – Steuerelemente sind keine Alternative.
Die Antwort: nö, sorry, leider nicht – aber man kann die Datenüberprüfung finden – mit Start / Bearbeiten / Suchen und auswählen:
Excel unterscheidet an fast keiner Stelle zwischen Groß- und Kleinschreibung.
Ich kann einen Zellnamen (f3) in Kleinbuchstaben eintragen, einen selbst erstellten Namen in Kleinbuchstaben schreiben, Funktionen (summe), bei Vergleichen wird nicht unterschieden (=WENN(„RENE“=“rene“;1;0) liefert 1), sortieren (dort kann man es einschalten), filtern, …
An einer Stelle(*) wird jedoch unterschieden: bei der Datenüberprüfung:
In einem Kalender darf der Mitarbeiter U für Urlaub, S für Seminar, K für krank, D für Dienstreise und T für Telearbeitstag eintragen. Verboten sind ihm bei einer solchen Liste jedoch die Kleinbuchstaben. Ärgerlich!
(*) Ich weiß, es gibt noch weitere Stellen, bei denen Excel nicht case-sensitiv ist – jedoch bei der Datenüberprüfung ärgert es.
Und ich weiß: man könnte die Liste natürlich mit beiden Varianten erstellen. Oder über die Option „benutzerdefiniert“ die Groß- und Kleinschreibung abfangen. Aber warum nicht einfach bei der Liste?
Noch schlimmer: manchmal bin ich fest davon überzeugt, dass etwas nicht geht. Und dann geht es doch.
Heute hat mich folgende Mail erreicht:
„Hallo Herr Dr. René Martin,
im Video ‘2599_02_05-datenüberpruefung_nutzen.mp4‘ sagten Sie:
Achtung, wenn Sie mit Namen arbeiten, müssen Sie genau wissen, wie die Namen geschrieben werden, weil innerhalb der Datenüberprüfung haben Sie keine Möglichkeit festzustellen, wie heißt der Name nochmal – es gibt hier keine Auswahlliste, an der sie erkennen können, wie der Name geschrieben wurde.
Das ist so nicht korrekt, denn wenn der Cursor im Feld ‘Quelle‘ steht, bringt die F3-Tasste alle definierten Workbooks-Namen zum Vorschein und man kann auswählen.
Gruß von Luschi
Aus klein-Paris“
Danke an Luschi. Und ich habe wieder etwas gelernt.
Ich weiß, dass ich nicht zwei Datenüberprüfungen verketten kann oder – wie bei der bedingten Formatierung – nacheinander anwenden kann. Trotzdem – es wäre doch schön, wenn der Anwender aus einer Liste bestimmte Werte eintragen darf (Liste) – jedoch nur, wenn kein Feiertag und kein Wochenende ist:
Datenüberprüfung / Liste
Die Antwort: Wenn Sie mit der Datenüberprüfung eine benutzerdefinierte (Formel) eingeben, dann funktioniert es. Beispielsweise so:
Wie hat denn das mein Kollege gemacht? In einer Tabelle befinden sich Formeln. Das Blatt ist nicht geschützt (die Namen darf ich beispielsweise ändern). Versuche ich jedoch die Formel zu ändern, erhalte ich eine Fehlermeldung:
ziemlich cleverer Schutz!
Die Antwort: Die Zellen, in denen sich Formeln befinden, wurden markiert und in der Datenüberprüfung die Formel <>““ eingegeben. So können sie nicht mehr geändert werden:
Übrigens: Das Löschen wird allerdings nicht verhindert!
Ich habe eine Spalte, in der das Geschlecht angegeben wird. Dort habe ich mit Hilfe einer Datenüberprüfung eine Dropdownliste für männlich und weiblich erstellt. Klappt prima.
Liste
In einer anderen Spalte lege ich nun fest, dass dort nur die Zahlen zwischen (einschließlich) 1 und 10 eingetragen werden dürfen. Ich finde aber den Schalter nicht, mit dem ich eine Auswahlliste erhalte:
wo ist die Liste?
Die Antwort: Diesen Schalter gibt es nicht. Entweder Liste oder ganze Zahl zwischen 1 und 10. Wenn Sie sich für „ganze Zahl“ entscheiden, müssen Sie die Zahl eintragen und können nicht die Zahl über ein Dropdownfeld auswählen. Ist auch ein bisschen verständlich – man könnte ja schließlich auch die Grenzen zwischen 1 und 1.000.000 wählen. Dann müsste Excel eine Liste mit einer Million Zeilen zur Verfügung stellen …
Ich bin wirklich verblüfft. Wusste nicht, dass es geht:
Man erstellt eine Datenüberprüfung für mehrere Zellen (beispielsweise Zahlen, Datumswerte oder Listen).
Datenüberprüfung (Gültigkeit)
Diese (und einige anderen Zellen) werden über Zellen formatieren / Schutz „nicht gesperrt“. Das Blatt wird mit einem Blattschutz versehen. Nun ist es für den Anwender möglich, in die freien Zellen etwas einzutragen und diesen Inhalt in die Zellen mit Datenüberprüfung hineinzukopieren.
Das heißt: Das Einfügen von Inhalten unterläuft die Datenüberprüfung. Perfide!
In Excel fehlt die Möglichkeit eine Datenüberprüfung/Liste mit der Option „Firstletter“ zu belegen.
Da ich oft Formulare mache mit Datenüberprüfungen, teilweise mit langen Listen, würde diese Funktion also statt das an klicken auf dem Schiebebalken, da ja auch en scrollen nicht funktioniert, viel helfen.
Michael
Nur mit der Maus?
Es stimmt – man kann solche Listen mit [Alt] + [↓] öffnen, mit der Pfeiltaste nach unten navigieren und mit [Enter] bestätigen. Viel zu umständlich!
Was ist denn das? Ich wollte in einer Spalte sicherstellen, dass der Anwender nur Uhrzeiten eingibt. Aber ich scheitere schon bei der Verwendung des Dialogs „Datenüberprüfung“.
Ist 24:00 keine Uhrzeit?
Das ist richtig und merkwürdig. Excel erlaubt bei Uhrzeiten nur Werte zwischen 0:00 und 23:59. Wenn Sie eine Spalte haben, in der Stunden eingetragen werden, beispielsweise: 40:00 oder 36:00 oder 18:00, müssen Sie auf die Option „Dezimal“ zurückgreifen. Dort müssen Sie allerdings die Grenzen dezimal eintragen, also statt 48:00 (Stunden) muss der Wert 2 (Tage) eingegeben werden.
Warum ist eigentlich die DatenÜBERPRÜFUNG in der Registerkarte DATEN und nicht in der Registerkarte ÜBERPÜFEN? Und wo wir gerade dabei sind – warum wanderte die Pivottabelle, pardon: die PivotTable von Daten zu EINFÜGEN? Da gehört sie doch wirklich nicht hin …
Aufteilung der Registerkarten
Nun zum einen wird man es niemals allen Benutzern recht machen. Zum anderen – ich weiß es nicht. Und kenne auch keinen, den ich fragen kann. Nehmen Sie es als exclegegeben hin …
Hallo zusammen! – Überall in Excel darf ich das Prozentzeichen eingeben; überall versteht er, dass dahinter die Rechenoperation geteilt durch 100 gemeint ist. Warum darf ich das nicht in der Datenüberprüfung?
Datenüberprüfung mit Prozent
Die Frage ist berechtigt, denn es erstaunt. Sie müssen hier die Prozentzahl als Dezimalzahl eingeben, also 0,1, wenn Sie möchten, dass der Anwender nicht mehr als 10% Zinsen einträgt.
Warum spricht Excel manchmal von „Gültigkeit“, an anderen Stellen von „Datenüberprüfung“?
Gültigkeit
Datenüberprüfung
Die Antwort: das Betriebssystem „zieht“ sich ein Menü, das noch die alten Beschriftungen von Excel 2003 verwendet. Andererseits wurde das Menüband von Microsoft übernommen – nach der Konvention von ungefähr Excel 2007. Das im Wechsel von Excel 2003 zu Excel 2007 die Gültigkeit in (Daten)überprüfung umbenannt wurde, findet sich nun in Excel die alte Beschriftung und die neue. Das ist nicht gerade konsistent …
Oh Gott, da hat jemand rote Ellipsen in Excel reingemacht – die bekomme ich nicht mehr weg!
Klar – da hat jemand eine Datenüberprüfung (Gültigkeit) eingeschaltet und sämtliche Werte anziegen lassen, die nicht dieser Datenüberprüfung standhalten. Ebenso können Sie die Datenüberprüfung wieder ausschalten: