expertenaustausch > microsoft.* > microsoft.excel

Herje70 (05.03.2019, 13:41)
Hallo zusammen,
in einer Mappe habe ich eine Spalte mit fortlaufenden Nummern (ID) und den entsprechenden Einträgen in den Zeilen dahinter. Diese Einträge werden abgearbeitet und als erledigt gekennzeichnet. Nun wird die Mappe einmal wöchentlich in ein Archiv kopiert und im Orgiginal die erledigten Zeilen gelöscht.
Dabei sollen die laufenden Nummern aber weitergeführt werden, d.h., jede vergebene ID darf nur einmal vorkommen.
Die höhste ID kann ich mit =max() ermitteln, aber wie stelle ich das an, dass mir in A1 die nächste freie ID angezeigt wird?

z.B., ID = 100, in A1 =max("A3:A300")+1 ergibt 101 als nächste freie ID.
wird aber Zeile mit ID 100 gelöscht, so steht in A1 100, ich bräuchte aber 101??

Ideen??

VG Jens
Claus Busch (05.03.2019, 14:01)
Hallo Jens,

Am Tue, 5 Mar 2019 03:41:25 -0800 (PST) schrieb Herje70:

> in einer Mappe habe ich eine Spalte mit fortlaufenden Nummern (ID) und den entsprechenden Einträgen in den Zeilen dahinter. Diese Einträge werden abgearbeitet und als erledigt gekennzeichnet. Nun wird die Mappe einmal wöchentlich in ein Archiv kopiert und im Orgiginal die erledigten Zeilen gelöscht.
> Dabei sollen die laufenden Nummern aber weitergeführt werden, d.h., jede vergebene ID darf nur einmal vorkommen.
> Die höhste ID kann ich mit =max() ermitteln, aber wie stelle ich das an, dass mir in A1 die nächste freie ID angezeigt wird?


das ist mit einer Formel nicht zu erreichen. MAX findet immer nur das
vorhandene Maximum. Das heißt, die IDs würden sich ändern, wenn das
Maximum gelöscht würde.Du musst dazu schon VBA verwenden und den
MAX-Wert festschreiben.
Werden die IDs von Hand eingetragen? Wie wird gelöscht? Von oben nach
unten? Kann es sein, dass die komplette Tabelle gelöscht wird, weil
alles abgearbeitet ist?
Eine Methode wäre, die abgearbeiteten Datensätze statt zu löschen nur
auszublenden. Das Maximum wäre immer vorhanden.

Mit freundlichen Grüßen
Claus
Thomas Langer (06.03.2019, 16:05)
Am 05.03.2019 um 12:41 schrieb Herje70:
> Hallo zusammen,
> in einer Mappe habe ich eine Spalte mit fortlaufenden Nummern (ID) und den entsprechenden Einträgen in den Zeilen dahinter. Diese Einträge werden abgearbeitet und als erledigt gekennzeichnet. Nun wird die Mappe einmal wöchentlich in ein Archiv kopiert und im Orgiginal die erledigten Zeilen gelöscht.
> Dabei sollen die laufenden Nummern aber weitergeführt werden, d.h., jede vergebene ID darf nur einmal vorkommen.
> Die höhste ID kann ich mit =max() ermitteln, aber wie stelle ich das an, dass mir in A1 die nächste freie ID angezeigt wird?
> z.B., ID = 100, in A1 =max("A3:A300")+1 ergibt 101 als nächste freie ID.
> wird aber Zeile mit ID 100 gelöscht, so steht in A1 100, ich bräuchte aber 101??
> Ideen??
> VG Jens

funktioniert als Formel, unter der Annahme, dass das Archiv in
Archiv.xlsx abgelegt ist:

=MAX(A3:A300;[Archiv.xlsx]Tabelle1!$A:$A)+1

einfach den Datei- und Tabellennamen anpassen

Gruß
Thomas
Herje70 (08.03.2019, 08:49)
Am Mittwoch, 6. März 2019 15:05:19 UTC+1 schrieb Thomas Langer:
> Am 05.03.2019 um 12:41 schrieb Herje70:
> funktioniert als Formel, unter der Annahme, dass das Archiv in
> Archiv.xlsx abgelegt ist:
> =MAX(A3:A300;[Archiv.xlsx]Tabelle1!$A:$A)+1
> einfach den Datei- und Tabellennamen anpassen
> Gruß
> Thomas
> --
> Für direkte Nachrichten Spam weglassen


Hallo Thomas,

das geht nicht da die Mappe jede Woche ins Archiv kopiert wird und aus dem Original die Zeilen gelöscht werden. somit haben wir viele Mappen im Archiv. Leider ist das so vorgegeben.

VG
Herje70 (08.03.2019, 08:55)
Am Dienstag, 5. März 2019 13:01:38 UTC+1 schrieb Claus Busch:
[..]
> --
> Windows10
> Office 2016


Hallo Claus,

die laufenden Nummern werden bei neuen Einträgen händisch vergeben, gelöscht werden Einträge (Zeilen) an verschiedenen Positionender Mappe, je nach dem wann sie erledigt sind.
Ich bräuchte also was was im Hintergrund die IDs mitzählt und mirin A1 fortlaufend die nächste freie ID anzeigt. nur wie...

VG
Herje70 (08.03.2019, 08:59)
Am Dienstag, 5. März 2019 13:01:38 UTC+1 schrieb Claus Busch:
[..]
> --
> Windows10
> Office 2016


Nachtrag, ausblenden funktioniert auch nicht, da die Datei jetzt schon ca. 30MB groß ist. deshalb löschen wir Einträge raus. Da ständig neue Einträge dazukommen, wir die Liste nie gänzlich geleert, jedenfalls nicht so lange sie benötigt wird.
VG
Claus Busch (08.03.2019, 09:11)
Hallo Jens,

Am Thu, 7 Mar 2019 22:55:06 -0800 (PST) schrieb Herje70:

> die laufenden Nummern werden bei neuen Einträgen händisch vergeben, gelöscht werden Einträge (Zeilen) an verschiedenen Positionen der Mappe, je nach dem wann sie erledigt sind.
> Ich bräuchte also was was im Hintergrund die IDs mitzählt und mir in A1 fortlaufend die nächste freie ID anzeigt. nur wie...


probiers mal so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A3:A300")) Is Nothing _
Or Target.Count > 1 Then Exit Sub

Range("A1") = Application.Max(Range("A3:A300")) + 1
End Sub

Mit freundlichen Grüßen
Claus
Herje70 (08.03.2019, 11:01)
Am Freitag, 8. März 2019 08:11:38 UTC+1 schrieb Claus Busch:
[..]
> --
> Windows10
> Office 2016


Danke für deine Mühe, aber damit zeigt es mir auch nur das Maximum +1 an, wenn die Letzte Zeile (z.B. 100) ist dann 101. lösche ich die100, dann schlägt er mir die 100 vor statt 101. das wollten wir nicht.. Die ID soll nur einmal vergeben werden. Hatte ich aber auch so geschrieben.
vg
Claus Busch (08.03.2019, 11:16)
Hallo Jens,

Am Fri, 8 Mar 2019 01:01:53 -0800 (PST) schrieb Herje70:

> Danke für deine Mühe, aber damit zeigt es mir auch nur das Maximum +1 an, wenn die Letzte Zeile (z.B. 100) ist dann 101. lösche ich die 100, dann schlägt er mir die 100 vor statt 101. das wollten wir nicht. Die ID soll nur einmal vergeben werden. Hatte ich aber auch so geschrieben.


wenn du nur das Maximum löschst, passiert dies.
Allerdings dachte ich, dass komplette Zeilen gelöscht werden. Dabei sind
mehrere Zellen markiert und das Makro wird nicht gefeuert. Damit bleibt
das Maximum+1 bestehen.

Mit freundlichen Grüßen
Claus
Claus Busch (08.03.2019, 11:20)
Hallo Jens,

Am Fri, 8 Mar 2019 01:01:53 -0800 (PST) schrieb Herje70:

> Danke für deine Mühe, aber damit zeigt es mir auch nur das Maximum +1 an, wenn die Letzte Zeile (z.B. 100) ist dann 101. lösche ich die 100, dann schlägt er mir die 100 vor statt 101. das wollten wir nicht. Die ID soll nur einmal vergeben werden. Hatte ich aber auch so geschrieben.


wenn nicht die ganze Zeile gelöscht wird, sondern nur die ID, erweitere
den Code so:

If Len(Target) > 0 Then Range("A1") = Application.Max(Range("A3:A300")) + 1

Mit freundlichen Grüßen
Claus
Carlos Naplos (08.03.2019, 16:45)
Am 05.03.2019 um 12:41 schrieb Herje70:
> Hallo zusammen,
> in einer Mappe habe ich eine Spalte mit fortlaufenden Nummern (ID) und den entsprechenden Einträgen in den Zeilen dahinter. Diese Einträge werden abgearbeitet und als erledigt gekennzeichnet. Nun wird die Mappe einmal wöchentlich in ein Archiv kopiert und im Orgiginal die erledigten Zeilen gelöscht.
> Dabei sollen die laufenden Nummern aber weitergeführt werden, d.h., jede vergebene ID darf nur einmal vorkommen.
> Die höhste ID kann ich mit =max() ermitteln, aber wie stelle ich das an, dass mir in A1 die nächste freie ID angezeigt wird?
> z.B., ID = 100, in A1 =max("A3:A300")+1 ergibt 101 als nächste freie ID.
> wird aber Zeile mit ID 100 gelöscht, so steht in A1 100, ich bräuchte aber 101??
> Ideen??
> VG Jens


Schreibe in A1 den richtige Wert (keine Formel)!
(Dann passiert beim Löschen irgendwelcher anderer Zeilen gar nichts.)

Sorge dafür, dass der Wert in A1 hoch gezählt wird, wenn neue Einträge
eingefügt werden.

Wie das geht, hängt von der Art und Weise ab, wie neue Einträge
eingefügt werden.

Du könntest z.B. den Bereich der Einträge als Tabelle formatieren und
ein kleines Makro schreiben, das Dir eine neue Tabellenzeile anhängt und
die ID automatisch einträgt.

Du könntest die Spalte A so sperren, dass nur das Makro dort etwas
ändern kann. Dann brauchst Du aber auch zum Löschen ein Makro.

Je narrensicherer die Sache sein soll, desto komplizierter wird sie.

Gruß
CN
Thomas Langer (08.03.2019, 23:38)
Am 08.03.2019 um 07:49 schrieb Herje70:
> Am Mittwoch, 6. März 2019 15:05:19 UTC+1 schrieb Thomas Langer:
> Hallo Thomas,
> das geht nicht da die Mappe jede Woche ins Archiv kopiert wird und aus dem Original die Zeilen gelöscht werden. somit haben wir viele Mappen im Archiv. Leider ist das so vorgegeben.
> VG


Das Löschen ist für die Formel kein Problem, da die Daten ja zuvor
kopiert worden sind. Du musst nur dafür sorgen, dass der richtige
Archivdateiname, d.i. der des zuletzt erzeugten Archivs, in der Formel
steht. Erläutere mal, wie sich die FQDN der Archivdateien über die Zeit
-Woche, Monat, Jahr - verändern. Darauf kann die Formel bestimmt
angepasst werden.

VG Thomas
Herje70 (25.03.2019, 14:10)
Danke für die Antworten, ich habe es über VBA versucht, aber so richtig klappt es nicht. wir zählen händishc die ID hoch, so viel Aufwand ist das nicht.

vg

Am Dienstag, 5. März 2019 12:41:27 UTC+1 schrieb Herje70:
[..]
Carlos Naplos (25.03.2019, 18:05)
Damit das funktioniert, muss das bisherige Maximum irgendwo gespeichert
sein, wo es nicht gelöscht wird. Denn wenn die Zeile mit der höchsten ID
gelöscht wird, ist diese Information verschwunden.

1. Speichere die bisher höchste ID in einer Zelle, die nicht gelöscht wird!

2. Nenne diese Zelle z.B. "NEU"!

3. Wechsle in den VBA-Editor!

4. Klicke dort im Projektfenster mit der rechten Maustaste auf Dein
Arbeitblatt und wähle im Kontextmenü "Code anzeigen" aus!

5. Schreibe das folgende Makro in das Code-Fenster:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("NEU") = _
WorksheetFunction.Max( _
Range("Neu"), _
WorksheetFunction.Max(Range("A1:A300")) + 1 _
)
Application.EnableEvents = True
End Sub

Fertig.

Statt "NEU" kannst Du auch die Adresse der Zellen angeben, z.B. "N1".
Dann kann 2. entfallen.
(Der Vorteil der Namen ist, dass sie mit der Zelle verschoben werden und
Du den Code nicht anpassen musst, wenn die Zelle verschoben wird.)

Gruß CN

Am 25.03.2019 um 13:10 schrieb Herje70:
[..]
Herje70 (30.04.2019, 14:01)
Am Montag, 25. März 2019 17:05:40 UTC+1 schrieb Carlos Naplos:
[..]
> Du den Code nicht anpassen musst, wenn die Zelle verschoben wird.)
> Gruß CN
> Am 25.03.2019 um 13:10 schrieb Herje70:


Hallo Carlos,

damit funktioniert es wie gewünscht, danke.

VG Jens
Ähnliche Themen