Office: (Office 2007) Zellen automatisch füllen wenn Bedingungen erfüllt

Hallo liebe Excel - Experten,

ich habe folgendes Problem (eine Beispieldatei habe ich angehängt).
Ich möchte erreichen, dass sich in der Zeitschiene im Tabellenblatt 2 die Zellen automatisch mit dem Text "Projektphase 1, 2 usw." in der entsprechenden KW im entsprechenden Monat füllen, wenn das Datum in Tabellenblatt 1 in die jeweilige KW passt und die Projektnummern übereinstimmen. Ich hoffe, das ist einigermaßen verständlich dargestellt.

Ich würde mich sehr freuen, wenn jemand eine Lösung weiß. Vielen Dank.

Grüße,
Querist

:)

 

Hallo Querist,

Mit dieser Formel, die in die Zelle B5 kommt, solltest Du das Ziel erreichen (Projektphase 1-5):

Code:

Du kannst die Formel in die restlichen Zellen nach rechts und nach unten kopieren. Mit der bedingten Formatierung kannst Du dann die Farben noch entsprechend setzen.

Viele Grüsse
Gipfelstürmer

 

Hallo Gipgelstürmer,

super, herzlichen Dank für Deine Antwort *Smilie

Ich hab allerdings Bedenken wegen der Funktion Kalenderwoche, da ich excel 2007 verwende und gelesen habe, dass diese teilweise bis excel 2007 falsch rechnet. Siehe:

http://excelformeln.de/formeln.html?welcher=7

Ich habe jetzt im Tabellenblatt 1 noch eine Spalte eingefügt, in der die Kalenderwoche nach der im oben angegebenem Link beschriebenen Formel ausgerechnet wird.

Die Zeitschiene in der Originaldatei geht zudem noch bis 2017. Wie kann ich gewährleisten, dass die Formel auch das richtige Jahr erkennt?

Würde mich freuen, wenn Du/ jmd weiter wüßte?

Viele Grüße,
Querist

 

Guten Abend Querist,

Ja, meine Formel hätte die Jahre nicht berücksichtigt. Dass Excel die Kalenderwochen nicht richtig rechnet, wusste ich nicht.

Damit ist die Formel jetzt etwas komplizierter geworden:

=WENN(UND(JAHR(MAX($B$2:B$2))=JAHR(SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;2;FALSCH)) ;SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;3;FALSCH)=B$4);"Projektphase 1";WENN(UND(JAHR(MAX($B$2:B$2))=JAHR(SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;4;FALSCH)) ;SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;5;FALSCH)=B$4);"Projektphase 2";WENN(UND(JAHR(MAX($B$2:B$2))=JAHR(SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;6;FALSCH)) ;SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;7;FALSCH)=B$4);"Projektphase 3";WENN(UND(JAHR(MAX($B$2:B$2))=JAHR(SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;8;FALSCH)) ;SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;9;FALSCH)=B$4);"Projektphase 4";WENN(UND(JAHR(MAX($B$2:B$2))=JAHR(SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;10;FALSCH) );SVERWEIS($A5;Tabelle1!$A$2:$ZZ$300;11;FALSCH)=B$4);"Projektphase 5";"")))))

Ich habe die Excel-Datei mit den Formeln hochgeladen.

Liebe Grüsse
Romedi

 

2. Versuch: es hatte noch ein Fehler in der hochgeladenen Datei

Gruss,
Romedi

 

Hey Romedi,

wow super genial *boah, ich ziehe meinen Hut *tongue.gif*

Ich danke dir vielmals und wünsche dir noch einen schönen Sonntag abend *Smilie

Viele liebe Grüße,
Angie

 

Hey Romedi,

mittlerweile sind einige Projekte in die oben beschriebene datei eingefügt worden, womit jedes Mal auch durch Kopieren die Formel in immer mehr Zellen hinterlegt wurde. Das hat dazu geführt, dass die Excel Datei sehr langsam wurde. Weißt du eine Lösung wie man die Geschwindigkeit wieder normalisieren könnte? Ist es vlt. möglich das gleiche Ziel der automatischen Anzeige der Projektphasen anstatt der Formel auch mittels eines Makros zu erreichen? Würde mich sehr freuen, wenn du mir da weiterhelfen könntest.

Viele liebe Grüße

 

Hallo Angie,

Ich denke, da lässt sich schon etwas machen. Wie viel schneller es wird, sehen wir ja dann.
Ich schlage eine Funktion in VBA vor:
Code:

In die Zelle B5 kommt dann die Formel:
=projektphase(B$4;$A5;MAX($B$2:B$2);VERGLEICH($A5;Tabelle1!$A$1:$A$800;FALSCH))

Wenn die Resultate aktualisiert werden müssen, wenn Du in Tabelle1 ab Spalte B etwas änderst, gibst Du einen mehrspaltigen Bereich vor:
=projektphase(B$4;$A5;MAX($B$2:B$2);VERGLEICH($A5;Tabelle1!$A$1:$ZZ$800;FALSCH))

Da der PC mit Excel2007 bei uns bis auf weiteres besetzt ist, konnte ich das Makro nicht in Deiner Beispieldatei ausprobieren.
Noch etwas: Die Funktion musst Du in ein VBA-Modul kopieren. Die Formel kannst Du nach rechts und nach unten kopieren.

Viele Grüsse
Romedi

 

Hi Romedi,

tausend Dank für deine Antwort *Smilie !!!
Habs in meiner Beispieldatei ausprobiert und klappt wunderbar.
Leider ist es so, dass die Projektphasen in Wirklichkeit alle andere Namen haben, die Abkürzungen von englischen Begriffen sind, also z.B. Projektphase 1 = CM Projektphase 2= DR etc. Sorry, das hätte ich erwähnen müssen *frown.gif* !! Das macht die Angelgenheit wahrscheinlich um einiges komplizierter, oder?

Liebe Grüße,
Angie

 

Hi Angie,

Das ist auch kein Problem*Smilie . Statt
Projektphase = "Projektphase " & p
machst Du ein Select Case im Code:
Code:

LG
Romedi

 

Hey Romedi,

das ist ja toll, danke schön *dance

Irgendetwas klappt bei mir mit der 2. Formel (=projektphase(B$4;$A5;MAX($B$2:B$2);VERGLEICH($A5;Tabelle1!$A$1:$ZZ$800;FALSCH) ) nur noch nicht, es kommt die Fehlermeldung #WERT? An was könnte das liegen? Die erste klappt wie gesagt super.

Viele liebe Grüße,
Angie

 

Hallo Angie,

Das mit der Erweiterung des Bereichs war demnach keine so gute Idee von mir. Also machen wir es anders:
=projektphase(B$4;$A5;MAX($B$2:B$2);VERGLEICH($A5;Tabelle1!$A$1:$A$800;0)+0*SUMME(Tabelle1!$A$1:$ZZ$800))

Wenn Du nur 300 Zeilen hast, nimmst Du besser den Bereich von $A$1:$ZZ$300, damit die Performance nicht darunter leidet.

Ist übrigens die Performance mit der VBA-Funktion wesentlich besser geworden, als sie vorher war?

LG
Romedi

 

Hallo Angie,

Ich habe noch einen Nachtrag: Um die Aktualisierung der Formeln sicherzustellen, gibt es noch eine bessere Möglichkeit, die keine Performance wegfrisst (wie es bei summe(Bereich) der Fall ist):

*	B	C	D	E	F
2	Jan 11
3	Kalenderwoche
4	1	2	3	4	5
5	CM	*	*	*	*

Zelle	Formel
B5	=projektphase(B;$A5;MAX($B:B);VERGLEICH($A5;Tabelle1!$A:$A0;0)+0*JETZT())

LG
Romedi

 

Hi Romedi,

sorry, dass ich erst jetzt antworte!!!!
Bin erst jetzt dazugekommen die Formel zu testen, funktioniert super *Smilie
Jetzt hab ich nur leider schon wieder ein Problem, und zwar ist es in der Originaldatei jetzt so, dass zwischen der Projektnr und der ersten Projektphase in Tabelle1 noch andere Spalten dazwischen stehen. Die erste Projektphase CM steht erst in Spalte M anstatt B, die restlichen Projektphasen wie gehabt im Anschluss daran also Projektphase 2 in Spalte O etc.... Da ich leider von VBA keine Ahnung habe, weiß ich nicht wie ich es umschreiben kann, dass es wieder passt *confused.gif*
Würde mich sehr freuen, wenn du mir nochmals behilflich sein könntest...

Viele liebe Grüße *winken,
Angie

 

Hallo Angie,

*rolleyes.gif* Ja, das ist schon lange her... Wenn ich das richtig gerechnet habe, sind da 11 Spalten dazu gekommen. Dann lautet der Code so:

Code:

In der Formel musst Du nichts anpassen, weil dort nur die 1. Spalte verwendet wird.

LG
Romedi