Bedingte Formatierung über 2 Tabellenblätter wer kann mir da weiterhelfen?
Hallo liebe Excel Experten,
Ich habe 2 Tabellenblätter mit jeweils Werten in A1 bis F1 und ca 500 Zeilen.
Nun möchte ich die Zellen in Tabellenblatt 2 formatieren dass wenn zB die Werte in Zeile 1 also A1:F1 identisch sind mit Werten in Tabellenblatt 1 ,
zB Tabellenblatt 2 A1:F1 stehen die Werte 10,20,30,40,50,60 und in Tabellenblatt 1 stehen in A499:F499 die Werte 60,30,40,50,10,20 dann soll die Formatierung der Zellen A1:F1 in Tabellenblatt 2 in Kraft treten .
das heißt die Werte sind nicht sortiert.
Ich würde mich sehr über Lösungsvorschläge freuen , dafür im voraus vielen Dank
4 Antworten
Damit es nicht im Sumpf der Kommentare untergeht
Mit einer Hilfsspalte in Tabelle 1
=KGRÖSSTE(A1:F1;4)&" "&KGRÖSSTE(A1:F1;3)&" "&KGRÖSSTE(A1:F1;2)&" "&KGRÖSSTE(A1:F1;1)
Damit werden die Zahlen aufsteigend aneinandergereiht.
In Tabelle 2 kann dann einfach auf die Hilfsspalte gesucht werden:
=ZÄHLENWENN(Tabelle1!G:G;KGRÖSSTE(A1:F1;4)&" "&KGRÖSSTE(A1:F1;3)&" "&KGRÖSSTE(A1:F1;2)&" "&KGRÖSSTE(A1:F1;1))
In meinem Beispiel waren es nur 4 Spalten, das Prinzip funktioniert auch mit 6, dann wird die Formel halt länger.
Mit neu verstandener Anforderung: Wenn einer der Werte aus Tabelle2!Zeile 1 in der Tabelle 1 auftaucht, sollen alle Zellen A:F in dieser Zeile gefärbt werden.
=(ZÄHLENWENN(Tabelle1!$A:$F;$A2)+ZÄHLENWENN(Tabelle1!$A:$F;$B2)+ZÄHLENWENN(Tabelle1!$A:$F;$C2)+ZÄHLENWENN(Tabelle1!$A:$F;$D2)+ZÄHLENWENN(Tabelle1!$A:$F;$E2)+ZÄHLENWENN(Tabelle1!$A:$F;$F2))>0
Unelegant, funktioniert aber. Eine Variante geht nocht mit Summenprodukt
=SUMMENPRODUKT((Tabelle1!$A$1:$F$500=$A1)+(Tabelle1!$A$1:$F$500=$B1)+(Tabelle1!$A$1:$F$500=$C1)+(Tabelle1!$A$1:$F$500=$D1)+(Tabelle1!$A$1:$F$500=$E1)+(Tabelle1!$A$1:$F$500=$F1))>0
Eine der Formeln in die bedingte Formatierung und den Bereich entsprechend für alle betroffenen Zellen A:F in Tabelle2 einrichten.
Ok jetzt verstanden dass alle Werte der Zeile auch in der anderen vorkommen müssen. Dann bringen die Formeln nichts. Ich schau morgen ob ich eine Idee hab
Die einzige Idee wäre allerdings mit einer Hilfsspalte in Tabelle 1
=KGRÖSSTE(A1:F1;4)&" "&KGRÖSSTE(A1:F1;3)&" "&KGRÖSSTE(A1:F1;2)&" "&KGRÖSSTE(A1:F1;1)
Damit werden die Zahlen aufsteigend aneinandergereiht.
In Tabelle 2 kann dann einfach auf die Hilfsspalte gesucht werden:
=ZÄHLENWENN(Tabelle1!G:G;KGRÖSSTE(A1:F1;4)&" "&KGRÖSSTE(A1:F1;3)&" "&KGRÖSSTE(A1:F1;2)&" "&KGRÖSSTE(A1:F1;1))
In meinem Beispiel waren es nur 4 Spalten, das Prinzip funktioniert auch mit 6, dann wird die Formel halt länger.
wenn du bitte die letzte Antwort mit deiner Formel als Antwort schreibst , dann kann ich sie als hilfreichste Antwort auszeichnen, leider geht das im Kommentar nicht !
Als Formel für die bedingte Formatierung:
=ZÄHLENWENN(Tabelle1!$A1:$F1;A1)>0
Jede Zelle, die in der gleichen Zeile auf Tabelle1 vorkommt, wird markiert. Ist das richtig verstanden, oder müssen alle Werte der Zeile auch in der anderen Tabelle stehen?
die Werte in Tabelle1 und 2 müssen identisch sein auch wenn die Werte nicht in der gleichen Zelle stehen, um es mal so auszudrücken soll Excel die Werte in Tabelle2 Zeile 1 nehmen und nun mit allen 500 Zeilen in Tabelle1 vergleichen ob diese Werte dort vorkommen , wenn ja , dann Zeile1 markieren
Das ist eine typische Aufgabe, die mit VBA relativ elegant und zügig gelöst werden könnte. Ich vermute aber, dass du die Excelvariante bevorzugst.
Ich nenne meine Tabellenblätter der Einfachheit halber T1 und T2. Die Werte der Tabellen fangen bei mir in Zeile 4 an.
Im Moment kann ich nur eine Lösung auf Zeilenebene bieten. Damit kannst du überprüfen, ob die Zeile aus Tabellenblatt zwei mit einer bestimmten Zeile in Tabellenblatt 1 identisch ist.
Man müsste die Matrixformel noch irgendwie erweitern, sodass das Max über alle Zeilenvergleiche ausgegeben wird, soll heißen 1, falls sich die zu untersuchende Zeile mit einer aus Tabelle 1 deckt und 0, falls es keine vollständige Zeilenübereinstimmung gibt.
=UND(KKLEINSTE('T1'!$A$8:$F$8;SPALTE(A1:F1))=A4:F4)
Die Eingabe muss mit Strg + Shift + Enter abgeschlossen werden.
Die Zeilennummer 8 in 'T1'!$A$8:$F$8 muss noch flexibilisiert werden.
Hallo und danke, kannst du mir sagen wie das mit VBA ablaufen könnte, ich habe von VBA keine Ahnung
Das ist das Problem. Wenn du keine Ahnung hast, kannst du
a) das Programm nicht implementieren (ohne Anleitung)
b) das Programm nicht warten
Aber gut. Ich skizziere mal das Programm:
Function ZeileVorhanden(Suchzeile As Range, Pool As Range) As Boolean
Dim bGefunden
Dim i As Integer
Dim j As Integer
For i = 1 To Pool.Rows.Count
If WorksheetFunction.CountBlank(Pool.Rows(i)) = 0 Then
bGefunden = True
For j = 1 To Pool.Columns.Count
If WorksheetFunction.Small(Pool.Rows(i), j) <> Suchzeile.Cells(1, j) Then
bGefunden = False
Exit For
End If
Next
If bGefunden Then
Debug.Print "Gefunden in Zeile " & i + Pool.Row - 1
ZeileVorhanden = True
Exit Function
End If
End If
Next
ZeileVorhanden = False
End Function
Aufruf:
=Zeilevorhanden(A4:F4;'T1'!A4:F500)
Auf Basis dieses Ergebnisses kannst du eine bedingte Formatierung definieren.
Hallo und danke erstmal, leider stimmt etwas mit der Formel nicht, ich habe beide probiert und beides mal bleibt die Formatierung bestehen sowohl bei Werten die in Tabelle1 stehen und auch bei Werten die nicht in Tabelle1 stehen????
nehmen wir für Tabelle2 jetzt mal nur Zeile 1 mit 6 Werten, von A1:F1 folgende Werte : 10,20,30,40,50,60 , ich möchte nicht wissen wo diese Werte überall drin sind, sondern, ob genau diese 6 Werte in Tabelle1 in den 500 Zeilen von A1:F500 schon mal genau so vorkommen, dabei darf es keine Rolle spielen in welcher Reihenfolge diese Werte sich in Tabelle1 als Beispiel in Zeile A444:F444 stehen diese Werte: so herum: 60,30,40,10,50,20 dann soll die Zeile A1:F1 in Tabelle2 formatiert werden!
Gruß Uwe