Office: (Office 2013) Doppelte Einträge farblich markieren und als vergeben oder frei deklarieren

Hallo Zusammen,
Ich bin neu hier und hoffe das mir jemand helfen kann, im google finden sich etliche Lösungsvorschläge mit denen ich als Excel Anfänger leider nicht viel anfangen kann...
Nun zu meinem Problem.
Ich habe eine excel Datei mit zwei Tabellen in Tabelle1 gennant APL befinden sich Personalnr. In Spalte A Name in spalte B Maschinen Typ in Spalte C und Maschinen Nr in Spalte D
Auf Tabellenblatt 2 genannt Maschinenbestand befindet sich eine Liste mit Maschinen Typ in spalte A Maschinen Nr in Spalte B und in spalte C soll stehen Vergeben oder Frei.

Im Prinzip benötige ich eine Formel die mir folgende Arbeit erledigt.

Wenn ich auf Tabelle 1 also APL in Spalte D einem Mitarbeiter eine Maschinen Nr. Zuweise soll auf Tabelle2 Maschinenbestand in Spalte C Vergeben erscheinen. Bei allen nicht vergebenen Maschinen Nr. Soll Frei stehen. Nett wäre noch wenn die freien Maschinen Nr. grün hinterlegt werden und die vergebenen Maschinen Nr in Spalte A Rot hinterlegt werden also die jeweilige zelle natürlich. Vergeben oder frei soll ganz normal schwarz geschrieben sein.

Ich hoffe ich habe es halbwegs verständlich erklärt und ich hoffe das mir jemand aus der Klemme helfen kann, besten Dank schon mal im Voraus....

 

Hi Uwe,

die Färbung kannst du mit bedingter Formatierung erreichen:


Maschinenbestand[TABLE="width: 1"]
<colgroup><col style="font-weight:bold; width:40px;"><col style="width:131px;"><col style="width:106px;"><col style="width:106px;"></colgroup><tbody>[TR="bgcolor: #99CCFF"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Maschinen-Nr.[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Vergeben[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Frei[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]2[/TD]
[TD="bgcolor: #00B050 , align: right"]1[/TD]
[TD="bgcolor: #FFFFFF , align: left"] [/TD]
[TD="bgcolor: #FFFFFF , align: left"]X[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]3[/TD]
[TD="bgcolor: #FF0000 , align: right"]2[/TD]
[TD="bgcolor: #FFFFFF , align: left"]X[/TD]
[TD="bgcolor: #FFFFFF , align: left"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]4[/TD]
[TD="bgcolor: #FF0000 , align: right"]3[/TD]
[TD="bgcolor: #FFFFFF , align: left"]X[/TD]
[TD="bgcolor: #FFFFFF , align: left"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]5[/TD]
[TD="bgcolor: #FF0000 , align: right"]4[/TD]
[TD="bgcolor: #FFFFFF , align: left"]X[/TD]
[TD="bgcolor: #FFFFFF , align: left"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]6[/TD]
[TD="bgcolor: #00B050 , align: right"]5[/TD]
[TD="bgcolor: #FFFFFF , align: left"] [/TD]
[TD="bgcolor: #FFFFFF , align: left"]X[/TD]
[/TR]
</tbody>[/TABLE]
die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt

[TABLE="width: 1"]
<tbody>[TR="bgcolor: #FFCC66"]
[TD="colspan: 3"] verwendete Formeln [/TD]
[TD] [/TD]
[/TR]
[TR="bgcolor: #FFCC66"]
[TD] Zelle [/TD]
[TD] Formel[/TD]
[TD] Bereich [/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]B2:B6[/TD]
[TD]=WENN(ISTFEHLER(VERGLEICH(A2;APL!$D$2:$D$4;0));"";"X")[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C2:C6[/TD]
[TD]=WENN(ISTFEHLER(VERGLEICH(A2;APL!$D$2:$D$4;0));"X";"")[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1"]
<tbody>[TR="bgcolor: #CCFFFF"]
[TD="colspan: 11"] Bedingte Formatierung Haupttabelle 1 [/TD]
[TD] [/TD]
[/TR]
[TR="bgcolor: #CCFFFF"]
[TD] Wird angewendet auf [/TD]
[TD] Nr. Bed. Regeltyp [/TD]
[TD] Operator [/TD]
[TD] Formel1 [/TD]
[TD] Formel2 [/TD]
[TD] Format Schrift Füllfarbe [/TD]
[TD] Unterstrichen [/TD]
[TD] Schrift- farbe [/TD]
[TD] Muster [/TD]
[TD] Musterfarbe [/TD]
[TD] Typ [/TD]
[TD] Bereich [/TD]
[/TR]
[TR]
[TD]$A$2:$A$6[/TD]
[TD="align: left"]01.Bed.: Formel ist[/TD]
[TD] [/TD]
[TD="align: left"]=$B2="X" [/TD]
[TD] [/TD]
[TD="bgcolor: #FF0000, align: right"]255[/TD]
[TD="align: left"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]A2:A6[/TD]
[/TR]
[TR]
[TD]$A$2:$A$6[/TD]
[TD="align: left"]02.Bed.: Formel ist[/TD]
[TD] [/TD]
[TD="align: left"]=$C2="X" [/TD]
[TD] [/TD]
[TD="bgcolor: #00B050, align: right"]5287936[/TD]
[TD="align: left"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]A2:A6[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1"]
<tbody>[TR="bgcolor: #CCFFFF"]
[TD="colspan: 5"] Bedingte Formatierung Haupttabelle 2 [/TD]
[TD="colspan: 3"] oberere/unterer Bereich [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR="bgcolor: #CCFFFF"]
[TD] Wird angewendet auf [/TD]
[TD] Nr. Bed. [/TD]
[TD] Format Zelle [/TD]
[TD] Anhalten [/TD]
[TD] Auswahl [/TD]
[TD] Anzeige [/TD]
[TD] Anzahl [/TD]
[TD] Durchschnitt [/TD]
[TD] Typ [/TD]
[TD] Bereich [/TD]
[/TR]
[TR]
[TD]$A$2:$A$6[/TD]
[TD]01.Bed.[/TD]
[TD] [/TD]
[TD="align: left"]Falsch [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]A2:A6[/TD]
[/TR]
[TR]
[TD]$A$2:$A$6[/TD]
[TD]02.Bed.[/TD]
[TD] [/TD]
[TD="align: left"]Falsch [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]A2:A6[/TD]
[/TR]
</tbody>[/TABLE]
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.



APL[TABLE="width: 1"]
<colgroup><col style="font-weight:bold; width:40px;"><col style="width:106px;"><col style="width:106px;"><col style="width:106px;"><col style="width:128px;"></colgroup><tbody>[TR="bgcolor: #99CCFF"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Person.Nr.[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Name[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Typ[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Maschinen-Nr.[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF , align: right"]1[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Meier[/TD]
[TD="bgcolor: #FFFFFF , align: right"] [/TD]
[TD="bgcolor: #FFFFFF , align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF , align: right"]2[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Müller[/TD]
[TD="bgcolor: #FFFFFF , align: right"] [/TD]
[TD="bgcolor: #FFFFFF , align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #99CCFF, align: center"]4[/TD]
[TD="bgcolor: #FFFFFF , align: right"]3[/TD]
[TD="bgcolor: #FFFFFF , align: left"]Schulze[/TD]
[TD="bgcolor: #FFFFFF , align: right"] [/TD]
[TD="bgcolor: #FFFFFF , align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE]
<tbody>[TR="class: style21"]
[TD="align: left"]http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip[/TD]
[/TR]
[TR="class: style21"]
[TD="align: left"]http://Hajo-Excel.de/tools.htm
[/TD]
[/TR]
[TR]
[TD="align: left"]XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007[/TD]
[/TR]
[TR]
[TD="align: left"] Add-In-Version 23.02 einschl. 64 Bit[/TD]
[/TR]
</tbody>[/TABLE]



Bis später,
Karin

 

Hallo,
Danke erst mal für die schnelle Hilfe. Ich bin mir aber nicht sicher ob das dass ist was ich Suche, wahrscheinlich habe ich das auch nicht ganz verständlich erklärt. Habe hier mal die Excel eingefügt, zur Absicherung habe ich mal so ziemlich alle Beschriftungen aus der liste genommen.

Den Anhang Test.xls betrachten


Im Tabellenreiter APL unter Spalte D will ich händisch 3 stellige Maschinennummern Vergeben, sonst nichts. Im Tabellenreiter Maschinenbestand habe ich eine Liste mit vorhandenen Maschinen, jier soll farblich mariert und beschriftet werden.

Wenn ich jetzt unter APL spalte D eine 3 stellige Maschinennummer eintrage soll auf Tabellenreiter Maschinenbestand die eben vergebene Nummer farblich markiert werden. Ich habe in der Tabelle im Anhang unter APl ein paar Nummern vergeben und unter Maschinenbestand dargestellt wie das ganze dann aussehen soll.

Also wenn die Nummer vergeben wurde soll die Nummer unter Maschinenbestand Farblich markiert werden und dahinter soll stehen vergeben. wenn die Nummer nicht auf Apl vergeben ist soll unter Maschinenbestand die Nummer grün markiert werden und Frei dahinter stehen.

Und als kleines extra wenn ich auf der Seite Maschinenbestand eine Maschinennummer händisch Rot markiere soll Defekt dahinter stehen.

Sorry für die mühe die ich mache, weis mir da einfach nicht zu helfen, es wäre lieb falls ihr das Problem lösen könnt wenn ihr das für Dummies oder Newbies erklärt, beschäftige mich noch nicht so lange mit Excel.
Danke schon mal im voraus....

 

Hi,

der erste Teil wäre kein Problem - dazu würde in Spalte B diese Formel zu verwenden sein: =WENN(ISTFEHLER(VERGLEICH(A2;'PBV Benutzer'!$D$4:$D$50;0));"Frei";"Vergeben")

Aber - das Färben von Hand (rot für defekt) lässt sich nicht so ohne Weiteres auf irgendeiner Art und Weise (weder per Formel noch per VBA) auswerten.
Du müsstest dazu einen Namen (z.B. "Vergeben") definieren, um die Schriftfarbe auszuwerten und das in einer extra Spalte (C) auflisten. Beachte aber, dass du nach jeder Hand-Färbung in Spalte B die Taste F9 drücken musst, damit sich die Hilfsspalte aktualisiert.

Bis später,
Karin

 

Danke Beverly du bist die Beste :-) einfach genial..... wirklich vielen vielen dank....
Das mit der Hilf spalte finde ich gar nicht mal schlecht, wenn ich jetzt 3 eingebe springt er sofort auf defekt und ich habe zusätzlich noch mit bedingter Formatierung die schriftfarben der Wörter angepasst.

ich schäme mich schon fast zu fragen aber eine Funktion würde mir da jetzt noch gelegen kommen.
Ist es möglich das mir auf der APL Tabelle jetzt noch ein Eintrag rot markiert wird wenn er auf Maschinenbestand schon auf vergeben oder defekt steht, denn wir wollen ja nicht doppelt vergeben können, bzw. so würde man schnell merken das die Maschine schon besetzt bzw. defekt ist.
Danke für die mühe
gruß Uwe

 

Hi Uwe,

ich würde das wie folgt lösen:

1. durch Verwendung von Gültigkeitszellen verhindern, dass Maschinennummerm überhaupt erst doppelt eingegeben werden können. Dazu müssen aber die verbundenen Zellen für die Abteilungs-Überschriften aufgelöst werden

2. bedingte Formatierung mit Rot-Färbung, falls die Maschine defekt ist


Bis später,
Karin

 

Hallo Karin,

das ist leider noch ne Nummer zu groß für mich, ich habe jetzt zwei Stunden lang rum probiert und auch DR. Google konnte mir nicht weiter helfen, (liegt aber sicherlich an meinem Excel Verständnis....)

Ich hätte gerne über die Datenprüfung das er mir prüft wenn ich auf Tabelle APL eine Maschinennummer eintrage ob diese auf Tabelle Maschinenbestand schon als vergeben oder defekt ausgezeichnet ist. Wenn das so ist soll eine Kurze Fehlermeldung erscheinen.

Ich bekomm entweder bei jeder Eingabe die Fehlermeldung oder gar nicht, habe es mit wenn und wenns Formel in der Datenüberprüfung versucht, aber irgendwas mach ich wohl falsch....

Ich hoffe du kannst mir nochmal helfen und wenn möglich schritt für schritt erklären, ich Arbeite bzw. lerne erst seit wenigen Monaten mit Excel umzugehen.....

Danke schon mal im voraus....

 

Hi Uwe,

deine Mappe hat bei mir ein Problem, deren Ursache mir unklar ist: ich hatte die Änderungen, wie gestern beschrieben, in deine Mappe eingebaut - in der hochgeladenen Mappe sind sie nicht mehr vorhanden. Sobald ich sie wieder eintrage, speichere und die Mappe neu öffne, sind die Veränderungen nicht mehr da. Das ist mir äußerst rätselhaft, sodass ich annehmen muss, dass die Mappe irgendwie korrumpiert ist.

Ich kann also nur beschreiben was ich meine:

1. markiere D4: D50, gehe auf Daten -> Datenüberprüfung, wähle Benutzerdefiniert aus und gib unter Formel ein =ZÄHLENWENN($D$4:$D$50;D4)=1

2. markiere D4: D50, gehe auf bedingte Formatierung, gib als Bedingung ein Formel ist: =INDEX(Maschinenbestand!$A$2:$A$47;VERGLEICH(D4;Maschinenbestand!$A$2:$A$47;0);4)=3 und wähle als Füllfarbe Rot aus.


Bis später,
Karin

 

Hola,

die Datei ist nicht korrupt!
Das die Eintragungen weg sind liegt schlicht an der Tatsache, dass diese als xls gespeichert ist. Erst ab Excel 2010 ist es möglich, in der bedingten Formatierung durch einfachen Zellverweis auf ein anderes Tabellenblatt zu verweisen. Vorher muss das per vergebenem Namen geschehen.

Wenn die Datei nicht als xlsx gespeichert wird, denkt Excel, die Version ist vor 2007 und löscht somit die bedingte Formatierung.

Gruß,
steve1da

 

Hi Uwe,

im Anhang die Mappe unter Verwendung von definierten Namen für die Bereich in der anderen Tabelle - hatte leider nicht bemerkt, dass es sich um eine xls-Datei handelt.
Ist nur seltsam, das Excel keine Warnung ausgegebn hat - macht es doch sonst in jeder wichtigen und unwichtigen Situation...

Bis später,
Karin

 

Hallo Karin,

die Datenprüfung klappt super, das mit der bedingten Formatierung war so nicht gemeint.
Ich habe dir die Datei nochmal erstellt, jetzt als .xlsx und ich habe alle Informationen in der Tabelle gelassen die ich kann. Datenschutz usw....

Das was mir jetzt noch fehlt ist das die defekten Geräte nicht ausgewählt werden können.

Auf Tabellenblatt PBV Benutzer trage ich in Spalte D (Talkman Nr.) dem Mitarbeiter seine Talkmannr. ein. Bei einer doppelten vergabe erscheint jetzt sofort die Meldung das diese Nummer schon vergeben ist, so wie du es in deinem letzten Post ja gelöst hast.
ABER....
Wenn ich eine Talkmannr. eingebe die auf Tabellenblatt 2 (Talkmanbestand) als defekt deklariert ist kommt die vor dem ABER erwähnte Blockade in form einer Fehlermeldung nicht.
Auch hier würde ich für die Benutzer gerne die Eingabe blocken, sodass die Mitarbeiter auf Tabelle 1 (PBV Benutzer) erst gar nicht die Möglichkeit bekommen eine vergebene oder defekte Maschine einzutragen.

Im Prinzip hätte ich gerne das die selbe Meldung der Datenprüfung kommt für vergebene wie für defekte Talkman....

Ich hoffe das ich dass halbwegs Verständlich erklärt habe und das du mir auch diesmal so souverän wie zuvor weiterhelfen kannst.

 

Hi Uwe,

genau das geht nicht: es lassen sich nicht 2 Bedingungen in die Gültigkeitsliste integrieren - aus diesem Grund hatte ich die bedingte Formatierung verwendet.
Wenn beides als Fehlermeldung ausgegeben werden soll, dann lässt sich die Fehelermeldung für die defekten Geräte nur mit VBA lösen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngBereich As Range
    Dim rngDefekt As Range
    Set rngBereich = Union(Range("D5:D10"), Range("D11:D19"), Range("D21:D26"), Range("D28:D35"), _
        Range("D37:D47"), Range("D49"), Range("D51"), Range("D53"))
    If Not Intersect(Target, rngBereich) Is Nothing Then
        If Target.Cells(1) <> "" Then
            Set rngDefekt = Worksheets("Talkmanbestand").Range("A4:A49").Find(Target.Cells(1), lookat:=xlWhole)
            If Not rngDefekt Is Nothing Then
                If rngDefekt.Offset(0, 1) = "Defekt" Then
                    MsgBox "Gerät defekt"
                    Application.EnableEvents = False
                    Application.Undo
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
    Set rngBereich = Nothing
    Set rngDefekt = Nothing
End Sub

Bis später,
Karin

 

Danke funktioniert wie es soll, du bist die beste.....

Kleine Anmerkung, habe den MSG Text abgeändert und habe Range geändert auf D5 : D55 weil sich die liste nahezu täglich ändert. funktioniert aber Einwand frei :-)
Nochmal vielen vielen dank....

Jetzt kann das Thema geschlossen werden