ich muss sagen Excel kann auch Spaß machen, hätte ich nicht gedacht und es gibt viele nützliche Anwendungsbereiche für den Alltag.
Ich habe es fast hinbekommen wie ich es mir vorgestellt habe, auch die Dropdown-Liste: Dynamisch erweitert und sortiert von Martin Weiß habe ich fast komplett ungesetzt.
Es fehlt noch eine Kleinigkeit, der automatische Neueintrag, wenn ich auf dem Blatt "Juni 2020" in in Spalte B nicht einen Eintrag aus dem Dropdown Menue nehme, sondern einen neuen Artikeltext eingebe. Dieser Text sollte eigentlich auf dem Blatt "Lebensmittel Kalorien" in Spalte A unterhalb von 103 "Zwiebeln" angehängt werden. Durch den Hinweis und auch durch die bedingte Formatierung, werde ich dann erinnert noch die Kalorien auf dem Blatt "Lebensmittel Kalorien" in Spalte B am Ende der Liste einzutragen. Dann einmal Strg Alt F5 und alles ist geritzt. Es ist ja durch die Pivot-Tabelle egal ob die Lebensmittel in Spalte A und B alphabetisch sortiert sind. :19:
Ich kann verstehen was da noch nicht stimmt, beim Artikel von Martin Weiß, befinden sich ja alle Spalten auf einem Blatt, wie ich das nun aber auf zwei Blättern realisieren kann, entgeht meiner geringen Erfahrung.
Vielleicht könnte Ihr nochmal schauen.
Wenn das durch ist schaue ich mir auch noch die Sachen mit dem Datum an, die Rudi'S vorgeschlagen hat und natürlich die Verbesserungen von Fred. Diese habe ich aber erst gelesen, als ich schon fertig war.
Zitat:Es fehlt noch eine Kleinigkeit, der automatische Neueintrag, ...
Diese "Kleinigkeit" geht in deinem Fall so nicht !
in dem Beispiel von Martin Weiss ist die Liste, in der per Dropdown Daten eingetragen werden gleichzeitig auch die Liste , die der Dropdown-Auswahl (indirekt, da noch die Pivot-Liste dazwischengeschaltet ist) zugrunde liegt Deshalb funktioniert das da.
Aber das ist bei dir ja anders, bei dir sind das ja völlig getrennte Bereiche. Es ist daher nicht möglich, dass wenn du im "Juni" (oder in einem anderen Monat) ein Lebensmittel einträgst, das noch nicht in der Dropdownliste enthalten ist, sich dieses Lebensmittel automatisch im Tabellenblatt "Lebensmittel Kalorien" in Spalte A automatisch einträgt.
Dafür brauchst du dann VBA, da geht sowas, und zwar mit dem Worksheet_Change - Ereignis
Folgendes wäre zu tun: 1. mit Alt+F11 den Visual Basic-Editor öffnen Alternativ: rechtsklick auf dem Blattregister "Juni" => Code anzeigen 2. im Tabellenblatt-Modul vom "Juni" den folgenden VBA-Code eingeben (=> reinkopieren) (in den Folgemonaten analog bzw. wenn du für di Folgemonate das Tabellenblatt des Juni kopierst, wird der VBA-Code automatisch mitkopiert)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:B600")) Is Nothing Then
Dim lZeile As Integer lZeile = Worksheets("Lebensmittel Kalorien").Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Worksheets("Lebensmittel Kalorien").Range("A2:A" & lZeile), Target.Value) = 0 Then Worksheets("Lebensmittel Kalorien").Range("A" & lZeile + 1).Value = Target.Value End If End If
End Sub
Der Code prüft bei jedem Eintrag im Bereich B4:B600 mit der Funktion Zählenwenn, ob das eingetragene Lebensmittel in der Liste auf dem Tabellenblatt "Lebensmittel Kalorien" in Spalte A bereits vorhanden ist oder nicht. Falls nicht, wird es auf dem Tabellenblatt "Lebensmittel Kalorien" in Spalte A unten ergänzt.
Wichtig: da in deiner Excel-Datei jetzt VBA-Code enthalten ist musst du sie als .xlsm abspeichern
Gruß Fred
Folgende(r) 2 Nutzer sagen Danke an Fred11 für diesen Beitrag:2 Nutzer sagen Danke an Fred11 für diesen Beitrag 28 • Rudi'S, Franky jr.
Einen schönen Sonntag an Alle und besonders an Fred,
Wow Wow Wow, das ist ja mal ein dickes Ding, echt super. Jetzt macht es noch mehr Spaß das Ernährungstagebuch zu führen. Jetzt noch ein Parr goldene Ohren dran programmiert und fertig ist die App fürs Handy.
Fred sag nicht Du kannst auch eine App fürs Handy programmieren?
Ich weiß da gibt es hunderte, ABER alles nur Datensammler und Werbeschleudern. Ich habe natürlich zu aller erst im Google Play Store nach einem Ernährungstagebuch gesucht und bin auch fündig geworden, nur leider sind die Programmierer nicht mehr zu erreichen und die App wird nicht mehr Supported. Die App heiß "Ernährungstagebuch" o. "My daily bits LCC".
Alles sehr klar strukturiert und leicht zu bedienen, ganz ohne Schnick Schnack, z.B. ein Avatar und die Datenweitergabe an alle die mir etwas verkaufen wollen. Leider werden die Daten nicht in der eigenen Cloud gespeichert und man hat keinen Zugriff vom Windows PC auf die Daten mit einer Windows App. Leider kann man keine neuen Lebensmittel anlegen oder die alten verändern. Die Entwickler hatten an viele nützliche Sachen gedacht, nach meiner Meinung, warum nichts draus geworden ist, ist mir schleierhaft.
Aber mit Deinen tollen Lösungen ist die Excel Tabelle auch top und ich kann Sie ja ohne Probleme auch auf dem Smart Phone nutzen. Dies ist mal wirklich ein Smarte Nutzung fürs Handy und macht dem Namen alle ehre.
Danke nochmal an Alle, ich werde jetzt mal die restlichen Monate anlegen und denke das wars dann mit dem Projekt "Lebensmittel Tagebuch" Ach die Sache mit dem Datum schaue ich mir jetzt auch noch an.
wie sich "Klein Frizchen", also so ähnlich wie ich, es sich vorstellt mit der Vereinfachung, habe ich noch einen Formelvorschlag für dich.
Vorweg empfehle ich, wenn du diese Datei auch in den Folgejahren, nicht zum Abnehmen - sondern zur Überwachung, nutzen möchtest, mache dir, wenn du alles so hast wie gewünscht, eine sogenannte Blaupause, die du dann für jedes Jahr kopieren kannst. (Ist einfacher als immer alle Einträge löschen zu müssen.)
Im Januar in Zelle G1 schreibst du den letzten Gewichtswert des Vorjahres, und in den anderen Monaten in Zelle G1 diese Formel:
(wobei 600 sich auf ca. 30 Tage mit ca. 20 Einträgen bezieht, ansonsten Wert erhöhen) Auch hier ist dann wieder jeden Monat der Bezug auf den Vormonat zu ändern)
Und in die Spalten H (also Zelle H4 und dann nach unten kopieren) eines jeden Monats schreibst du dann diese Formel:
=WENN(G4="";"";G4-VERWEIS(9^9;G$1:$G3))
Damit brauchst du auch nicht mehr die Differenzen berechnen.
Gruß Rudi
Folgende(r) 1 Nutzer sagt Danke an Rudi'S für diesen Beitrag:1 Nutzer sagt Danke an Rudi'S für diesen Beitrag 28 • Franky jr.
jetzt sind wir schon bei den goldenen Ohren denke ich, aber die machen erst recht Spaß!
Ich habe Deine Vorschläge getestet, leider ist das erst silber, der erste Wert also im Januar sollte in Zelle G4 stehen oder bei meiner Tabelle im Mai G4 als Zahl mit einer Nachkommastelle und nicht außerhalb der "Tabelle" und die Veränderung rechnet sich nicht nur beim ersten Mal von diesem Wert, danach immer vom Wert der Vorwoche, also z.B. auf dem Blatt Juni G4 und die Veränderung ist gleich Mai G402 minus Juni G4. Dann geht es weiter mit Juni G4 minus Juni 99 usw. Natürlich könnte man noch eine Spalte hinzufügen in der dann die Gesamt-Veränderung zum Ausgangswert im Feld G4 Januar (G4 Mai) ausgewiesen wird. Sicherlich auch sehr nützlich.
Dann würde ich noch zwei oder drei Farbregeln einbauen, wenn der Wert in den Spalten G, H und I sich "Positiv" verändert also ein Minus als Vorzeichen hat oder in Spalte G kleiner ist als der letzte Wert den ich eingetragen habe, sollte die Schrift Grün sein, keine Veränderung Schwarz und ein Zunahme Rot. Wenn in Spalte I ein Roter Wert auftaucht, sollte der Computer mir eine runter hauen.
Dann ist das rechte Ohr perfekt und am linken Ohr ist es nur noch eine Kleinigkeit, denke ich. "MAN" also Du, ich kann das leider noch nicht könnte die Eingabe des Datum noch automatisieren. Der erste Tag im Monat ist ja fix auf dem Blatt z.B. Juni und dann könnte ich mir vorstellen, immer wenn minimum eine Leerzeile zwischen der letzten Eingabe ist, wird ein neuer Tag eingetragen, im Format wie in der Tabelle. (Montag, 1.Juni 2020) und immer Montags kommt dann noch ein Zelle darunter die KW als Zahl und dem Text "KW" dahinter in Fett.
Wie schön kann so ein trüber Tag mit Excel sein.
Ich habe den letzten Stand nochmal mit hoch geladen, die Monate Juli bis Dezember habe ich wieder gelöscht, diese werde ich auch erst einfügen wenn die Tabelle wirklich fertig ist und dann mache ich auch noch ein Blaupause. :21:
in deinen vorigen Beispielen hast du für den neuen Tag immer 2 freie Zeilen in Spalte B gehabt, darauf basiert die Formel und funktioniert. Für den ersten Tag im neuen Monat ist zusätzlich der Eintrag für die KW vorgesehen, damit du weißt in welcher Woche du bist. Wenn es dir nicht gefällt, entferne es aus der Formel.
Deine Farbgestaltung bekommst du alleine über die bedingte Formatierung geregelt. Auch deine fett geschrieben KW regelst du über bedingte Formatierung (Neue Regeln-Nur Zellen formatieren, die enthalten und dann eingeben (nur Zellen formatieren mit: in der 1. Wahl Bestimmten Text weiter endend mit und dann ="KW" eingeben. und wird angewendet auf =$A$4.$A$600. UND das für jedes Arbeitsblatt -logisch.
Der Wert für die Messung des Gewichtes ist bewusst nach außerhalb verlegt worden. Weil, ich gehe davon aus, dass auch mal am 1. Tag Messungen vorgenommen werden. Aber dann ist kein Bezug zum Vormonat möglich (mit meinen Formelkenntnissen). Den Wert kannst du ja mit der Schriftfarbe weiß unkenntlich machen.
Für deine neue Spalte wählst du die Formel die immer den Bezug zum 1. Eintrag hat, hier also für die 1. Zelle I4:
=G4-'Mai 2020'!$G$4
Diese Formel in allen Monaten einfügen und jeweils nach unten kopieren.
Wenn du die Spalten H und I mit einer Nachkommastelle haben willst, regelst du das über "Start" und dem Unterbegriff "Zahlen".
danke für die vielen Anleitungen, ich habe ein Blatt Januar angelegt und den Bezug zum Anfangswert/Gewicht zu haben, alle Berechnungen sind einwandfrei in den Spalten G H und I, gefällt mir schon richtig gut. Bedingte Formatierung sieht sehr einfach aus, ABER funktioniert nur wenn der Wert wenn der Wert = dem Bezug ist und dann passiert nicht in der Zelle, oder der Wert kleiner 0 ist dann wird die Zelle Grün. Wenn nichts eingetragen ist in G sind alle Zellen Rot, wenn ich einen Wert eintrage der größer ist als G1, dann ist die Zelle in Spalte H auch rot und in Spalte I Grün solange der Wert kleiner ist als der Bezug vom Anfang. Ich habe es mit gleich/größer probiert, denke aber es liegt an der Formel in der Zelle oder ?
Mit der Spalte Datum komme ich noch nicht zurecht da ich die Formel und was sie bewirken soll nicht richtig verstehe. Du wolltest doch automatisch bei der Auswahl eines Produkt in B4 in der Zelle A4 das Datum eintragen und in A5 die KW und später dann, immer wenn eine leere Zeile nach einem Tag kommt wieder das Datum in A oder?
Vielleicht kannst Du nochmal in meine Datei reinschauen, ich such mal weiter nach Erklärungen und YouTube Videos zu Bedingte Formatierung. So schwer kann das doch nicht sein.
Was mir noch durch den Kopf geht ist die Spalte Summe Tag, ich schwanke zwischen einem festen Wert von z.B. 30 Lebensmitteln und dann einer automatische Summierung und der händischen Lösung und dadurch einer kompakteren Tabelle die gleichmäßig formatiert ist, also immer zwei Zeinen zwischen den Tagen. Es sind ja nur weinige Klicks und schon hat man die Summierung.
Selbst nach einigen Videos sehe ich kein Land, ich denke es liegt vielleicht an der Wenn= Formel, wenn ich die drei Regeln nur für die Zeile H4 anwende klappt es einwandfrei. Sobald ich bei der Roten Formatierungsregel den ganzen Bereich eintrage von =$H$4:$H$600 wir die ganze Spalte Rot.
Zellwert = 0 Kein Format festlegen =$H$4:$H$600 Zellwert < 0 Grün festlegen =$H$4:$H$600 Zellwert > 0 Rot festlegen =$H$4:$H$600
Sollte doch eigentlich ganz einfach sein oder versteht Excel mich nicht?
Zwei weitere Sachen frage ich mich, ob die farbliche Gestaltung der Spalten nur über den Schritt "Als Tabelle formatieren" möglich ist und dann weiter wenn man gar keine Tabelle brauch diese wieder in einen Bereich umzuwandeln? Wenn man wie in meiner Tabelle je Spalte eine andere Farbe haben will, entstehen ja 9 Tabellen und dann spring der Cursor nicht mehr nach Rechts sondern immer nach Unten in der Tabelle. Wenn ich mehrere Spalte gleichzeitig in eine Tabelle umwandele, habe ich immer die gleiche Farbgestaltung.
Ist es ratsam einige Spalten später zu sperren? z.B. C Spalte und die E Spalte, sowie H und I? Beeinflusst das Sperren auch den Tab-Sprung, so das nur Felder angesprungen werden in denen eine Eingabe möglich ist?
in der bedingten Formatierung nimmst du bei Zellen formatieren, die enthalten für den 1. Parameter nur Zellen formatieren: Zellwert für den 2. Parameter: kleiner als und als Formel beim 3- Parameter: =0 deine Füllfarbe wählst du dann aus und für den Gültigkeitsbereich (wird angewendet auf.) trägst du ein: =$H$4:$I$600 und schickst das mit "Übernehmen" ab.
Für den 2. Fall (Wert in Spalte G ist positiv wählst du unter neue Regel den Befehl Formel zur Ermittlung .......... aus und gibst als Regel ein: =und(H4>0;H4<999) Ansonsten wie oben beschrieben weiter machen.
Datum und KW werden immer dann eingetragen, wenn du in der Spalte B zwischen den Eingaben 2 Leerzellen in den Einträgen hast.
Also, wenn du die Formeln so übernommen hast (kopiert hast, dann könnten sie ohne Fehler sein) dann passiertes so wie deine Ausgangslage war. Ansonsten gehe doch mal über die PN und wir stellen dann dein gewünschtes Ergebnis hier wieder ein.
![[-]](https://www.clever-excel-forum.de/images/collapse.png)