Excel / Wie Zellen sperren WENN in Folgeregister?

Dieser Thread ist Teil einer Diskussion zu einem Artikel:  Zum News-Artikel gehen

Galapapagos

Stammgast
Hallo
Ich habe eine Exceltabelle mit 8 Registern zur Berechnung der Lieferkosten.
Diverse Abteilungen können dort eintragen, wann an wen wieviel geliefert wurde.
Die ersten 7 Register sind die Wochentage, dann folgt der Zusammenzug auf Blatt 8 für die Rechnungsstellung.
In den ersten 20 Zeilen habe ich immer die gleichen Kunden, diese Zellen habe ich gesperrt. Die Kunden können nicht überschrieben werden.
Nun möchte ich verhindern, dass auf Zeile 21 am Dienstag der Kunde Meier eingetragen werden kann, wenn dort am Montag schon Huber manuell dazugefügt wurde. Dann soll Meier am Dienstag erst auf Zeile 22 eingetragen werden können usw.
Wie kann ich das machen/sperren?

Zudem soll dann auf Register 8 im Zusammenzug auf Zeile 21 Meier angezeigt werden und auf Zeile 22 Huber. Wie kann ich das machen? Ich weiss ja noch nicht, an welchem Tag die Zelle relevant ist für den Zusammenzug. Kann die gesperrte Zelle dann in den Folgeregistern auch gleich noch eingefärbt werden?

Für eure Hilfe bin ich dankbar, damit ich nicht jede Wochen in den Registern rumblättern muss um zu erkennen, wer was erhalten hat.
Dank im Voraus und beste Grüsse.
🙏
 

nochEinAndreas

Stammgast
Hallo Galapapagos,

zu meinem Verständnis: Ab Zeile 21 darf eine bestimmte Zeile immer nur in einem Blatt gefüllt werden?

Was ich nicht verstehe: Auf dem Montagsblatt steht Huber in Zeile 21. Auf dem Dienstagsblatt steht Meier in Zeile 22. Warum ist das in Register 8 genau anders rum?

Wenn ich dich richtig verstanden habe (den ersten Teil), geht das glaube ich nur mit einem Makro. Wenn du deine Mappe (oder ein Fakebeispiel) hochladen könntest, würde ich versuchen, dir einen Makro zu schreiben.

Grüße, Andreas
 

Thomas Ramel

Stammgast
....und warum machst Du das alles auf verschiedenen Tabellenblättern?

Erfasse alles in einer intelligenten Liste/Tabekle, jeden Eintrag eine Zeile mit Datum.

Dann kannst Du oriblemlos z.B. mit einer Oivot-Tabbelle deine Auswertungen und Zusammenzüge erstellen.

Herzliche Grüsse
Thomas Ramel
 

Turakos

Stammgast
Hallo guten Tag

Das Vernünftigste ist aus dem Vorschlag von Thomas Ramel abzuleiten.

In einer Arbeitsmappe eine Tabellenblatt erstellen - so quasi als Journal - mit allen Datensätzen in "sauberer" und auswertbarer Datenstruktur.
Diese Ausführung ermöglicht eine Auswertung mit Pivot-Table und/oder Power Query - es ist einfacher für eine Auswertung die Datensätze mit allen Eingabe(Input)-Daten aus einem Tabellenblatt zulesen, als diese Input-Daten mühsam aus mehreren(!) Tabellenblättern zusammen zusuchen.

Freundliche Grüsse
Turakos
 

Galapapagos

Stammgast
Hallo zusammen
Besten Dank für die Feedbacks.
Bitte entschuldigt, hatte die beiden Namen Huber und Meier mit den Zeilennummern verwechselt.
Die Anregungen von Thomas und Turakos kann ich nachvollziehen. Da es jedoch ein bestehendes Instrument in unseren Abteilungen ist, möchte ich den Wechsel wenn möglich vermeiden.
Zur Frage von Andreas. Im Anhang das Muster: Ich habe auf Zeile 25 (Seite 1) am Montag den Kunden Huber eingetragen. Alle Folgezellen auf Zeile 25 (Seiten 2-7) sollen in dieser Woche dann gesperrt sein, damit beim Zusammenzug auf Blatt Seite 8 nur Huber und seine Beträge erscheinen.
Auf Zeile 26 habe ich dann am Dienstag Meier eingetragen. Die Felder auf Zeile 26 sollen demnach an den Folgetagen Mi/Do/Fr/Sa/So gesperrt sein und das Total von Meier in Zeile 26 auf Blatt 8 stehen.
Für eure Hilfe bin ich sehr dankbar.
Beste Grüsse und Danke im Voraus. Reto
 

Anhänge

  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle.pdf
    607,1 KB · Aufrufe: 11

nochEinAndreas

Stammgast
Guten Abend Galapapagos,
mit einem Bild (oder pdf) ist mir leider nicht so sehr gedient. Um einen Makro schreiben zu können, brauche ich eine Excel-Datei.
Und wie ist das?: Wenn auf einem Blatt (z.B. Mittwoch) ein Eintrag für einen Kunden erfolgt; sind dann nur Do-So danach gesperrt oder auch Mo-Di davor?

Grüße, Andreas
 

Galapapagos

Stammgast
Guten Tag Andreas
Danke, dann versuche ich das nochmals mit Excel anzuhängen. Kam am Freitag die Meldung, dass Format Excel nicht gehe.
Zu deiner Frage. Hauptsache ist, dass dann die Folgetage gesperrt sind, da es vorwiegend tagesaktuell eingetragen wird und nicht als Reservation. Wenn die Vortage aber auch gesperrt werden können, ist das sicher toll.
Besten Dank und Gruss
Reto
PS. tut mir leid, aber ich kriege Excel nicht als Anlage dazu. Vielleicht hier als Downloadlink von wetransfer?
https://wetransfer.com/downloads/c51a859619f1bde0db4990154082781a20201123093429/4e153daf3282906f8962dbf49387665f20201123093500/89ee06
 

nochEinAndreas

Stammgast
Danke Galapapagos für das Hochladen bei wetransfer. Diesen Dienst möchte ich aber nur ungern nutzen, bevor ich die ganzen AGBs durchgelesen habe, und das dauert mit gerade etwas zu lang.
Kannst du bitte, wie slup empfohlen hat die Datei umbenennen und hier hochladen? Danke.
Übrigens, wen es interessiert, und wer es noch nicht weiß: xlsx-Dateien sind eigentlich schon zip-Dateien. Wenn man sie entsprechend umbenennt und sie dann (mit eine Zip-Programm oder mit Windows-Bordmitteln) öffnet, kommt man an ihre Innereien. Das sieht recht interessant aus.

Gruß, Andreas
 

Galapapagos

Stammgast
Hola
Geht es so?
Gruss Reto
 

Anhänge

  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle.zip
    46,7 KB · Aufrufe: 6
  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle.txt
    7,6 KB · Aufrufe: 4

nochEinAndreas

Stammgast
Danke Reto,

die ZIP-Datei ist in Ordnung. Bei der TXT-Datei ist allerdings irgendwas schief gelaufen: Wenn man sie in XLSX umbenennt und öffnen will, motzt Excel, dass es sie nicht aufmachen kann. Aber egal, die ZIP-Datei geht ja.

Gruß, Andreas
 

nochEinAndreas

Stammgast
Hallo Reto,

hier der erste Versuch eines Makros. Er funktioniert folgendermaßen:
Wenn du auf einem der Wochentagsblätter im Bereich A25:B30 etwas änderst, geschieht Folgendes:
  • Die Änderung wird auf das Zusammenfassungsblatt kopiert.
  • War die Änderung ein Kundeneintrag (Spalte A), dann werden in der selben Zeile in den Wochentagsblättern davor und dahinter die Zellen A und B gesperrt. D.h. sie werden mit rotem Text "gesperrt!" versehen und werden auch wirklich gesperrt, können also nicht beschrieben werden.
  • War die Änderung ein Leeren der Zelle in Spalte A, dann werden die Sperrungen auf den Wochentagsblättern davor und dahinter aufgehoben, der "gesperrt!" Text wird gelöscht.
Ich habe noch so einige Bedingungen eingebaut, die der Makro überprüft, bevor er loslegt:
  • Nur Änderungen auf den Wochentagsblättern werden berücksichtigt. Änderungen auf dem Zusammenfassungsblatt interessieren den Makro nicht.
  • Auf den Wochentagsblättern werden nur Änderungen im Bereich A25:B30 berücksichtigt. Sag Bescheid, wenn noch mehr Zeilen infrage kommen (Deswegen auch meine Frage im vorigen Posting).
  • Das erste Zeichen in einem Kundenname muss ein Buchstabe sein. Anderes berücksichtigt der Makro nicht. Das habe ich eingebaut, weil du leere Kundenzellen mit Punkten gefüllt hast. Das soll ja nicht auch zu Sperrungen führen.
Am Schluss des Makros erfolgt eine Meldung, welche Zeile auf welchen Blättern gesperrt wurde.

Ich habe bestimmt noch einige Bedienungs-Eventualitäten nicht berücksichtigt (jeder Benutzer ist anders). Der Makro wird sich also vermutlich das ein oder andere Mal verschlucken. Teste ihn mal und melde dich bei Reklamationen.

Gruß, Andreas
 

Anhänge

  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle.zip
    78 KB · Aufrufe: 3

Galapapagos

Stammgast
Lieber Andreas
Vielen herzlichen Dank. Finde ich wirklich seeehr toll von dir!
Genau so habe ich mir es vorgestellt.
3 Punkte/Bitten dazu habe ich noch:
  • Bitte wie nachgefragt bis auf Zeile 40 erweitern. Somit 10 Zeilen dazu. Geschäft soll ja ausbaufähig sein.....
  • Nach der Eingabe kommt die Info zu den gesperrten Zeilen davor und danach. Kann das weggelassen werden? Muss sonst immer weggeklickt werden und Infoinhalt ist danach durch Text "gesperrt" ersichtlich
  • Bei mir hat es mit den Berechnungen nicht mehr funktioniert. Habe ich die Menge 1 eingetragen bei einer Abteilung, dann hat es dies hintenraus nicht mehr gerechnet. Ist das bei dir auch so?

Wie kann ich mich bei dir erkenntlich zeigen?
Blumenstrauss auf die Redaktion senden?

Nochmals besten Dank und Gruss
Reto
 

Galapapagos

Stammgast
Hoi Andreas
Habe soeben festgestellt, dass es die Berechnungen der Mengeneingaben übernimmt, sobald gespeichert wird.
Geht es auch, dass direkt berechnet wird vor Speicherung? Ansonsten verwirrt es die zuständigen Personen in den Abteilungen.
Danke und Gruss
Reto
 

nochEinAndreas

Stammgast
Gute Abend Reto,

Danke für deine Info. Freut mich, dass es nach deinen Vorstellungen läuft.
Zu deinen Bitten:
  • Die Reaktion des Makros habe ich jetzt bis auf Zeile 40 erweitert. Achte aber mal drauf, ob alles richtig läuft, wenn du neue Zeilen einfügst. Das ist nämlich für VBA auch ein "Ändern" von Zellen und der Makro läuft also auch da los. Könnte sein, dass er dann Blödsinn produziert. Falls ja, melde dich.
  • Die Fertig-Meldung habe ich raus genommen.
  • Irgendwann hatte ich schon mal den eigenartigen Effekt, dass ein Makro völlig ungewollt die Berechnungs-Optionen in Excel auf Manuell gestellt hatte. Ich habe damals nicht rausfinden können wann/warum das passiert. Bei mir ist es diesen Mal nicht vorgekommen. Ich habe aber vorsichtshalber als letzte Zeile im Makro ein Kommando eingebaut, dass die Berechnung der Excel-Formeln wieder auf Automatisch einstellt. Ich hoffe, das hilft.
Und erkenntlich hast du dich schon gezeigt, dadurch, dass ich dir helfen konnte. Deswegen bin ich hier, und das macht mir Spaß.

Schöne Grüße,
Andreas
 

Anhänge

  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle.zip
    80,9 KB · Aufrufe: 2

Galapapagos

Stammgast
Hallo Andreas
Das hat ganz gut geklappt. Danke.
Habe bis Zeile 40 dazu gefügt und getestet. Es stimmt und funktioniert :).

Inzwischen stellt sich mir eine zusätzliche Anforderung:
Bei den rot markierten Kunden mit Gratislieferung soll nun ein Kostensplitt rein. Siehe Anlage.
Zeile 5 / Bürgenstock: Hier gibt es 1 Sendung von FleuraMetz in Spalte D. Bezahlen soll Fleura Metz die 30.- in Spale L
Zeile 8 / Parkhotel: Hier gibt es 1 Sendung von FleuraMetz (Spalte D) und 1 Sendung von Pflanzen (Spalte E). -> Hier soll FleuraMetz 20.- (Spalte L) und Pflanzen (Spalte M ECF) 10.- bezahlen.
Zeile 8 / hätte hier FleuraMetz keine Sendung, dann müsste die Abteilung Pflanzen/ECF die 30.- bezahlen in Spalte M.
Zeile 9 / Deko-TBBL und Deko-Päckli bezahlen nie an Kosten. Somit soll in Zeile 9 der Betrag 30.- an Fleura Metz in Spalte L verrechnet werden.

-> Sobald also die Pflanzen eine Sendung mitgeben (unabhängig der Anzahl), sollen sie 10.- Kostenanteil von FleuraMetz übernehmen.

Geht das? Getraue mich schon fast nicht mehr zu fragen ......
Wie muss die Formel in Spalte L und M sein?

Gruss Reto
 

Anhänge

  • 2099_KW-99 Lieferservice-Pauschal_Mastertabelle_Makro.zip
    91,9 KB · Aufrufe: 7

nochEinAndreas

Stammgast
Hallo Reto,

versuchs mal mit diesen Formeln:

In L5: =WENN(UND(ODER(D5>0;F5>0;G5>0);E5=0);30;WENN(E5>0;20;0))
In M5: =WENN(E5>0;10;0)

und dann runterkopieren.

Gruß, Andreas
 

Galapapagos

Stammgast
Lieber Andreas
Vielen Dank. Das funktioniert.
Kann die Formel zusätzlich angepasst werden, dass wenn in D5 der Wert 0 drin ist und in E5 der Wert >0 drin ist, dass dann bei L5 CHF0.- und bei M5 CHF30.- drin ist?
Ansonsten bezahlt FleuraMetz CHF20.-. obwohl nur die Pflanzen eine Sendung drin haben.

Dies nur als Frage, wenn kein grosser Aufwand. ;-)

Danke vielmals für deine Hilfe.
Beste Grüsse
Reto
 

nochEinAndreas

Stammgast
Also, ich hätte jetzt mal gesagt so:
in L5: =WENN(D5>0;WENN(UND(ODER(D5>0;F5>0;G5>0);E5=0);30;WENN(E5>0;20;0));0)
in M5: =WENN(E5>0;WENN(D5>0;10;30);0)

Aber noch 'ne Frage: Du sagtest in Posting #17:
Zeile 9 / Deko-TBBL und Deko-Päckli bezahlen nie an Kosten. Somit soll in Zeile 9 der Betrag 30.- an Fleura Metz in Spalte L verrechnet werden.
Was ist aber, wenn Deko-TBBL oder Deko-Päckli Lieferungen bekommen, Fleura Metz bekommt aber keine Lieferung? Wer zahlt dann? Momentan zahlt niemand. Muss ich da noch was ändern?

Gruß, Andreas
 
Oben