Dropdown Menue SVERWEIS Formeln kopieren

[Rudi'S]

Hi Franky jr.,

damit du dich doch nur noch um die Eingaben deiner Kalorien kümmern musst, und du immer 2 Leerzeilen zwischen den einzelnen Tagen hast, schlage ich für die Zellen A4 die Formel 
=WENN(TAG(MAX('Mai 2020'!A4:A600))<29;"";MAX('Mai 2020'!A4:A600)+1) [hier bezieht sich die Formel auf die Zelle A4 im Monat Juni, d.h., leider der Bezug jeweils zum Vormonat hergestellt werden muss, also für den Monat Juli zum Vormonat Juni, usw]

und für alle anderen Zellen in Spalte A die Formel
=WENN(UND(B2<>"";B3="";B4="";B5<>"");MAX(A$4:A4)+1;WENN(ODER(A4="";RECHTS(A4;2)="KW");"";WENN(ODER(ZEILE()=5;WOCHENTAG(A4)=2);KALENDERWOCHE(A4;21)&" KW";"")))

(Formeln in Zelle A5 eintragen und nach unten kopieren.)

vor. 
Allerdings muss der erste Tag eines neuen Jahre händig in der Zelle A4 der ersten Erfassung eingetragen werden.

Obige Formeln lassen sich von Profis bestimmt noch verkürzen, aber so funktionieren sie schon mal.

Gruß Rudi

[Franky jr.]

Hallo Rudi'S,

ich hatte noch gar nicht über eine Verbesserung der Spalte A nachgedacht, aber danke ich werde es mal testen.

Ich mag es halt sehr einfach und etwas automatisiert, wenn möglich.

Mein eigentliches Problem ist die Eingabe der Lebensmittel und da der Punkt das sich meine Tabelle ja immer weiter vergrößert und
ich bisher ständig über "Daten" "Datenprüfung" den Bereich erweitern muss, von ='Lebensmittel Kalorien'!$A$2:$A$93 auf z.B. ='Lebensmittel Kalorien'!$A$2:$A$95,
wenn zwei Lebensmittel dazugekommen sind, es hat mal automatisch funktioniert, macht es aber nicht mehr.

Vielleicht habe ich in den Optionen etwas verstellt?? Denn normalerweise sollte sich der Bereich automatisch erweitern, da ich ja die Spalten im
Blatt "Lebensmittel Kalorien" A und B in eine Tabelle umgewandelt habe. So hatte ich es mir auf den Seiten von Microsoft und anderen Quellen angelesen.

In meiner Unzufriedenheit habe ich einen Interessanten Beitrag auf der Seite von Martin Weiß der Tabellen Experte gefunden,
Dropdown-Liste: Dynamisch erweitert und sortiert dies hörte sich für mich sehr interessant an und hätte ja main Problem lösen können.

Jetzt aktualisiert sich die Pivot-Tabelle, aber die anderen Funktionen sind noch nicht da, automatische Neueintrag der Spalte A auf dem Blatt
"Lebensmittel Kalorien" z.B. und ich kann bisher nicht den  Formelnamen "Dropdown" bei der Datenüberprüfung - Einstellungen Liste und Quelle verwenden.
"Dropdown" wird zwar nach dem drücken von F3 angezeigt, nach der Auswahl kommt dann aber eine Fehlermeldung.
 
Ich hoffe Fred schaut sich die Sache auch nochmal an, ich komme ja zurecht mit der Tabelle, aber es ist noch Raum für Verbesserungen.

Danke

[Rudi'S]

Hi,

fiel mir gerade ein. Wenn am Monatsende noch ein Eintrag vorgenommen werden sollte (z.B. vergessen, dass der Februar statt 29 nur 28 Tage hat), würde dieser vielleicht unbemerkt als neuer Monat eingetragen und damit der Folgemonat nicht mit dem 1. Tag beginnen, deshalb die folgende Formelergänzung mit dem Hinweis, dass ein neuer Monat beginnt:

=WENN(UND(B5<>"";MONAT(MAX(A$4:A4))<>MONAT(MAX(A$4:A4)+1));"ACHTUNG - neuer Monat";WENN(UND(B2<>"";B3="";B4="";B5<>"");MAX(A$4:A4)+1;WENN(ODER(A4="";RECHTS(A4;2)="KW");"";
WENN(ODER(ZEILE()=5;WOCHENTAG(A4)=2);KALENDERWOCHE(A4;21)&" KW";""))))

Sorry, hätte ich gleich dran denken sollen, aber learning by doing.

Gruß Rudi

[Rudi'S]

Hi Franky,
kann es sein, dass du die Tabelle umbenannt hast?
Mindestens beziehst du dich auf die feste Listeneinstellung von Zelle A2 bis Zelle A93. erstellst aber als Formel "Dropdown" mit dem Bezug auf die Spalte D. Da solltest du doch noch mal schauen.
Gruß

[Rudi'S]

....

=BEREICH.VERSCHIEBEN('Lebensmittel Kalorien'!$A$2;0;0;ANZAHL2('Lebensmittel Kalorien'!$A:$A);1)

mit dieser Formel in Formel --> Namensmanager  ---> unter Dropdown anlegen 

und in Daten --> Datentools --> Datenüberprüfung --> Liste --> = Dropdown --> OK

läuft es (mindestens) bei mir.

Vielleicht solltest du überlegen, mit Anfangsbuchstaben (z.B. A-E F -H oä.) eine erste Dropdownliste zu erstellen und mit dieser Auswahl eine zweite Dropdownliste für die weiterer Auswahl aufzurufen (damit könnte das Scrollen kürzer werden).

Gruß Rudi

[Fred11]

Hallo Franky,

ich habe mir deine aktuelle Datei angeschaut.

wie Rudi dir bereits geschrieben hat:
du hast das Tabellenblatt, in dem die Daten für dein Dropdown stehen offensichtlich umbenannt von "Lebensmittel" auf "Lebensmittel Kalorien".

Du musst die im Namensmanager für den Namen "Dropdown" definierte Formel entsprechend anpassen - das geschieht hier nämlich nicht automatisch.

Das ist die Ursache für deine Fehlermeldung ("Die Quelle untersucht gerade eine möglichen Fehler…"), weil Excel das Tabellenblatt "Lebensmittel" sucht und nicht findet.

Wenn du in der Formel für "Dropdown" den Tabellenblattname anpasst, sollte alles wieder funktionieren.
(da der Tabellenblattname jetzt ein Leerzeichen enthält, musst du ihn in einfache Anführungszeichen ' setzen - siehe Beitrag #15 von Rudi...)


Gruß
Fred

[Franky jr.]

Guten Morgen,

danke für die Informationen, da ist wirklich was schief gelaufen bei all den Versuchen. "Lebenslmittel Kalorien" ist ein Blatt und
"Lebensmittel" war der Name einer Tabelle auf dem Blatt und die Verknüpfung im Names Manager ließ sich nicht löschen.

Lange Rede kurzer Sinn, ich habe eine neu Datei erstellt und nur die Werte übernommen, das Blatt "Lebensmittel Kalorien" hat jetzt
nur eine Tabelle mit zwei Spalte, das Blatt Mai 2020 beinhaltet nur Werte und sonst nichts. 

Das Blatt Juni 2020 habe ich auch ganz neu aufgebaut und erst einmal nur die Werte übernommen und das Aussehen formatiert. (Farbe)
Ab Zeile 190 habe ich Spalte B als Dropdown Menue erstellt und dann noch je eine Formel in C und E.

Es ist zum Mäuse melken, ich kann auf dem Blatt Lebensmittel erweitern was ich will, also unterhalb von "Zwiebel" A103, 
es wird im Dropdown Menue nicht übernommen, so wie es in den Anleitungen immer beschrieben wird.   Zork Point Narf 
Ich hatte gehofft, wenn ich mit einer neuen Datei anfange, klappt das wenigsten wieder.

Was mich auch noch beschäftigt ist, die farbliche Gestaltung scheint nur Möglich zu sein wenn man eine oder mehrere Spalten in
ein Tabelle umwandelt, auf dem Blatt Juni 2020 brauche ich aber keine Tabelle. Ist das der richtige Weg, zuerst eine Tabelle mit Farbe 
erstellen und dann wieder in Tabellenentwurf in einen Bereich konvertieren?

An die Erweiterung mit der Pivot-Tabelle werde ich mich noch mal rantrauen, aber erst wenn das Aktualisieren wieder klappt.
Ich lade die original Datei noch mal hoch, mit Gewicht, aber wehe einer sagt ich bin dick, bin ich nicht nur Plüschig.  :05:


Danke schon mal im Voraus.

[Rudi'S]

Hallo Franky,

Guten Morgen!

Warum hast du alles gelöscht un d neu erstellt? Ok, das musst du wissen.
Auf jeden Fall schau dir noch mal den Beitrag #15 an. 

Du hast ja jetzt keinen Bezug mehr für dein Dropdown.

Vertraue auch Fred, der dir diesen Schritt nahegelegt hat.
Gruß Rudi

[Fred11]

Hallo Franky,

die intelligente Tabelle auf dem Tabellenblatt "Lebensmittel Kalorien" darf nicht bis zur allerletzten Excelzeile gehen, sondern nur so groß sein wie dein Datenbereich.
Das Wesen von intelligenten Tabellen, ist, dass sie sich, sobald ein neuer Eintrag unten dazukommt automatisch erweitert.
(Und wenn du sie von vornherein schon bis Zeile 1048567 geht, macht das keinen Sinn)

Und wenn du jetzt als Datenbasis für dein Dropdown eine intelligente Tabelle verwendest (ohne Pivot....), brauchst du auch keine benannte Formel mit BEREICH.VERCHIEBEN….

Vorgehensweise (nachdem die intelligente Tabelle richtiggestellt wurde):
1. markiere den Bereich von A2 bis A103 (oder so weit, wie halt die Tabelle geht), also ohne die Überschrift
2. Klicke in das Namenfeld (oben links) und vergebe für diesen Datenbereich einen Namen, z.B. Dropdown (Eingabe abschliessen mit Enter),
3. In der Datenüberprüfung:
zulassen => Liste
im Feld Quelle gibst du folgendes ein:
=Dropdown

Fertig

jetzt passt sich das Dropdown automatisch an - siehe angehänget Beispieldatei


Gruß
Fred

[Rudi'S]

Hi,

im Anhang mal die Datei wie sie bei mir lauffähig ist.

Gruß