Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1412to1416
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Bedingte Formatierung Fortsetzung

Bedingte Formatierung Fortsetzung
02.03.2015 08:23:03
Rolf
Hallo Experten,
habe eine Tabelle mit Maschinenverleih, wenn eine Maschine verliehen wird, wird in der Tabelle Bestand, die Zeile wo die Maschine eingetragen ist farblich gekennzeichnet (wir mit Datumseintrag formatiert). Wenn eine Maschine zurück kommt, wird die Zeile wieder farblos (wird mit Datumseintrag gelöscht), funktioniert auch alles. Wenn eine Maschine die schon verliehen war, wieder verliehen wird, funktioniert die Formatierung nicht mehr (wegen der Formel). =UND(SVERWEIS($A2;Verleih!$A$3:$K$300;10;FALSCH)"";SVERWEIS($A2;Verleih!$A$3:$K$300;11;FALSCH)="")
Ist ja auch klar. Habe eine Hilfsspalte gemacht in der Ja steht, diese Spalte blende ich dann aus. Kann mir jemand helfen, dass die Maschinen farblich hinterlegt werden, egal wie oft sie ausgeliehen werden. Habe die Datei hochgeladen https://www.herber.de/bbs/user/96099.xlsx

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bedingte Formatierung Fortsetzung
02.03.2015 09:24:57
Hajo_Zi
Du kennst Deine Datei ganz genau, die meisten hier sehe Sie das erste mal.
Also schreibe genau was gemacht wird und was passiert und was falsch ist.
Ich vermute mal die meisten investieren keine Zeit um die Logik Deiner Datei rauszubekommen?

AW: Bedingte Formatierung Fortsetzung
02.03.2015 10:17:51
Rolf
Hallo Hajo,
die Datei hat vier Reiter, Bestand und Verleih sind für meine Frage. Wenn ich in Verleih die Ragalnummer eintrage, werden die anderen Zellen ausgefüllt. Trage ich in Spalte J das Datum ein, wann die Maschine verliehen wird verfärbt sich im Reiter Bestand die Zeile mit der Regalnummer die im Verleih eingeben wurde. Wird die Maschine zurück gegeben und in Spalte K von Verleih, das Datum eingegeben wird im Bestand die Zeile wieder farblos. Hoffe das ist jetzt verständlich.
Gruß Rolf

Anzeige
mit VERWEIS() anstelle SVERWEIS() ...
02.03.2015 09:40:12
neopa
Hallo Rolf,
... ich gehe davon aus, dass Deine Verleihtabelle chronologisch fortgeschrieben wird. Weil nun SVERWEIS() von vorn / ääh hier von oben sucht, solltest Du deshalb anstelle SVERWEIS() die Funktion VERWEIS() in der Formel der bedingten Formatierung der Tabelle Bestand! nutzen.
z.B. so:

=VERWEIS(9;1/(Verleih!$A$1:$A$999=$A9)/(Verleih!$J$1:$J$999"");Verleih!$K:$K)>0
Gruß Werner
.. , - ...

AW: Bedingte Formatierung Fortsetzung
02.03.2015 09:42:41
yummi
Hallo Rolf,
mal als Idee: wenn du in Spalte J ein Datum einträgst, löscht Du gleichzeitig die Zelle in Spalte K, dann hast du immer nur den Zustand ausgeliehen
Könntest Du über vba machen
rechtsklick auf den Reiter Verleih und folgendes einfügen:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then
Cells(Target.Row, 11).Value = ""
End If
End Sub
Gruß
yummi

Anzeige
AW: Bedingte Formatierung Fortsetzung
02.03.2015 14:39:39
Rolf
Hallo Yummi,
Makro funtioniert, nur wird dann die Zelle vom Verleihdatum Rot eingefärbt. Habe leider ich nicht erwähnt, dass nach 14 Tagen die Zelle Rot wird, wenn die Maschine noch nicht zurück gegeben wurde.
Wenn ich Dein Makro ausführe sind ja die Zellen Rot, dann muß ich ja die ganze Tabelle durchsuchen ob die Maschine wieder ausgeliehen ist oder noch nicht zurück gegeben wurde.
Kann ich nicht irgendwie die Hilfsspalte L mit in die Formel nehmen?
Gruß Rolf

AW: Bedingte Formatierung Fortsetzung
03.03.2015 10:05:02
yummi
Hallo Rolf,
ja du kannst jede Spalte mit berücksichtigen. ich weiß nur nicht wann was genau passieren soll. schreib mal alle Möglichkeiten und auf und vermerke was wann noch nicht geht und wie es sein soll.
Gruß
yummi

Anzeige
AW: Bedingte Formatierung Fortsetzung
03.03.2015 11:53:21
Rolf
Hall Yummi,
Meine Tabelle, hat 4 Register (Tabellenblätter), zum Maschinen verleihen. Die Register Bestand und Verleih sind für meine Frage relevant. Im Bestand sind alle Maschinen aufgeführt mit Regalnummer, Seriennummer der Maschine usw., in Verleih wird die Regalnummer eingetragen und durch Formeln, die sich aus Bestand beziehen, werden die restlichen Daten ausgefüllt. Trage ich in Verleih das Datum vom ausgeliehen Tag ein (Spalte J), wird im Bestand die Zeile (von der Regalnummer) farblich formatiert. Wird die Maschine zurückgegeben, wird die formatiert Zeile im Bestand wieder farblos, durch Eingabe des Datums im Register Verleih und in Spalte K. Das funktioniert soweit alles. Wenn jetzt aber eine Maschine die schon ausgeliehen war, wieder verliehen wird, funktioniert die Formatierung nicht mehr. Ist ja durch die Formel auch logisch. =UND(SVERWEIS($A2;Verleih!$A$3:$K$300;10;FALSCH)"";SVERWEIS($A2;Verleih!$A$3:$K$300;11;FALSCH)="")
Was ich noch erwähnen muss, kommt die Maschine nach 14 Tagen noch nicht zurück, färbt sich die Zelle vom Verleihdatum Rot, wird dann eine Spalte daneben das Datum vom Zurückgabe Tag eingetragen, wird die Zelle vom Verleihtag wieder Farblos. Hoffe ich habe es verständlich beschrieben, die Tabelle habe ich ja schon hoch geladen.
Hab schon etliche Formeln ausprobiert und sogar eine Hilfsspalte kreiert und komm einfach nicht mehr weiter.

Anzeige
AW: Bedingte Formatierung Fortsetzung
03.03.2015 13:47:19
yummi
Hallo Rolf,
deine Erklärung in allen Ehren, aber sie beantwortet meine Fratge in keinster Weise.
deien hilfssplate L (in welchem Sheet die auch sein mag), habe ich bisher in deiner Beispilemappe übersehen. Ich weiß nicht was die machen soll
Dann versuch ich das makl und du musst ergänzen
1. Spalte J auf Verleih wird ein Datum eingegen - andere Felder werden gefärbt funzt richtig?
2. Spalte J auf Verleih ist gefüllt und vor Ablauf der 2 wochen wird Spalte K auf Verleih gefüllt
irgendetwas passiert funzt richtig?
3. Spalte J ist leer Spalte K wird gefüllt, muss das abgefangen werden?
4. Spalte J ist gefüllt 2 wochen sind abgelaufen irgend etwas passiert funzt richtig?
5. Spalte J ist gefüllt nach über 2 wochen wird k gefüllt was soll passieren?
6. Spalte j und Spalte K sind gefüllt, die Maschine wird erneut verliehen, als Spalte J ändert sich, dann muss Spalte K wird geleert, Spalte J gefärbt Spalte K ohne Farbe (macht das makro von mir) was soll noch passieren bzw berücksichtigt werden?
So wie ich es bisher verstanden habe passieren die eingaben ur auf dem Sheet Verleih.
Spiel die Fälle mal für eine Zeile durch und sag mir was passieren soll wenn in welcher Spalte Einträge gemacht werden. Erzähl mir aber nicht wieder was von Maschinen, die sind mir egal, reduziere das mal auf Zellen und sag was wann passieren soll.
Ich hoffe, ich konnte mich verständlich ausdrücken, welche Infos ich benötige, wenn ich dir helfen soll.
Gruß
yummi

Anzeige
AW: Bedingte Formatierung Fortsetzung
03.03.2015 15:13:13
Rolf
Hallo Yummi,
sorry das dass mit meiner Erklärungen nicht so gut war. Die Hilsspalte ist in der hochgeladenen datei noch nicht vorhanden.
Ich versuchs nochmal mit erklären. Zu Deiner 1. Frage: im Reiter Verleih wird in Spalte A die Regalnummer eingetragen. In Spalte J wird ein Datum eingetragen. Jetzt wird im Reiter Bestand, die Zeile, wo in Spalte A die gleiche Regalnummer steht wie im Reiter Verleih Spalte A, farbig formatiert. Funzt richtig!
2. -5. Im Reiter Verleih, wenn 12 Tage seit Verleih Tag vorüber sind wird in Spalte J die Zelle in der das Datum steht Rot formatiert. Wird in der gleichen Zeile in Spalte K, ein Datum eingegeben, wird in Spalte J die formatierte Zelle wieder farblos. Genauso im Reiter Bestand wird die formatierte Zeile farblos.
5. Spalte J und Spalte K ist gefüllt, Zelle in Spalte J ohne formatierung.
6. Spalte J sollte nicht gefärbt werden
Wenn es geht, sollte K nicht gelöscht werden. Meine Hilfsspalte, im Reiter Verleih, heist Erledigt. Wenn J und K befüllt sind, wird Spalte L mit Ja befüllt.

Anzeige
AW: Bedingte Formatierung Fortsetzung
03.03.2015 16:11:31
yummi
Hallo Rolf,
ok hab jetzt verstanden was du willst ;-)
aber ich glaube du musst das ganze komplett überdenken.
1. Sverweis funktioniert zuverlässig nur mit sortierten Listen (meines wissens nach) und das ist bei dir ja nicht gegeben.
2. Sverweis findet immer den ersten Eintrag des Suchbegriffs, dass ist auch der Grund warum du eine Maschine kein 2. mal ausleihen kannst. weil in der 1. Zeile ja alles ok ist gibt es keine Einfärbung.
Mein Vorschlag, schau dir mal das an:
http://www.tippscout.de/excel-liste-reihe-letzter-wert_tipp_5625.html
Gruß
yummi

Anzeige
nachgefragt ...
03.03.2015 17:57:40
neopa
Hallo Rolf,
... hattest Du meinen Vorschlag getestet?
Gruß Werner
.. , - ...

AW: nachgefragt ...
04.03.2015 07:48:31
Rolf
Guten Morgen Werner,
entschuldige bitte das ich Dir kein Feedback gegeben habe. Mit Deiner Formel werden nicht alle ausgeliehenen Maschinen angezeigt. Muss auch ehrlich zugeben, das ich bei Deiner Formel nicht ganz durchblicke. Was hat 9;1 zu bedeuten? Spalte kann es nicht sein oder? Könntest Du mir das bitte erklären?
Gruß Rolf

Deine Aussage ist richtig, ...
04.03.2015 08:35:29
neopa
Guten Morgen Rolf,
... mein Formelvorschlag konnte wie geschrieben nicht das gewünschte erzielen. Ich hatte da zwei dumme Schreibfehler drin, wie ich jetzt beim Nachstellen feststellen musste.
Für Deinen Geltungsbereich (ab Zeile 2) der bedingten Formatierung folgende korrigierte Formel:
=VERWEIS(9;1/(Verleih!$A$1:$A$999=$A2)/(Verleih!$J$1:$J$999"");Verleih!$K:$K)=0
Natürlich musste es gleich 0 und nicht größer 0, wie am Montag geschrieben, lauten.
Nun sollte das gewünschte markiert werden, oder? Wenn ja, schiebe ich die Erläuterung nach.
Gruß Werner
.. , - ...

Anzeige
AW: Deine Aussage ist richtig, ...
04.03.2015 09:09:41
Rolf
Hallo Werner,
DANKE, ja jetzt funktioniert es. Wenn ich das (9;1)gewußt hätte, hätte ich die Formel genauer angeschaut und wäre vielleicht auf den Fehler gekommen. Bin auf die Erläuterung gespannt.
Gruß Rolf

dazu nun meine spez. Erläuterungen ...
04.03.2015 11:45:30
neopa
Hallo Rolf,
... schreib mal die folgende (bewusst reduzierte)Formel :
=VERWEIS(9;1/(Verleih!$A$1:$A$29=$A4)/(Verleih!$J$1:$J$29"");Verleih!$K:$K)=0
in eine Zelle (z.B. H4) Deiner Tabelle Bestand. Als Ergebnis ergibt diese ein WAHR.
Markiere nun in der Eingabezeile den Formelteil: 1/(Verleih!$A$1:$A$19=$A2) und betätige die [F9] Taste. Nun findest Du in der Teilergebnismatrix außer vielen #DIV/0! auch eine 1. Diese steht platzierungsmäßig an der Stelle, wo der Wert A4 auch in Verleih!$A$1:$A$29 gefunden wird.
Nun markiere 1/(Verleih!$A$1:$A$29=$A4)/(Verleih!$J$1:$J$29"") und betätige [F9]. Auch hier findet sich wieder eine 1. Diese 1 findet VERWEIS() trotz der sonstigen Fehlermeldungen (andere Funktionen würden in so einem Fall die Arbeit verweigern ;-)) und übergibt diese "Fundstellenplatzierung" als Parameter zum Suchen in der Matrix Verleih!$K:$K der VERWEIS()-Formel. Damit ergibt die gesamte Formel dann aus dieser den dort gefundenen Wert wieder. Der ist im speziellen Beispiel nicht vorhanden und somit ergibt sich eine 0 als VERWEIS()-Formelergebnis, was mit =0 verglichen wird und deshalb hier ein WAHR ergibt.
Wenn Du die Formel nach H7 kopierst, steht in der Teilergebnismatrix: 1/(Verleih!$A$1:$A$29=$A7)/(Verleih!$J$1:$J$29"") die 1 an einer Stelle, wo in Verleih!$K:$K als Ergebnis ein Datumswert vorhanden ist, der damit gemäß Formel nicht gleich 0 ist und ein FALSCH als Formelergebnis ergibt.
Es können nun bei anderen Vergleichen mehrere 1 in der Teilmatrix stehen oder überhaupt keine. Im letzterem Fall ist das Ergebnis immer #NV und im ersteren Fall eben immer der Wert der sich an der Stelle von der Matrix Verleih!$K:$K befindet, wo die letzte 1 steht (VERWEIS() sucht stets von hinten bzw. von unten oder eben rechts)
Damit die Stelle der letzten 1 auch wirklich lokalisiert werden kann, bedarf es als Parameter vor dem Formelteil 1/(...) einen Wert größer 1. Ich nutze dafür stets die 9. Du kannst auch 1,001 schreiben oder 9876 oder ...
Gruß Werner
.. , - ...

Anzeige
AW: dazu nun meine spez. Erläuterungen ...
04.03.2015 16:37:15
Rolf
Hallo Werner,
Tausend Dank für Deine Formel und Deine detaillierte Erklärung. Werde es heute Abend nach Deiner Anleitung testen und hoffe das ich die Formel dann verstehe :-)
Gruß Rolf

nachgeschoben ....
04.03.2015 17:07:08
neopa
Hallo Rolf,
... würde mich freuen, wenn Du mit meinen Erläuterungen einigermaßen klarkommst, denn dies ist nicht gerade meine starke Seite und auch nicht leicht nachzuvollziehen. Oberflächlich betrachtet möglicherweise schon. Doch dies zu verinnerlichen, benötigt Zeit. Also ich hab dazu bestimmt einige Wochen wenn nicht mehr benötigt.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige