---

Hallo Gemeinde,

in einer Excel-Tabelle sollen die Eingaben in den 4 Zellen H1049, H1053, H1056 und H1060 gemäß bereits produzierter und demzufolge mit Werten hinterlegter Kombinationen geprüft / ausgewertet werden.

Bisher hat das für bis zu drei verschiedene Eingabefelder mit mehr oder minder langen Formeln unter "Datenüberprüfung->Einstellungen->Formel:" auch gut funktioniert.

Jetzt taucht das erste Mal eine Auswahl auf, bei der auch dieses vierte Feld in die Auswertung mit aufgenommen werden soll und da entstünde dann folgende Formel:

Code:

---

=WENN(UND(ODER(H1049="L";H1049="R";H1049="RL");ODER(H1053=10;H1053=16;H1053=20);ODER(H1056="A";H1056="J");ODER(H1060=1980;H1060=3000;H1060=3500;H1060=4500);UND(ODER(H1049<>"L";H1056<>"A");ODER(UND(H1056="J";ODER(H1060=3000;H1060=4500));UND(H1053=16;H1060=3500);UND(H1053<20;H1056="A";H1060<>3500))));WAHR;FALSCH)

---

Diese Formel erzeugt tatsächlich - in eine eigene Zelle eingetragen - exakt entsprechend der Vorgaben "WAHR" bzw. "FALSCH" und wäre demzufolge eigentlich perfekt dafür geeignet, die Überprüfung im Formelfeld der Datenüberprüfung zu übernehmen.

Nur lässt sie sich dort leider per Strg+C -> Strg+V nicht einfügen.

Meine Vermutung war nun, dass dieses Formelfeld vielleicht eine Begrenzung bezüglich der dort einfügbaren Zeichenanzahl hat, konnte aber nichts dementsprechendes ergooglen 

Daher verkürzte ich die obige Formel noch ein wenig, aber kürzer wollte sie mir einfach nicht mehr gelingen:

Code:

---

=WENN(UND(ISTZAHL(SUCHEN(H1049;"RL"));ODER(H1053=10;H1053=16;H1053=20);ODER(H1056="A";H1056="J");ODER(H1060=1980;H1060=3000;H1060=3500;H1060=4500);UND(ODER(H1049<>"L";H1056<>"A");ODER(UND(H1056="J";ODER(H1060=3000;H1060=4500));UND(H1053=16;H1060=3500);UND(H1053<20;H1056="A";H1060<>3500))));WAHR;FALSCH)

---

Und leider lässt sie sich auch so verkürzt nicht einfügen.

Da die ersten vier ODER()-Blöcke in der Ursprungsformel nur dazu dienen sollen, die Eingaben in den vier Zellen auf die jeweils wenigen korrekten Werte einzugrenzen, könnte man sie vielleicht irgendwie elegant auf die jeweils direkt in den entsprechenden Zellen erfolgende Auswertung mittels bedingter Formatierung (falscher Wert = rote Zelle, bevorzugter Wert = hellblau, akzeptable Werte oder leere Zelle = hellgrün) umlenken, aber ich habe auch dazu über Google nichts wirklich hilfreiches gefunden ...

Diese Eingabeprüfung in der Datenüberprüfungsformel hat sich als notwendig erwiesen, da man ohne sie bei einer Datenüberprüfung über mehrere Zellen, in einer oder mehreren dieser Zellen völligen Blödsinn eingeben kann und solange der letzte eingegebene Wert korrekt war, nicht einmal eine Fehlermeldung bekam.

Hm, ich schweife ab ... also, noch einmal kurz: gibt es eine Längenbeschränkung für die Datenüberwachungsformel ? Falls ja: hat jemand eine Idee, wie ich meine Formel(n) deutlich kürzer gestalten könnte ohne andere Tabellenbereiche/-blätter oder VBA zu bemühen ? Falls nein: woran kann es liegen, dass die Formeln in separaten Zellen funktionieren, aber sich nicht einfügen lassen ? ... eine "Verkürzung" der Formel durch hochschieben auf dem Tabellenblatt in den Bereich von z.B. H49 bis H60 fällt leider flach, da zum einen dort schon Daten stehen und zum anderen diese Problematik mir dann höchstwahrscheinlich an anderer Stelle von hinten in die Kniekehlen treten würde 

Eine mögliche Variante wäre es, die Einträge in diese Felder jeweils aus einer vorgegebenen DropDown-Liste wählen zu lassen und so die Notwendigkeit der Eingabeüberprüfung ganz wegfallen zu lassen ... würde für mich allerdings für die bereits erstellten Bereiche einen nicht unerheblichen Änderungsaufwand bedeuten ... vielleicht hat ja jemand einen anderen Vorschlag.

Danke für Euer Gehirnschmalz

Gernot 

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hallo,
die maximale Formellänge beträgt schon in Office 2003 ganze 1024 Zeichen und wurde seitdem erhöht.
Das kann also nicht die Ursache sein.
Ich habe in meinem Excel keinerlei Probleme, deine Formel zu verwenden.
Trotzdem wäre IMHO deine Idee:

Zitat:

---

Eine mögliche Variante wäre es, die Einträge in diese Felder jeweils aus einer vorgegebenen DropDown-Liste wählen zu lassen und so die Notwendigkeit der Eingabeüberprüfung ganz wegfallen zu lasse

---

wesentlich eleganter und pflegeleichter

------------------

Gruß Thomas
CADdog the dog formerly known as TR

AutoCAD spricht mit einem, aber viele hören nicht zu.

[Diese Nachricht wurde von CADdog am 25. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hi Gernot,

m.W. ist bei Gültigkeitsprüfungen die Länge der Formel auf 255 Zeichen beschränkt und sie lassen sich nicht per Copy and Paste übertragen - sie müssen von Hand eingegeben werden.

Du kannst aber die Formel in eine Zelle eintragen und dich in der Gültigkeitsprüfung dann auf das Formelergebnis beziehen.

Bis später,
Karin

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin Thomas,

da Windows (und besonders Windows 10) ja durchaus manchmal nicht reproduzierbare Fehler "erzeugt" und Excel 365 vom gleichen Hersteller kommt, probierte ich es heute morgen mit recht frisch gestartetem Rechner unmittelbar nach dem Lesen Deines Posts noch einmal ... und es funktioniert NICHT   

Ich vermute, Du hast die Formel in eine "normale" Zelle kopiert ? ... DA funktioniert es auch bei mir, das Problem ist bei mir die Einfügung in das Formelfeld unter "Datenüberprüfung->Einstellungen->Formel:" ... egal ob ich eine bereits existierende Datenüberprüfung mit einer der beiden Formeln überschreiben oder in einer anderen Zelle eine komplett neue Datenüberprüfung anlegen will: die Formel lässt sich weder mit Strg+C und Strg+V noch mit "Rechtsklick->Einfügen" dort hineinschreiben ... und bei "heineinschreiben": schreibe ich die zweite, etwas kürzere Formel händisch in dieses Formelfeld, komme ich genau bis:

Code:

---

=WENN(UND(ISTZAHL(SUCHEN(H1049;"RL"));ODER(H1053=10;H1053=16;H1053=20);ODER(H1056="A";H1056="J");ODER(H1060=1980;H1060=3000;H1060=3500;H1060=4500);UND(ODER(H1049<>"L";H1056<>"A");ODER(UND(H1056="J";ODER(H1060=3000;H1060=4500));UND(H1053=16;H1060=3500);UND(

---

was eine programmiertechnisch "magische" String-Länge von 256 Zeichen ergibt (falls ich mich nicht total verzählt habe, aber =Länge("...") funktioniert auch nach Ersetzen der Anführungszeichen innerhalb des Strings wegen Überlänge nicht und auf Basteleien mit Verketten o.ä. hatte ich im Moment keine Lust mehr ... o.k.: mit Länge=() auf die in einer anderen Zelle auskommentierte Formel funzt es doch: zweite Formel insgesamt 303 und eintippbarer Teil 256 Zeichen ... oder doch nur 302 bzw. 255, falls das auskommentierende Hochkomma mitgezählt wird ? ... und das nochmal auszuzählen habe ich nun wirklich keine Lust mehr) ... insofern betrachte ich meine Vermutung über eine Längenbegrenzung in speziell diesem Formelfeld mal einfach als leider bewiesen   

Was wohl doch auf die (zugegebener Maßen) elegante Version mit den DropDown-Listen hinausläuft, obwohl mir die insofern "unsympathisch" ist, als dass ich die dafür notwendigen Vorlage-Listen eben doch in anderen Tabellenbereichen/-blättern "hinterlegen" muss und ich eigentlich gern alles lokal relevante auch wirklich lokal zusammenhalte ... schade eigentlich   

Trotzdem danke und 10 Unities an Dich für den Versuch

Gernot   

und fertig

o.k.: während ich diesen Schrieb schrieb, trudelte im Hintergrund Karins Post mit der Antwort auf meine Frage ein ... daher die 10 Unities an sie und nur 5 an Thomas ... ja-ja, unfair, aber angemessen  

ABBA nu wirklich: fertig

[Diese Nachricht wurde von ArCADe-Spieler am 25. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin Karin,

Dein Post ist zwar die Antwort auf meine Frage nach der (eventuell) vorhandenen Längenbegrenzung in diesem Formelfeld, aber ich möchte Dich insofern korrigieren, als dass ich seit Jahren die in diesem Feld verwendeten Formeln wegen der echt beschissenen Editierfunktionen innerhalb solcher Formelfelder (betrifft z.B. auch bedingte Formatierungen usw. ... wo wahrscheinlich die gleiche Längenbegrenzung gilt ?!) bis auf wenige, sehr kurze Ausnahmefälle, immer komplett in "normalen" Zellen oder sogar in einer parallel geöffneten NotePad-Instanz zusammenstelle und dann sehr wohl per Copy'n'Paste in diese Formelfelder eintrage.

Deinen zweiten Vorschlag mit der ausgelagerten Gültigkeitsprüfung hatte ich gestern schon getestet, aber wieder verworfen, weil "sichtbar" und damit "unelegant" 

Grüße

Gernot 

und fertig

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hi Gernot,

deine Aussage bezüglich Copy and Paste trifft zu, wenn die Formel < 256 Zeichen lang ist - nicht jedoch wenn sie länger ist.

Bis später,
Karin

[EDIT]: Anrede korrigiert

[Diese Nachricht wurde von Beverly am 25. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

... so nun doch noch ein letztes Mal von mir zu diesem Thread (und schon ein Wenig offtopic, aber um das Ganze abzurunden     ):

Es läuft nun wohl doch auf die in eine separate Zelle ausgelagerte Gültigkeitsprüfung hinaus, da die DropDown-Listen ja NUR die eingegebenen Werte gemäß der vorher (anderswo) hinterlegten Listen einschränken würden, dann jedoch NICHT mehr die zwingend erforderliche Prüfung auf Kompatibilität aller eingegebenen Werte gemäß der bereits produzierten und demzufolge mit Werten hinterlegten Kombinationen mittels Datenüberprüfung möglich wäre ... die ist ja dann schon durch die Liste "verbraucht".

Und ich muss doch die erste (längere) Formel benutzen, da merkwürdiger Weise die zweite Formel (mit ISTZAHL(SUCHEN(H1049;"RL")) ) auch bei einer leeren Zelle H1049 ein WAHR ergeben kann, wenn die drei anderen Werte den Vorgaben entsprechen.

Demzufolge steht nun in H1064 folgende Formel:

Code:

---

=WENN(UND(ODER(H1049="L";H1049="R";H1049="RL");ODER(H1053=10;H1053=16;H1053=20);ODER(H1056="A";H1056="J");ODER(H1060=1980;H1060=3000;H1060=3500;H1060=4500);UND(ODER(H1049<>"L";H1056<>"A");ODER(UND(H1056="J";ODER(H1060=3000;H1060=4500));UND(H1053=16;H1060=3500);UND(H1053<20;H1056="A";H1060<>3500))));WAHR;FALSCH)

---

und ergibt WAHR oder FALSCH

und in den Formelfeldern der Datenüberprüfung der Zellen H1049, H1053, H1056 und H1060 steht jeweils:

Code:

---

=WENN(ODER(H1064=WAHR;ANZAHLLEEREZELLEN(H1049:H1060)>3);WAHR;FALSCH)

---

Das ist zwar ein Wenig "schmuddelig" programmiert, weil der Bereich H1049:H1060 neben den vier auf nicht-Leerheit zu prüfenden Zellen eben auch beschreibende Überschriften und ->3<- Leerzeilen/-zellen enthält, ich aber in dieser Formel nicht jede der vier Werte-Zellen auf <>"" prüfen lassen wollte ... grrr, da ich die Excel-Tabelle nicht schützen will/werde, muss ich die Einzelprüfung wohl doch durchziehen, da ja "aus Versehen" jemand eine der Überschriften löschen oder etwas in eine der Leerzellen schreiben und so diese "schmuddelige" Abfrage aushebeln könnte ... grmbl ... also steht dort nun jeweils:

Code:

---

=WENN(ODER(H1064=WAHR;ODER(H1049="";H1053="";H1056="";H1060=""));WAHR;FALSCH)

---

Sollte doch noch jemand einen Geistesblitz haben - nur her damit.

Grüße

Gernot (und nicht Thomas)    

und fertig

[Diese Nachricht wurde von ArCADe-Spieler am 25. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Servus,

Ohne Beispiel Excel diese lange Formel prüfen macht wenig Sinn.

Eventuell die Gültigkeit per VBA prüfen lassen. Dort könnte man es halbwegs lesbar inkl. Kommentaren hinschreiben.

EDIT: Man könnte auch die Formel mit einer Gültigkeitsprüfungen komponieren, dann dürfte sie auch kürzer werden.

------------------
<----- Bitte Systeminfo eintragen, warum siehst du hier. Schöne Grüsse aus der Steiermark  Bernd P.

[Diese Nachricht wurde von Bernd P am 26. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

DemoDatenuberprufung.xls.txt

Hallo Bernd,

es ging NICHT um die Prüfung der Korrektheit der vorgegebenen Formeln ... die machen - zumindest die erste, längere - tatsächlich ganz genau das, was sie sollen, nämlich die vier Eingabewerte gemäß bereits produzierter und daher mit weiter verwendbaren Werten hinterlegter Kombinationen auf WAHR oder FALSCH zu prüfen.

Die komplette Excel-Tabelle (>>20MB) würde auch nicht beim Verständnis der Formel(n) helfen, da die Matrix der bereits produzierten Kombinationen dort nur in Form eben dieser Formeln und der Eingabemeldungen der 4 Zellen hinterlegt ist. Daher habe ich mal einen Mini-Schnipsel (von zwei anderen, zufällig erwischten Stellen) hochgeladen, um die gegebene Situation zu illustrieren ... die ist allerdings eben nur an den konkreten Positionen in der Tabelle genau so, im nächsten Teilabschnitt können geringfügig oder deutlich abweichende Bedingungen gelten ... eben immer in Abhängigkeit von den bisher bereits produzierten bzw. technisch realisierbaren Kombinationen.

Meine Frage bezüglich hilfreicher Vorschläge für die Bearbeitung/Verkürzung der Formel (damit sie ohne "Auslagerung" in das 255-Zeichen-Formelfeld der Datenüberprüfung passen könnte) zielte nur auf prinzipielle Tipps, wie ich vielleicht z.B. die Prüfung der korrekten Werteingabe in jeder der 4 Zellen aus der Kombinatorik-Formel für die Datenüberprüfung herausnehmen könnte, da diese Werte-Prüfung zwar schon in jeder dieser Zellen selbst per bedingter Formatierung erfolgt, aber leider eben auch bei der Kombinations-Datenüberprüfung notwendig zu sein scheint. Ohne diese Werte-Prüfung kann - wie bereits weiter oben beschrieben - die Kombinations-Datenüberprüfung ausgetrickst werden.

Theoretisch könnte man zwar die eigentliche Kombinatorik-Prüfung so kleinteilig ausführen, dass man tatsächlich ALLE möglichen Kombinationen erschlägt, aber das würde die Verwendung von >, < und <> als bequeme Operatoren nahezu ausschließen und die Formel würde noch länger werden (müssen) ... ich habe es in meiner Excel-Tabelle an zwei Stellen mit zwei bzw. drei wechselseitig zu prüfenden Eingaben auf diesem Weg versucht und war schon während der Zusammenstellung der entsprechenden Formeln von deren Länge im Vergleich mit der bei integrierter Werte-Prüfung entsetzt ... und hab's ab da gelassen.

Dein Edit deutet ja schon etwas an, aber so richtig verstanden was wo wie habe ich ihn nicht.

Grüße

Gernot    

[Edit] jetzt auch wirklich mit Anhang ... ".txt"-Erweiterung löschen und als normale xls-Datei öffnen

[Edit2] hm, vielleicht JETZT mit Anhang ? Der Datei-Upload "sagt" bei leerem Fenster in der Fenster- bzw. Tab-Beschriftung jedes Mal "Datei gespeichert" ... scheint aber nicht zu funktionieren - sorry

[Diese Nachricht wurde von ArCADe-Spieler am 26. Feb. 2020 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP