Zeilen in Excel Tabelle sperren nach Werteingabe und datumsaktualisierung

Guten Tag Liebe Forengemeinde...

Ich arbeite momentan an einer recht großen excel Tabelle. Dabei eröffnene sich nunmehr 2 Probleme:

1. Ich habe eine Spalte und möchte, dass sobald in einer Zelle innerhalb dieser Spalte ein bestimmter Wert (zum Beispiel"1") eingetragen wird die gesamte zugehörige Zeile oder zumindest eine benachbarte Zelle der gleichen Zeile entweder Schreibgeschützt oder zumindest irgendwie kenntlich markiert(z.B. durchgestrichen) ist.
Habe es mit einer bedingten Formatierung probiert...leider erfolglos...

2. Ich möchte eine Spalte erstellen, in welcher das Datum der letzten Änderung innerhalb der zugehörigen Zeile aufgeführt ist und dieses automatisch aktualisiert wird sobald eine Änderung in der Zeile vorgenommen wird.
Jegliche Funktionen der Rubrik "Zeit & Datum" konnte ich leider nicht dazu bringen mir eine solche Funktionalität zu ermöglichen

Ich danke für jegliche Hilfe!

Grüße aus dem grünen Herz Deutschlands

 

Hallo Jamin,

zu 1. ...das müsste mit bedingter Formatierung doch gehen. Zwar nicht das Sperren, aber das Markieren (durchgestrichen, farbiger Text oder Hintergrund, Schriftgrösse, fett kursiv...)

Annahme > wenn in Zelle A5 eine 1 ist, müssen andere Zellen ihr Format ändern.
Dann die bedingte Formatierung in den anderen Zellen so >
- links "Formel ist" und rechts die Formel =$A$5=1
- dann Button Format und dort unter Schrift/Rahmen/Muster etwas auswählen das gefällt...


zu 2. ...mir ist nicht bekannt, dass Excel selbst das könnte. Ein Auto-Makro, das laufend das Blatt überwacht, müsste aber das aktuelle Datum in der entsprechenden Spalte einfügen können.

Damit aber nicht jede Aenderung irgendwo im Blatt dann irgendwo sinnlos ein Datum hinschreibt, muss man kennen >
- den gesamten Bereich (zB B12:G120), der überwacht werden soll, und
- die Spalte wo das Datum hingeschrieben werden soll

Gruss Urs

 

ok...zumindest der erste Punkt funktioniert problemlos...dafür erstmal vielen Dank...an den 2. muss ich mich eben nochmal in Ruhe ransetzen...

Aber wie kann ich diese bedingte Formatierung jetzt für meine 9000 zeilen übernehmen ohne jede zeile einzeln einzugeben? man kann diese bedingte Formatierung ja nicht irgendwie als formatvorlage oder so nehmen...

Ich bräuchte quasi eine bedingte Formatierung für die gesamte Spalte sodass jede Zelle die Zellen rechts neben sich auf die Formatierungsbedingung überprüft.

Wenn ich einfach die Spalte markiere und die bedingte Formatierung für die gesamten benachbarten Spalten eingebe dann läuft es logischerweise so dass er die bedingte Formatierung für die gesamte Spalte übernimmt wenn im "Bedingungsgebiet" irgendwo eine eins auftritt, er soll sie aber natürlich nur übernehmen wenn sie in der gleichen Zeile auftritt...

Danke im Voraus!

 

Excel 2003 aus dem office professional...war bislang nicht erfolgreich...aber ich probiere mal noch ein bisschen

ok...habs hinbekommen das genze für alle bedingt zu formatieren
grandios...

wenn ihr noch irgendwelche hinweise bezüglich der Änderungsdatumsgeschichte habt wäre ich euch sehr verbunden :-[...

aber bis dahin erstmal danke...ist einfach so schön wenn die technik das so macht wie man will

 

...und wo bleiben diese beiden Angaben?
Erst dann kann ich Dir ein Automakro schreiben...

Gruss Urs

 

Entschuldigung hab die 2. seite irgendwie verpasst^^

Also A7:V9388

Änderungsspalte A9390

Wäre es möglich dass das Makro mit angibt in welcher Zelle die Änderung vonstatten ging?

Ich danke wie schon so oft inzwischen^^

Achso und wo wir einmal dabei sind...wie kann ich denn aus einem markierten Zellbereich alles in eine neue Mappe übertragen was in einer konkreten Spalte den Wert G hat?

 

Das ist aber eine Zelle, keine Spalte !

Du wolltest doch eine neue Spalte verwenden, um dort das Aenderungsdatum für jede Zeile festzuhalten, oder nicht?
Also am Ende Spalte W, X oder Z...
Oder am Anfang Spalte A.

Die letzte Frage habe ich nicht verstanden...

Gruss Urs

 

du hast natürlich recht entschuldigung

also... spalte AC wäre optimal

so und zur letzten Frage.

Ich möchte die Tabelle in eine neue Arbeitsmappe übertragen, und zwar nur die Zeilen, die eine bestimmte Bedingung erfüllen, nämlich dass ihr zugehöriger in einer bestimmten Spalte z.B. "G" oder "1" beträgt. Man könnte es vielleicht als "bedingten Kopiervorgang" beschreiben
ich weiß nicht ob man das mit einer "WENN" Funktion lösen kann, wenn ja habe ich die genaue verfahrensweise noch nicht herausgefunden^^

 

Hallo Jamin,

Das ist ja ein rechter Brummer mit 250'000 Zellen, wenn man da nicht den schnellsten Weg nimmt...
Ich denke so geht es schnell genug >

1. Gib der Tabelle einen Namen
Ganze Tabelle A7:V9388 markieren, dann oben links, wo die Zellennummer angezeigt wird, den Namen Table1 reinschreiben (wenn Du jetzt auf den kleinen Pfeil daneben klickst, kannst Du Table1 auswählen...)

2. Rechtsklick auf den Reiter des Blattes mit der Tabelle > Code Anzeigen
Im VB-Editor sollte links jetzt dieses Blatt markiert sein
Ins Feld rechts diesen Code kopieren >>>

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
                    'prüft ob geänderte Zelle innerhalb des Bereiches "Table1"
If Intersect(Target, Application.Names("Table1").RefersToRange) Is Nothing Then Exit Sub
                    
                    'schreibt Datum in geänderte Zeile(n) in Spalte AC (=Spaltennummer 29)
xxx = Target
                    'wenn mehrere Zeilen (copy/paste, Füllen, Verschieben)
    If IsArray(xxx) = True Then
        arr = UBound(xxx) - 1
        Range(Cells(Target.Row, 29), Cells(Target.Row + arr, 29)).Value = Date
    Else
                    'wenn nur eine Zeile
        Cells(Target.Row, 29) = Date
    End If

End Sub

3. Innerhalb der Tabelle etwas ändern >> in Spalte AC wird in der geänderten Zeile das heutige Datum gesetzt.
Ausserhalb der Tabelle etwas ändern >> das Makro merkt das... und macht nichts !


4. "Bedingtes Kopieren" > Filtern scheint mir der schnellste Weg zu sein >
Die Tabelle über ihren Namen aus 1) markieren.
Menü Daten > Filtern > Autofilter
In der Spalte nach der gefiltert werden soll, auf den kleinen Dropdown klicken und das G, 1, P oder sonst was anklicken
Wenn das Zeichen dort nicht sichtbar ist, auf "benutzerdefiniert..." und dort neben "entspricht" rechts dieses Zeichen eingeben
...jetzt werden nur noch die Zeilen mit dem gewählten Zeichen angezeigt.
Mit Copy/Paste kannst Du das einfügen wo es gebraucht wird.

Du kannst auch nach dem ersten Kriterium noch aus einer anderen Spalte weiteres entfernen lassen.
Zum Entfernen der Pfeile und Anzeigen der ganzen Tabelle >> im Menü wieder auf Autoformat klicken.

Teste an einer Kopie, ich will das Monster dann nicht neu schreiben müssen...
... berichte, ob alles tut wie es soll

Gruss Urs

 

Die Sache mit dem Filter erleichtert den Datenextraktionsprozess ungemein danke...

Aber das mit der Datumsüberwachung funktioniert nicht...hab alles getan wie beschrieben, den code für die entsprechnde tabelle eingefügt, "Table1" definiert, usw...

Änderungsdatum wird nicht angezeigt.

Muss ich das evtl. noch irgendwie als makro aktivieren oder sowas oder fällt das weg?(ist quasi der charme des sog. auto makros?^^)

 

Nichts aktivieren, das Makro wird erkannt und führt sich automatisch aus, sobald im Blatt etwas geändert wird...
...sofern das Makro auch am richtigen Ort eingefügt wurde >

Annahmen >
Deine Mappe heisst "GrosseMappe"
Das Arbeitsblatt mit der Tabelle heisst "GrossesBlatt"

Im VB-Editor steht links im Projekt-Explorer (wenn nicht angezeigt, dann über Menü Ansicht anzeigen lassen) >
VBAProject(GrosseMappe)
...darunter zu dessen Unterordner zu
TabelleX(GrossesBlatt) ....... X ist irgendeine Zahl...
...Doppelklick darauf >>> dort muss rechts jetzt der Code sein...

Wenn er wo anders sein sollte, kann es nicht funktionieren.
Ich habe auch Excel2003, bei mir tut es, was es soll.


Wenn es immer noch nicht gehen sollte, dann >>>
- im VB-Editor, neben der Zeile "Private Sub Worksheet_Change(....." auf den linken grauen Rand klicken > dort erscheint dann ein brauner Punkt
- im Blatt eine Zelle ändern > jetzt müsste der VB-Editor angezeigt werden, die Zeile Private Sub... gelb unterlegt
- nun kannst Du im Schrittmodus das Makro abarbeiten
- Taste F8 drücken, für die jeweils nächste Zeile...

- wenn eine Zeile ausserhalb des benannten Bereichs geändert > steigt nach der ersten Code-Zeile aus > Ende
- sonst müsste es alle Zeilen abarbeiten...

Was passiert bei Dir in diesem Schrittmodus??

Gruss Urs

 

Ok...funktioniert hervorragend, hatte es auf das ganze projekt gelegt, deshalb wollte es zunächst nicht.

ich kann folglich nur zum wiederholten male meinen dank aussprechen...

eine letzte Problemstellung wäre:

Ich habe Werte in einer anderen Tabelle. Wenn einer dieser Werte mit einem wert aus einer definierten Spalte der "monstertabelle" identisch ist, dann soll in einer anderen Spalte eine 1 erscheinen.

Ich habe es mit einer Funktion =WENN(VERGLEICH();1;"") probiert aber so kriege ich es nicht hin dass er die gesamte Spalte nach dem entsprechenden Wert durchsucht.

Wenn euch eine mögliche Lösung einfällt...

 

Problem X

Annahmen >>>
...in Blatt GrosseMappe\GrosseTabelle >
Spalte R enthält die zu vergleichenden Werte
Spalte S erhält einen Wert, wenn der Vergleich wahr ist, sonst einen anderen Wert.

...in Blatt GrosseMappe\AndereTabelle >
Spalte A enthält die anderen zu vergleichenden Werte, in zB A1:A101

...dann so >>>
Im Blatt GrosseTabelle, in Zelle S7 diese Formel >
=WENN(ISTNV(SVERWEIS(R7;AndereTabelle!$A$1:$A$101;1;FALSCH));"notOK";"OK")
...und runterkopieren bis 9389

Wenn SVERWEIS die Entsprechung nicht findet, schreibt es den Fehler "#NV" in die Zelle. Dieses wird mit WENN verhindert.
Die "notOK" und "OK" kannst Du ändern wie Du es haben willst, auch in "" , dann bleibt die Zelle leer.

Uebrigens > Bei Aenderungen im Blatt AndereTabelle wird kein neues Datum gesetzt...
...im Blatt GrosseTabelle wäre das keine neue Eingabe, sondern eine Neuberechnung.

Gruss Urs