Office: sverweis und Wochenenden in Matrix darstellen

Hallo,

ich habe zwei Probleme, welche ich aktuell nicht gelöst bekomme.
Nr. 1
Ich habe eine Jahresübersicht in Form einer Matrix, wo ich nur die WE farblich dargestellt bekommen möchte. Über bedingte Formatierung bekomm ich das nicht hin, wäre über jeden Hinweis dankbar.

Nr. 2
Ich habe einen Urlaubsplan welcher von li. nach rechts aufgebaut ist, wo entsprechend dann "U" drinnen steht. Der wird mittels sverweis auf die o.g. Matrix projiziert.
=SVERWEIS($A$32;$A$2:$AI$28;SPALTE(E$2);0)

Dies funktioniert aber nur, wenn die Matrix sich auf dem selben Tabellenblatt befindet. Wenn ich diese auf einem Extra Blatt haben möchte, steht da nur noch #NV....
=SVERWEIS($A$1;'2024'!A2:NF28;'2024'!E2$;0)

 

Hola,
bitte lade eine Exceldatei hoch ansonsten ist das pures Raten.
Deine beiden Sverweis() Formeln sind a) unterschiedlich und b) kann Spalte(E$2) so bleiben.
Gruß,
steve1da

 

Hi,

zu 1)
irgendwo auf dem Blatt muss die Jahreszahl stehen. Sagen wir mal dies sei in A1 der Fall. Dann markierst du B2:AF13 und richtest eine bed.Form. mit der Formel
=(WOCHENTAG(DATUM($A$1;$A2;B$1);2)>5)*(FORMAT(DATUM($A$1;$A2;B$1);"MMMM")=$A2)
ein.
Wenn es dir egal ist, dass bei kürzeren Monaten z.B. der "31.Februar" ebenfalls als Wochenende markiert werden könnte, dann kannst du den Teil nach dem *-Zeichen auch weg lassen.

 

@HKindler
Die Formel funktioniert leider nicht, es wird aktuell nichts markiert. Habe sie in die hochgeladene Datei bereits eingebaut

@steve1da
Ich konnte die Datei nicht hochladen, da sie zu groß sei (2,56MB)
Habe die Excel jetzt auf Dropbox hochgeladen...
Dropbox - Excel - Simplify your life

Vielen Dank schonmal

 

=WOCHENTAG(DATUM($A$15;MONAT($A2&0);WECHSELN(B$1;".";""));2)>5

 

Hi Densch,

das ist der Nachteil von Bildern im Gegensatz zu einer Datei. Ich habe z.B. schlicht übersehen, dass du in Zeile 1 nicht 1, 2, 3 ... sondern 1., 2., 3., ... stehen hast. Das führt dann natürlich bei DATUM() zu einem Fehler.
Genauso wie z.B. Januar statt 1 also Monat bei DATUM(). Auch das ist mein Fehler. Und wieder fehlenden Datei geschuldet. Denn ich bin zu faul aus einem Bild eine Datei nachzubauen. Daher habe ich die Formel nicht getestet und den Fehler nicht bemerkt.
Auch FORMAT() statt TEXT() zu verwenden ist ein beliebter Fehler von mir, da Format() in VBA TEXT() von Excel entspricht. Auch hier hätte eine Beispieldatei geholfen.

Ich werde mir jedoch keine 2,5MB von Dropbox runter laden (ist hier so oder so gesperrt). Wenn deine Datei zu groß ist, musst du sie halt auf den notwendigen Teil abspecken.

So müsste es klappen:
=(WOCHENTAG(B$1&$A2&$A$1;2)>5)*(TEXT(B$1&$A2&$A$1;"MMMM")=$A2)

 

Vielen Dank erstmal für eure Unterstützung. Habe jetzt ein Teil nochmal in eine neue Excel Tabelle kopiert und nun funktioniert auch der sverweis zu einem anderen Blatt wieder :-) Die Datei ist jetzt auch nur noch15kb groß, keine Ahnung was an der Originaldatei schief gelaufen ist.
Habe die Datei jetzt mal angehangen...

@HKindler
=(WOCHENTAG(B$1&$A2&$A$1;2)>5)*(TEXT(B$1&$A2&$A$1;"MMMM")=$A2) --> läuft leider nicht :-(

@steve1da
=WOCHENTAG(DATUM($A$15;MONAT($A2&0);WECHSELN(B$1;".";""));2)>5 --> funktioniert :-)

bei der Jahresmatrix muss ich für jeden 1. am Monat den Spaltenindex manuell anpassen
Januar SPALTE(E$2); Februar SPALTE(AJ$2); .....
Lässt sich dies mittels Formel auch automatisch erledigen, so dass ich nur ein sverweis anlege und dies auf die anderen Zellen kopiere!? Hoffe es war verständlich

 

Hi,

wenn du meine Anweisungen befolgen würdest, dann würde meine Formel klappen. Ich schrieb:

Wenn du in A1 "MA 9" stehen hast, statt der Jahreszahl, dann brauchst du dich über eine nicht funktionierende Formel nicht zu wundern. Also schreib in meiner Formel statt $A$1 eine Zelle, in der die Jahreszahl steht, z.B. so wie bei Steve '2024'!$A$1, und alles funktioniert.
Vorteil bei meiner Formel: Du brauchst keinen zerstückelten Bereich bei "Wird angewendet auf:" sondern kannst einfach =$B$2:$AF$13 verwenden.

 

Klappt doch :-) Hab ich leider übersehen, Danke dafür

 

Hab noch eine Frage.
Wie müsste die Formel lauten, wenn ich neben den Wochenende auch die Feiertage entsprechend markieren möchte.
Würde mit einer zweiten bedingten Formatierung arbeiten wollen aber bekomm den Abgleich nicht hin :-(
Die Feiertage steht auf einem extra Blatt. Siehe Anhang

 

Das wird mit Zählenwenn() in der bedingten Formatierung erreicht, also:
=zählenwenn(Bereich_mit_den_Feiertagen;Datum)
Wie du das Datum ermittelst weißt du ja jetzt.

 

Schön wäre es, es ist für mich nicht so einfach die Formeln nachzuvollziehen.
Versuche bereits mit Google mir das beizubringen aber leider komme ich hier nicht weiter.

 

=ZÄHLENWENN(Infos!$A$2:$A$12;DATUM('2024'!$A$1;MONAT($A2&0);WECHSELN(B$1;".";"")))

 

Danke Dir, hatte nur ">5" weggelassen aber nicht noch die "2".
Jedoch wird hier der 31.4. als Feiertag markiert, aber dieser steht weder in der Liste noch ist dieser existent

 

Excel wandelt das automatisch in den 01.05.2024 um und der ist ja ein Feiertag. Muss ich mal nachdenken, oder du spielst etwas mit Helmuts Formel rum - eventuell klappt es da, hab ich nicht getestet.