Excel / OO Calc: Gültigkeitsbereich - Nur verfügbare Werte anbieten

Hallo zusammen und ein frohes neues Jahr!!
Ich weiß nicht ob die Überschrift für den Thread sooo verständlich ist, die Sache lässt sich leider nur schwer erklären, aber ich versuchs: Es geht um folgendes Problem bei OpenOffice Calc (Vers. 3.0). Eine Lösung für Excel (2000 oder 2003) würde mich aber ebenfalls glücklich machen

Also ich habe einen Zellbereich X definiert, zB. A1:A100. Weiterhin wurden anderen Zellen (zB. die ganze B-Spalte) mit "Daten -> Gültigkeit" so definiert, dass keine Werte manuell eingegeben werden können, sondern lediglich ein Dropdownmenü mit den Werten des Zellbereiches X zur Auswahl steht. So weit so gut. Wenn ich allerdings einen Wert aus dem Dropdownmenü auswähle, bleibt dieser Wert weiterhin zur Auswahl im DDMenü erhalten.
Nun meine Frage: Gibt es eine Möglichkeit, dass im Dropdownmenü nur noch die Werte zur Verfügung stehen, die noch nicht ausgewählt wurden, also noch nicht im "definierten Gültigkeitsbereich" vorhanden sind??

Ist schwer zu erklären, ich hoffe ihr versteht worum es geht, sonst muss ich weiter ausholen

Besten Dank vorab und schöne Grüße.

 

Hallo danleh

mit einer Zusatzspalte weit rechts draussen geht es einigermassen... Du kannst diese Zusatzspalte dann auch verstecken...

Bereich A1:A100 enthält die vorgegebenen Werte
Bereich B1:B100 enthält die DropDowns

Zusatzspalte ist zB M, dann kommt in M1 diese Formel >
=WENN(ZÄHLENWENN(B$1:B$100;A1)=0;A1;"")
...das runter kopieren bis M100

Die Zelle B1 bekommt als Gültigkeit >>> Liste >>> "M1:M100" ......auch runter kopieren bis B100

Jetzt wird jeder in Spalte B schon ausgewählter Wert im DropDown nicht mehr angeboten.
Schönheitsfehler (oder auch nicht... man muss es nur als Feature verkaufen...) >>>
> die leeren Zellen in Spalte M bleiben als Leerzeile im DropDown...

Beverly hatte einmal eine Formel gepostet, mit der man in Spalte N eine zwischenraumlose Liste aus der Spalte M machen könnte.
Die Gültigkeit müsste dann auf Spalte N verweisen.

...aber leider habe ich vergessen, wie Beverly's Formel lautete...

Gruss Urs

 

Hallo Hascheff und Urs2, vielen Dank für eure Antworten!!

Es handelt sich zwar nicht um die selbige "Datenbank", wohl aber um eine gleiche, welche auf einem ähnlichen Konzept aufbaut. Zu deiner Access-Empfehlung: Für einen User wäre es ja noch 'akzeptabel', sich der ganzen Sache via MS Access / OO Base anzunehmen. Da diese Tabelle(n) allerdings in erster Linie Vorlagen für "Dau's" sind, ist ein Umstieg auf Datenbankprogramme wohl mit etwas mehr Support- bzw. 'Lehraufwand' verbunden, sodass Aufwand und Nutzen wohl zuuu ungleich wären... Aber trotzdem danke für deine Info

@Urs2: Genaaaauuuu sooo hab ichs mir vorgestellt

Warum als Feature verkaufen, für mich ist es sogar ein Feature, da mit den Leerzeilen eine gewisse Übersicht gewahrt wird - die Leerzeilen verdeutlichem dem "Dau": "Aaaahhhh, die Werte hab ich wohl schon verwendet" *lol*

Thread gelöst?? ZU 100%!!!

Besten Dank euche beiden!!

 

@danleh
Genau so muss das funktionieren >>> ICH habe DIR meine Unzulänglichkeit als Feature verkauft... und Du bist begeistert !


@Beverly
Deine Konzentrier-Formel wäre wohl auf Deiner Homepage zu finden...
...leider führt der Link dorthin, hier in Deinem Benutzerprofil, nur ins Leere...

Gruss Urs

 

Hi Urs,

heute ist meine Homepage leider immer wieder mal kurzzeitig offline - liegt wohl am Server, auf den ich aber keinen Einfluss habe.

Die Formel findet man jedoch nicht auf meiner Seite (da gibt es mehr was zu VBA und zu Diagrammen) sondern unter diesem Link: http://www.excelformeln.de/formeln.html?welcher=194 auf der ultimativen Formelseite im Web

 

Bewirb dich doch mal bei Microsoft...

 

...das Problem hatte mir jetzt doch keine Ruhe gelassen und musste gelöst werden...

@Karin - Danke für den Link
Entweder hatte ich das dort falsch verstanden, oder es eignet sich tatsächlich nicht für unser Vorhaben.
Bei Pearson hatte ich dann doch noch ein Formel-Monster gefunden und übersetzt. Als ich dann endlich begriffen hatte, dass es ja eine Array-Formel ist... funktionierte es perfekt.
>>> http://www.cpearson.com/EXCEL/noblanks.htm


@danleh
Falls mein "Feature" doch einmal in Verruf geraten sollte, und um das hier seriös abzuschliessen >>>

Es braucht eine zweite versteckte Hilfsspalte, in meinem Beispiel neben M die Spalte N
Jetzt die Bereiche in beiden Spalten benennen >
Bereich M1:M100 heisst "MitLeerzellen", Bereich N1:N100 heisst "OhneLeerzellen"

In Zelle N1 kommt diese kleine Formel >

Code:

=WENN(ZEILE()-ZEILE(OhneLeerzellen)+1>ZEILEN(MitLeerzellen)-ANZAHLLEEREZELLEN(MitLeerzellen);"";INDIREKT(ADRESSE(KKLEINSTE((WENN(MitLeerzellen<>"";ZEILE(MitLeerzellen);ZEILE()+ZEILEN(MitLeerzellen)));ZEILE()-ZEILE(OhneLeerzellen)+1);SPALTE(MitLeerzellen);4)))
            .........

Achtung es ist eine Array-Formel !
Die Formel in die Zelle kopieren und dann den Mauszeiger in die Eingabezeile setzen, wie zum Aendern der Formel...
...jetzt gemeinsam die Tasten Strg+Umschalt+Eingabe drücken
Die Formel ist jetzt von geschweiften {Klammern} umgeben >>> diese aber nie eintippen, das macht Excel selbst.

... dann runter kopieren bis N100
... und die DropDowns auf Spalte N (anstatt M) verweisen lassen.
Jetzt werden alle Leerzeilen zusammen ans Ende des DropDowns gestellt


Bei Microsoft bewerben? Wo ich den faulen Feature-Trick doch gerade bei denen abgeschrieben hatte...

Gruss Urs

 

@Urs,

sorry, ich hatte auf das falsche Beispiel verlinkt - das betraf Auflisten ohne Doppelte. Der richtige Link ist dieser: http://www.excelformeln.de/formeln.html?welcher=43

@danleh,

im Anhang eine Lösung mit der Formel aus dem Link in diesem Beitrag - in Spalte N werden alle noch nicht verwendeten Namen ohne Leerzeilen aufgelistet (die Formel musst du so weit nach unten ziehen wie bei dir in Spalte A Daten stehen). Außerdem ist der Name "DropdownBereich" über Einfügen -> Namen -> Definieren festgelegt, der als Quelle in den DropDown-Listenfeldern der Spalte B angegeben ist. Der Name wurde mit der Funktion BEREICH.VERSCHIEBEN definiert, wobei sich die Anzahl an aufgelisteten Werten aus dem Maximum der Spalte O ableitet (diese Formel musst du ebenfalls entsprechend weit nach unten ziehen). Der Umweg über die Hilfsspalte O ist leider erforderlich, weil andernfalls die letzte belegte Zelle in Spalte N falsch ermittelt würde.
Wenn du nun ein DropDown-Listenfeld in Spalte B anwählst, werden nur noch die "übriggebliebenen" Namen aufgelistet (ohne jegliche Leerzeilen).

 

Hallo Beverly!
Vielen vielen Dank für deine Mühe!! Ich habe zwar meine Tabelle(n) schon entsprechend bearbeitet und angepasst, werde aber bei Bedarf auf deine Vorlage zurückgreifen
Nochmals allerbesten Dank für die promte ausführliche und umfangreiche Hilfe!!!
Schöne Grüße!