Kochrezepte: absolute Angaben aus relativen berechnen

[echo]

Danach: "Menüband Daten > Datenüberprüfung > [x] Leere Zellen ignorieren
Aber da in I17 ein Formel steht, gilt natürlich die Zelle nicht als leer.
Sie wird daher im Dropdownfeld als Eintrag angeboten.
Wählt ein Nutzer ihn aus, erhält er die Fehlermeldung #DIV/0!

Hallo
mein Excel 2019 kennt XVERWEIS() nicht, daher bekomme ich in deiner Liste eine Fehlermeldung

Die Datenüberprüfung könnte man lösen in dem man zuerst eine lückenlose Liste erzeugt und anschließend die Anzeige auf den relevanten Bereich begrenzt.

1. Per Formel eine lückenlose Liste erzeugt
2. Bereichsname erzeugt, Name: "Liste" > Bezieht sich auf: =BEREICH.VERSCHIEBEN($J$7;0;0;ZÄHLENWENN($J$7:$J$206;">@"))
3. Datengültigkeit: Bezieht sich auf: =Liste

Hier noch ein Link "Formular-Steuerelemente einfügen" als mögliche Alternative zum PullDown
https://www.youtube.com/watch?v=-BaItfVGxI0
Hinweis dazu: Bei gedrückter [ALT]-Taste richten sich die Steuerelemente beim positionieren an den Zellrahmen aus.
Gruß Holger

[ibu]

Die Datenüberprüfung könnte man lösen in dem man zuerst eine lückenlose Liste erzeugt und anschließend die Anzeige auf den relevanten Bereich begrenzt. [...]

Guten Morgen Holger.

Danke für diese Idee, ich muss mich da noch einarbeiten um das zu verstehen.

Dass für eine Lösung gleich zwei Hilfsspalten nötig sind, ist natürlich nicht so richtig schön. Aber das ist dann wohl

Hier noch ein Link "Formular-Steuerelemente einfügen" als mögliche Alternative zum PullDown
https://www.youtube.com/watch?v=-BaItfVGxI0
Hinweis dazu: Bei gedrückter [ALT]-Taste richten sich die Steuerelemente beim positionieren an den Zellrahmen aus.

Danke. Auch in der Mac-Version lassen sich diese Formular-Steuerelemente hinzufügen.

Aber:
Bei der Bedienbarkeit weicht MS von bewährten und üblichen Standards ab. Man kann nach dem Öffnen der Liste nicht mit den Pfeiltasten auswählen.

Im normalen Dropdown geht das.

Dieser Preis ist mir zu hoch, dann verzichte ich auf die dauerhafte Sichtbarkeit des grafischen Bedienelementes zum "Aufklappen". Schade.

---

1. Per Formel eine lückenlose Liste erzeugt

Du hast diese verwendet: =WENNFEHLER(@INDEX($I$7:$I$200;AGGREGAT(15;6;ZEILE($I$7:$I$200)-6*($I$7:$I$200<>"");ZEILE(A1)));"")

Das ist sehr komplex.

Gibt es wirklich keine anderen Techniken um eine lückenlose Liste zu erzeugen?

[echo]

Gibt es wirklich keine anderen Techniken um eine lückenlose Liste zu erzeugen?

Hallo

letztendlich geht es darum, dieses automatisch zu machen, das geht halt nur mit Formel oder Makro
Da gibt es verschiedene Formeln, ich kenne keine kürzere.

=AGGREGAT ist der Hauptbestandteil dieser Formel, und liefert nacheinander die Positionen der Einträge. Schau dir mal diesen Teil an:
(vielleicht mal in K7 rein kopieren und runter ziehen, danach mit dem Formeleditor (Fx) ansehen)
=AGGREGAT(15;6;ZEILE($I$7:$I$200)-6*($I$7:$I$200<>"");ZEILE(A1))

Die Position wird an Index übergeben und zeigt dann den Inhalt der Liste.
Gibt es nichts mehr anzuzeigen, unterdrückt wennfehler() die Fehlermeldung.

Die Hilfsspalten sind nicht zwingend notwendig. ich habe es so gemacht, damit es besser zu verstehen ist. 
Man kann auch alles in eine Formel zusammenbauen
Lückenloste Liste ohne irgend eine Hilfsspalte:

Code:

=WENNFEHLER(INDEX($C$7:$C$200;AGGREGAT(15;6;ZEILE($F$7:$F$200)-6*($F$7:$F$200<>"");ZEILE(A1)))&" "&INDEX($D$7:$D$200;AGGREGAT(15;6;ZEILE($F$7:$F$200)-6*($F$7:$F$200<>"");ZEILE(A1)));"")

Gruß Holger

[ibu]

Vielen Dank erneut lieber Holger.

Ich muss mich weiter belesen. Noch verstehe ich nicht, wie es ohne Hilfsspalte gehen soll.

Ich dachte bisher, dass ich die mindestens die Hilfsspalte I für XVERWEIS bzw. SVERWEIS benötige.

Auch das Konzept mit dem Bereichsnamen ist mir noch nicht klar.

[echo]

Ich muss mich weiter belesen. Noch verstehe ich nicht, wie es ohne Hilfsspalte gehen soll.

Ich glaube das ist ein Missverständnis, die Formel oben erzeugt eine Liste ohne Lücken, benötigt dafür natürlich einen Zellbereich (Hilfsspalt)

Auch das Konzept mit dem Bereichsnamen ist mir noch nicht klar.

Bereichsnahmen ersetzen Bereiche durch Klartext. Dadurch lassen sich u.a. Formeln besser lesen

Beispiel: D7:D17 in deiner Liste
Wenn du den Bereich markierst und links oben im "Namenfeld" Zutaten rein schreibst und die Eingabe mit Return bestätigst, 
kannst du "Zutaten" in jeder Formel oder Datengültigkeit als Bezug verwenden. Gleichzeitig ist es jetzt möglich diesen Bereich dort übers PullDown-Menü anzuwählen.
Anpassung über den Namenmanager

Ersetze ich jetzt diesen festen Bezug wie oben im Namenmanger durch eine Formel lässt sich der Bereich definieren.
So muss zur Arbeit
Gruß Holger

[ibu]

Edit: Ich hatte etwas Falsches zu benannten Bereichen in der Macversion geschrieben.

Einen Post löschen ging leider nicht.

[steve1da]

Also in meinem Excel for Mac 365 findet man das unter Formeln - Namen definieren.

[ibu]

Also in meinem Excel for Mac 365 findet man das unter Formeln - Namen definieren.

Das gibt es in Excel für Windows auch. Aber es nicht vergleichbar mit dem übersichtlichen Namensmanager von Windows.


Darf ich bitte zu Bereichsnamen noch etwas fragen?

Gegeben seien die Formeln
I7=C7&" "&D7
I8=C8&" "&D8

Nun möchte ich Lesbarkeit durch einen Bereichsnamen verbessern.

Also weise ich C7:C16 den Bereichsnamen "_Nummerierung" und D7:D16 den Bereichsnamen "_Zutat" zu.


Mit welcher Syntax kombiniert man nun die Zeilennummer mit dem Namen?

Ich habe es bisher nicht rausgefunden.

[schauan]

Hallöchen,

mit INDEX. C7 wäre im Bereich dann Zeile 1 / Spalte 1.

[ibu]

Hallo Holger,

ich habe jetzt Folgendes getan:

* Einbau Deiner Hilfsspalte und Formeln aus #11

* Alle Zellbereiche und Zellen, die für Formeln verwendet werden, habe ich benannt und diese Namen auch in die Formeln integriert

* Bei allen Formatierungen, wo dies möglich ist, habe ich Zellenformatvorlagen angewendet


Resultat:
Klappt : )


Wirklich verstanden habe ich jedoch weder Deine Formel in J noch die Formel und das Verfahren bei der Datenüberprüfung.
Oder auch den Nutzen der langen Formel aus #13.

Kleinigkeit:
In J16 erscheint eine "0" als Resultat der Formel. Den Grund habe nicht rausbekommen.


Alle wesentlichen Ziele für den Rezeptvorlagen-Prototyp sind nun verwirklicht.

Ganz lieben Dank nochmal für Deine Geduld.


  parameterrezept-template-0.3.xlsx (Größe: 14,11 KB / Downloads: 0)

---

mit INDEX. C7 wäre im Bereich dann Zeile 1 / Spalte 1.

Danke. Aber dessen längliche Syntax
INDEX(Matrix;Zeile;[Spalte])
verbessert meinem Empfinden nach die Lesbarkeit einer Formel nicht.

Statt "F7" wäre es dann "INDEX(_Skaliert;7)"

Nutzt ihr das in Kombination mit benannten Zellbereichen?