[gelöst] notwendiges Scrollen Auswahlliste bei Gültigkeit

[theindless]

Hallo miteinander,

ich habe folgendes Problem.

Ich habe in einer Tabelle über "Gültigkeit" eine Auswahlliste eingefügt, welche die Werte aus einem Zellbereich zieht.

Der tatsächliche Wertebereich hat derzeit 15 Einträge, kann sich jedoch jederzeit erweitern, da neue Kriterien hinzukommen.
Aus diesem Grund habe ich den Zellbereich sehr weit definiert ($B$3:$B$100).

Wenn ich nun das Auswahlfeld öffne, erhalte ich zuerst eine leere Liste angezeigt.
Die tatsächlichen Werte erreiche ich erst über ein Scrollen nach oben.

Zuerst dachte ich, dass es funktioniert, wenn ich den Haken bei "Leerzeilen zulassen" entferne, aber dem war nicht so.

Ist es möglich, dass beim Öffnen die erste Zeile des Wertebereichs oben steht, so dass sofort eine Auswahl möglich ist?

Danke für eure Hilfe.

theindless

[Karolus]

Hallo
Hier ist die erste Zeile oben.


Karo

[theindless]

Hallo Karo,

vielleicht habe ich mich falsch ausgedrückt.

Die erste Zeile ist natürlich oben , aber beim Drücken auf den Pfeil wird erstmal eine leere Liste angezeigt und ich muss hochscrollen.

Ich hab mal ne Beispieldatei angehängt

[balu]

Hallo

in OOo 3.2.1 ist es so wie es sein soll. In der 3.3.0er trit dein Phänomen auf. Abhilfe bei mir hier, auch wenn nicht ganz so elegant, die erste Zelle des Datenbereichs einfach leer lassen. Dadurch wird dann leider in der Auswahl auch die erste Zeile leer angezeigt.



Gruß
balu

[theindless]

Hallo balu,

danke für die Umgehungslösung. Funktioniert so auch bei mir.

Ist zwar nicht so schön, da der Quellbereich aus einer Liste mit Spaltenüberschrift stammt und ich somit

a) die Spaltenüberschrift weglassen oder
b) eine Leerzeile nach der Spaltenüberschrift einfügen

muss.

Aber es funktioniert... Danke!

theindless

[OttoZ]

... noch eine "Umgehungslösung"
mfG OttoZ

[theindless]

Hallo,

@OttoZ
Danke für die weiteren Bemühungen.
Deine Lösung (Leerzeichen in Zellen schreiben) funktioniert auch.

Ermöglicht sogar, dass ich eine Spaltenbeschriftung beibehalten kann und keine Leerzeile benötige.
Leider wirft diese ein anderes Problem in einer Formel von mir auf... mal sehen wie sich das lösen lässt.

Ich habe ausgehend von deinem Vorschlag noch 2 Dinge versucht:
1. Leerzeichen nur in den ersten, leeren Zeilen des Datenbereiches
2. Leerzeichen nur in den letzten, leeren Zeilen des Datenbereiches

Leider ohne Erfolg...

[balu]

Hallo theindless,

ich hab jetzt für dich wohl eine zufriedenstellendere Lösung im Gepäck. Jedoch bedarf es dafür ein paar Verrenkungen. Das hat aber auch seine Vorteile, wie Du dann sehen wirst.

Zur Lösung trägt eine Formel bei.

Code: Alles auswählen

INDIREKT(ADRESSE(1;SPALTE(A1);4; ;"Tabelle1")&":"&ADRESSE(MAX(ZEILE(A1:A1000)*(A1:A1000<>""));SPALTE(A1);4))

Der Teil der sich innerhalb von INDIREKT befindet, erstellt eine Bereichsadressierung in der Form von: Tabelle1.A1:A12 her. Dabei wird immer die erste Zelle und die letzte benutzte Zelle des Datenbereichs A1:A1000 ausgewertet. Das heißt, so bald ein Eintrag in dieser Spalte (Datenbereich) hinzukommt, passt sich die Adressierung automatisch an. So kann dann hinterher z.B. Tabelle1.A1:A23 dabei rauskommen.

Diese Formel habe ich aber nicht direkt bei Gülktigkeit eingesetzt, sondern ich habe ihr einen Namen (DATENFORMEL) zugeordnet, "Strg" + "F3". Und nur der zugeordnete Name steht bei Gültigkeit.

Als gegenprobe habe ich die Formel auch noch mal ins Tabellenblatt geschrieben. Jedoch ohne INDIREKT, da dort nur die Bereichsangabe angezeigt wird, aber als Matrixformel abgeschlossen. Denn das ist nämlich die Formel, sonst würd sie nicht zuverlässig funktionieren.

Wenn Du schwierigkeiten mit der Formellösung hast, musst Du dich noch mal melden.

Ansonsten wünsch ich dir viel Spaß und Erfolg beim anpassen.


Gruß
balu

[Stephan]

leider nun quasi doppelt:

ein versuchweiser Eintrag des belegten Zellbereichs als Bereich:

$Tabelle1.$A$1:$A$12

für Daten-Gültigkeit, zeigt mir das in der Auswahlliste kein führender Leereintrag auftritt wenn nur belegte Zellen für die Auswahlliste genutzt werden. Folglich ist eine LÖsung dadurch möglich das man nur die belegten Zelklen angibt, z.B. indem man den entsprechenden Bereich, unter Daten-Gültigkeit, per Formel bestimmt:

INDIREKT("$Tabelle1.$A$1:$A$" & ANZAHL2($Tabelle1.$A$1:$A$1000))



Gruß
Stephan

[Karolus]

Hallo

INDIREKT("$Tabelle1.$A$1:$A$" & ANZAHL2($Tabelle1.$A$1:$A$1000))

oder

Code: Alles auswählen

VERSCHIEBUNG($Tabelle1.$A$1;0;0;ANZAHL2($Tabelle1.$A$1:$A$1000))

Karo

[balu]

Hallo Stephan,

ich würd sagen das deine Formellösung doch bedeutend übersichtlicher und kürzer ist. Aber dafür müsste sie ein ganz klein wenig geändert werden, da sie nach meinen Beobachtungen nicht korrekt arbeitet.

Code: Alles auswählen

INDIREKT("Tabelle1.A1:A" & ANZAHL2(Tabelle1.A1:A1000)+1)

Ohne den Korrekturfaktor +1 wird nämlich die letzte benutzte Zelle nicht berücksichtigt, wenn zuvor noch eine leerzelle vorhanden ist.


Gruß
balu

[Stephan]

Aber dafür müsste sie ein ganz klein wenig geändert werden, da sie nach meinen Beobachtungen nicht korrekt arbeitet.

kann ich nicht bestätigen.

1.
überprüfe ich durch HInsehen ob in der ausgeklappten Auswahlliste alle Werte auftauchen so tun sie das

2.
überprüfe ich ob, betreffend der Beispieldatei, die Formeln identisch sind, so sind sie das denn:

$Tabelle1.$A$1:$A$12

entspricht der formel:

INDIREKT("$Tabelle1.$A$1:$A$" & ANZAHL2($Tabelle1.$A$1:$A$1000))

weil

ANZAHL2($Tabelle1.$A$1:$A$1000)

als Ergebnis 12 liefert.


Datei jetzt anhängend.

Ohne den Korrekturfaktor +1 wird nämlich die letzte benutzte Zelle nicht berücksichtigt, wenn zuvor noch eine leerzelle vorhanden ist.

Das kann sein, nur brauche ich das garnicht zu überprüfen weil ja meine Formel ohnehin garnicht dafür gemacht/gedacht ist Leerzellen zu berücksictigen, denn das kann sie nicht. Hierbei ist es egal ob eine Leerzelle auftritt oder mehrere die innerhalb der Liste verstreut sind.

Ich verstehe somit Deinen Einwand nicht bzw. falls es darauf ankäme in den Bereich A1 bis A1000 beliebige Zellen zu belegen und beliebige Zelen leerzulasen (ganz egal ob eine oder mehrere und ganz egal ob am Anfang am Ende oder in der Mitte) ist meine Formel völlig ungeeignet, wobei ich allerdings auch nicht verstünde wieso für ein solches Szenario Deine Lösung besser wäre ODER ich kann genausogut argumentieren, falls du Dich NUR auf den konkreten Fall der letzten Zelle bezögest, wie Du darauf kämest gerade den anzunehmen, denn der ursprüngliche Frager hat eine Beispieldatei geliefert und an der ist meine Lösung orientiert.

(Bitte lies meine Argumentation sorgfältig, denn ich habe garnicht die Absicht mich vor einer Allgemeindiskussion zu drücken, nur ging es bisher um eine spezielle Aufgabe und Du kritisierst meine Lösung aufgrund einer gemachten Annahme die, soweit ich sehe, garnicht dieser Aufgabe entspricht.)


Am Rande:
meine Lösung war natürlich kein Gegenentwurf zu Deiner sondern nur dem Umstand geschuldet das ich hier im HIntergrund erst aslles vorschreibe und sich dadurch unsere Lösungen zeitlich überschrittten, was ich mit "leider nun quasi doppelt" zu kennzeichnen versucht habe. Das "quasi" sollte hierbei nicht andeuten das meine LÖsung besser wäre sondern nur das sie sich zwar unterscheidet aber gleichzeitig auf demselben Herangehen (der mit Werten belegte Bereich muß ermittelt werden) beruht.


Gruß
Stephan

[theindless]

Hallo miteinander,

danke für eure zahlreichen Lösungen... ich habe alle mal umgeschrieben (auf meine Daten; kommen aus unterschiedlichen Blättern) und getestet:

@Karolus

Code: Alles auswählen

VERSCHIEBUNG($Tabelle1.$A$1;0;0;ANZAHL2($Tabelle1.$A$1:$A$1000))

Deine Formel funktioniert ebenfalls gut.
In der Realdatei habe ich jedoch ein Macro, dass mir die Auswahlliste vorne, d.h. in Beispiel in Zelle A1 eine neue Zeile einfügt und diese Verschiebung wird von der VERSCHIEBUNG nicht berücksichtigt. Der neu eingefügte Eintrag ist somit in der Auswahl nicht vorhanden.

@Stephan:

Code: Alles auswählen

INDIREKT("$Tabelle1.$A$1:$A$" & ANZAHL2($Tabelle1.$A$1:$A$1000))

Das Beispiel war nicht so durchdacht, dass ich mir um Leerzeilen innerhalb des Gültigkeitsbereiches Gedanken gemacht hätte.
Tatsächlich ist es so, dass in meinem Fall die Einträge fortlaufend ohne Leerzeilen vorkommen.
Auch das per-Macro-Einfügen wird bestens verarbeitet.
Insofern ist deine Formel natürlich perfekt für diese Lösung.

@Balu

Code: Alles auswählen

INDIREKT(ADRESSE(1;SPALTE(A1);4; ;"Tabelle1")&":"&ADRESSE(MAX(ZEILE(A1:A1000)*(A1:A1000<>""));SPALTE(A1);4))

Auch deine Formel funktioniert bestens und ist damit perfekt geeignet.
Diese ist insbesondere für den Fall, wenn Leerzeilen vorkommen perfekt.

Ich danke euch nochmal allen Dreien.
Ich habe wieder was dazugelernt.

P.S.: Aufgrund anderweitiger Formelprobleme mit dem Macro tendiere ich dazu, neue Zellen hinten hinzuzufügen.

[Stephan]

Diese ist insbesondere für den Fall, wenn Leerzeilen vorkommen perfekt.

wenn das so ist erkläre bitte warum, denn ich verstehe es nicht.

LÖsche ich beispielsweise in der von Balu geposteten Datei den Eintrag in Zelle A10 und schaue mir danach die ausgeklappte Liste in Zelle C1 an klafft dort eine Lücke. Ist das so gewünscht?

Ändere ich nun in C1, betreffs DAten-Gültigkeit, die Anzeige von Nullwerten, erreiche ich genau das was hier im Thread bereits kritisiert wurde, nämlich es entsteht in der ausgeklappten Liste in Position 1 ein Leereintrag. Ist das so gewünscht?

Was betrachte ich hier falsch?
Warum wäre Balus Lösung für Leerzellen z.B. besser als mein Lösungsvorschlag? Ich sehe es nicht. Beide Lösungen sind, so wie ich es sehe, gleichgut aber nicht mehr.



Gruß
Stephan

[theindless]

Hallo Stephan,

Code: Alles auswählen

Beide Lösungen sind, so wie ich es sehe, gleichgut aber nicht mehr.

du hast recht, wie mein nochmaliger Test gezeigt hat.
In beiden Fällen taucht mitten in der Liste eine Leerzeile auf, sofern im Datenbereich eine vorhanden ist (bei mir nicht der Fall).

Danke für den Hinweis.