Wert mit XVerweis ermitteln, anzeigen mit Bed.-Form.

[Bertel21]

Hallo zusammen,

ich habe eine 2-Dim-Tabelle, in Spalten stehen Monate, in Zeilen Bundesländer. In einer Zelle N6 ermittele ich den Wert je nach Monat/Bundesland mit einer verschachtelten XVERWEIS-Funktion. In der Tabelle wir der ermittelte Wert mit einer bed. Form. durch Vergleich mit der Zelle N6 eingefärbt. Funktioniert alles.

Wenn jetzt aber zufällig eine andere Zelle (je nach Monat/Bundesland) den gleichen Zahlenwert hat, wird diese natürlich ebenfalls eingefärbt. Das soll nicht passieren.

Hatte gedacht, wenn ich jetzt die Formel der Zelle N6 direkt in die bedingte Formatierung kopiere (also ohne Zell-Vergleich) müsste das so funktionieren, tut es aber nicht.

Gibt es Vorschläge was ich tun könnte?

N6:        =XVERWEIS(L6;C7:C22;XVERWEIS(M6;D6:I6;D7:I22;;0);"nicht da";0)
L6:         Eingabe Bundesland
M6:        Eingabe Monat
C7:C22   hier stehen Bundesländer
D6:I6     hier stehen Monate
D7:I22   hier stehen Zahlen

Vielen Dank für die Unterstützung und viele Grüße

Bert

[BoskoBiati]

Hallo,

zeige mal die Tabelle, damit man das besser nachvollziehen kann.

[HKindler]

Hi,

Formel in
N6: =WENNFEHLER(INDEX(D7:I22;O6;P6);"nicht da")
O6: =XVERGLEICH(L6;C7:C22)
P6: =XVERGLEICH(M6;D6:I6)

Als Formel in der bed.Form:
=((ZEILE(D7)-ZEILE($C$6))=$O$6)*((SPALTE(D7)-SPALTE($C$6))=$P$6)

[UweD]

Hallo

meinst du das so?

Arbeitsblatt mit dem Namen 'Tabelle1' C D E F G H I J K L M N 5 Bundesland Monat Ergebnis 6 April Mai Juni Juli August September Bayern Juli 4 7 Baden-Württemberg 1 2 3 4 5 6 8 Bayern 11 122 33 4 45 5 9 Berlin 22 33 44 5 5 6 10 Brandenburg 22 64 51 82 45 32 11 Bremen 47 59 28 94 51 33 12 Hamburg 90 24 21 23 45 94 13 Hessen 42 31 64 17 84 60 14 Mecklenburg-Vorpommern 94 47 95 85 50 14 15 Niedersachsen 86 10 79 56 67 12 16 Nordrhein-Westfalen 39 24 59 95 25 34 17 Rheinland-Pfalz 91 10 17 48 68 54 18 Saarland 77 23 65 93 94 77 19 Sachsen-Anhalt 19 74 34 55 19 78 20 Sachsen 60 87 89 75 51 21 21 Schleswig-Holstein 38 30 67 35 74 61 22 Thüringen 78 95 56 67 17 13 23
Zelle Formel N6 =XVERWEIS(L6;C7:C22;XVERWEIS(M6;D6:I6;D7:I22;;0);"nicht da";0)	Zelle bedingte Formatierung... Format D7 1: ($C7=$L$6)*(D$6=$M$6) abc	Zelle Gültigkeitstyp Operator Wert1 Wert2 L6 Liste =$C$7:$C$22 M6 Liste =$D$6:$I$6

LG UweD

---

Edit:
Alternative Formel für N6: =INDEX(D7:I22;VERGLEICH(L6;C7:C22;0);VERGLEICH(M6;D6:I6;0))

Fehler kommt nicht vor, wenn du die Datenüberprüfung, wie oben vorgeschagen, über die Dropdowns machst

[BoskoBiati]

Hi,

ich würde da sowieso die alternative Formel von UweD nehmen, mit einer kleinen Abwandlung:

Code:

=INDEX($D$7:$I$22;VERGLEICH($L$6;$C$7:$C$22;0);Monat(M6&0)-3)

[Bertel21]

Hallo zusammen,

ich bin richtig baff, so schnell so gute Antworten. 

Es hat geklappt.

@UweD
Ja, genauso war es gemeint. Ich hatte natürlich vorher einiges herumprobiert, eigentlich hätte ich selber drauf kommen können, für die bedingte Formatierung
nicht die Zelle N6 zu nehmen, sondern direkt die waagerechte (Monate) und senkrechte (Bundesländer) Position zu ermitteln. 
Vielen lieben Dank für die Mühe.

@BoskoBiati
ja, bei älteren Excel-Versionen gibt es ja die Funktion XVERWEIS nicht, da wäre die Variante mit Index und Vergleich sicher die bessere, allgemeingültigere Version.

Nochmal vielen vielen Dank an alle für die Unterstützung.

VG Bert

[Bertel21]

Nochmal ein freundliches Hallo,

eine Verständnisfrage zum Kopieren von bedingten Formatierungen: 

In der Beispiel-Tabelle trage ich in D7 für die bedingte Formatierung ein: =($C7=$L$6)*(D$6=$M$6).

Wenn ich diese Formatierung dann nach rechts und unten kopiere wirken die $-Zeichen, alles funktioniert.

Wenn ich mir dann aber die bedingte Formatierung für z .B. E8  anschaue, steht da dann auch =($C7=$L$6)*(D$6=$M$6) drin wie in D7.

Müsste da nicht $C8 (Zeile ändert sich) sowie E$6 (Spalte ändert sich) drin stehen?

Mache ich da einen Denkfehler, ist das eine Wissenslücke von mir, ist das ein Programmier-(Anzeige)-Fehler?

Ist nicht weiter tragisch, es funktioniert ja so wie es soll. Mich irritiert einfach die Anzeige.

VG Bert

[BoskoBiati]

Hi,

markiere den gesamten Bereich und trage die Formatierung ein, dann gilt diese für alle Zellen, angezeigt wird aber nur die Formel für die Zelle links oben:

   

Im Übrigen sind die alten Funktionen auch in den neuen Versionen nicht zu verachten.