Office: Werte übernehmen mit Zeitspiegel Verschiebung per VAB-Formel

Hallo verehrte Damen und Herren,

bin bei meinem Problem an meine Grenzen gestoßen.Hoffe es kann mir hier geholfen werden.

IST zustand: Siehe auch angehängte Datei
Tabellenblatt "Daten Planung"
-Spalte C Mit Listenbox (Daten aus "Datenbasis" B4-B11)
-Spalte H mit SVERWEIS aus Spalte C Listenbox u. "Datenbasis" Spalte AZ. Hier erscheint dann der "Start" Wert.
-Bedingte Formatierung Farbe: Spalte G "Datum" mit "Datum" I2:DR2. Somit sehe ich abhängig vom Datum Spalte G wann der Start ist.

Werte in Tabellenblatt "Datenbasis" sind Fest.

Folgendes soll in "Daten Planung" ausgeführt werden:

1. Durch Eingabe Datum in Spalte G soll der Wert aus ("Datenbasis" Spalte AZ "Start") in die Spalte mit gleichem Datum I2:DR2 mit abgleich der ausgewählten Position Listenbox Spalte C übernommen werden.
Hierbei sollen die Werte Links und Rechts vom Wert Blatt Datenbasis "Start" bis Anfang Datum Spalte I "Daten Planung" (Jan. 2008) übernommen werden. Z.B. Start Datum Okt. 2008 Dann übernehme werte maximal bis Jan. 2008 (9 Zellen lins vom "Start" und alle Werte Rechts (Nov. 2008-Jun. 2017).

2. Die übernommen Werte sollen mit dem Wert % Spalte E "Daten Planung" berechnet werden, wenn kein Wert dann keine Berechnung.

Durch Auswahl in Listenbox Spalte C und Eingabe Start Datum Spalte G
sollen die Werte aus "Datenbasis" automatisch mit Anpassung Zeitspiegel übernommen werden.
Es ist wichtig das nur Werte aus dem gleichen Bereich (Zeile) übernommen wird, was im Listenbox ausgewählt wurde.

Ich glaube dass diese Funktionen nur mit VBA möglich ist und hoffe hier auf Unterstützung. Wenn es mit Formeln lösbar ist, wäre ich auch dankbar.

 

Hallo,

Glauben heißt, nicht wissen!

Vorab: Ist das Ganze noch aktuell? Wenn ja, dann Folgendes:

1. Du hast einen langen Sermon hier abgelassen, aber wenig Nachvollziehbares.
2. Deine SVERWEIS-Formel in H:

Code:

=SVERWEIS($C4;Datenbasis!$B$4:$BK$11;51;1=0)

entspricht nicht der Syntax eines vernünftigen SVERWEIS! Was willst Du da erreichen?
3. Wo soll der Start in der Datenbasis angesiedelt sein? Immer da wo Start steht, oder ist das variabel, je nach Startdatum?
4. Wo sollen die Werte herkommen, aus einer Spalte, oder aus Allen? Nach welchen Kriterien? Du hast 114 Wert-Spalten in Planung, aber nur 59 in Datenbasis.
5. Warum hast Du die Spalten A-H mit Filtern versehen? Du brauchst doch eher eine Listbox bzw. Listen, die aber anders erzeugt werden.
Zu guter Letzt: Was soll am Schluß herauskommen?

Wenn ich es richtig verstanden habe, ist das mit 1 zweizeiligen Formel erledigt.

Gruß

Edgar

 

Hallo Edgar,

danke für deine Antwort.

Es ist für mich nicht einfach gewesen, das Problem zu schildern.
Wo fange ich da am besten an ? Dann fing ich an Schritt für Schritt es zu erläutern.
Wenn es wirklich mit 1 zweizeiliger Formel erledigt werden kann, wäre ich natürlich Dankbar.
Zu deiner Frage SVERWEIS Spalte H im Blatt „Daten Planung“ ist nur für die Übersicht.
Hier kann ich dann gleich den Start Wert Spalte AZ „Monat NULL „ vom Blatt „Datenbasis“ sehen.
Über diese Übersicht habe ich dann eine Bedingte Formatierung gemacht, damit die jeweilige Zelle
Gelb markiert wird. Bedingte Formatierung (=$G4=I$2).

1. Meine Bezugsquelle ist das Blatt "Datenbasis".
Hier ist die Spalte AZ "Start", also Monat NULL, primärer Kriterium Punkt.
Wenn ich im Blatt "Daten Planung" Spalte G "Start" ein Datum eingebe, soll es im gleichen Blatt Zeile 2 (Spalte I bis GR ) diesen Datum abgleichen und den Wert aus dem Blatt "Datenbasis" Spalte AZ "Start" also Monat NULL vom gleichem Bereich wie im Blatt „Daten Planung“ Spalte C „Tätigkeiten“ ausgewählt, übernehmen. Hier sollen aber auch die Werte bis -84 Monate davor und 11 Monate danach auch übernommen werden.
Wenn ich aber z.B. das Datum Juni 2009 eingebe, dann sollen die Werte bis Jan 2008, also -17 Monate vom Start wert und 11 Monate danach übernommen werden.

2. Im Blatt "Daten Planung" Spalte C habe ich in jeder Zeile eine Auswahlliste (Combo Box, oder Listenfeld). Wenn ich hier eine Auswahl treffe und Datum in Spalte G „Start ist auch vorhanden , dann sollen die Werte aus dem Blatt „Datenbasis“ vom gleichem Bereich mit dem Prozedur wie oben beschrieben, übernommen werden. Wenn Start Datum näher zum Datum Januar 2008 (Spalte I) ist, dann sollen auch die Werte nur bis dort übernommen werden.

3. Und zuletzt sollen die übernommen Werte nach dem Prozentwert in Spalte E „Daten Planung“ minimiert werden. Wenn dort 50% steht, dann automatisch alle übernommen werte mit 0,5 multiplizieren.

Funktionsübersicht:
Hier die Werte aus dem Blatt "Datenbasis" (z.B. Bereich Derivat) übernehmen.
Suche in "Datenbasis" den Bereich "Derivat, übernehme aus Spalte AZ den Start Wert und setzt es hier in die Zelle mit dem gleichen Datum wie in Spalte G4 ein. Kopiere auch die Werte, hier bis -17 Zellen davor und 11 Zellen danach ein. Diese kann je nach Datum variieren. Kopieren hier maximal bis Jan 08 (Spalte I) Rest der Zellen bleiben hier leer.

Wenn dann in Spalte E ein Prozent Wert (50%) steht, dann alle Werte mit diesem Prozentwert berechnen.
Also mit 0,5 multiplizieren. Hier wurde jetzt keine Berechnung durchgeführt ( nur Bespiel ).
Werte Anpassung automatisch nach Abgleich "Tätigkeit", "%" und "Start" (Datum).

Bei Änderungen "Tätigkeit" oder "%" oder "Start" (Datum) soll es automatisch Bereichsbezogene Werte aus dem Blatt "Datenbasis" übernehmen.

LG Martin

 

Versucherle

Hallo,

ich verstehs zwar immer noch nicht so richtig, aber hier mal ein Versuch. Die Sverweis-Formel von ist schlicht falsch!

Arbeitsblatt mit dem Namen 'Daten Planung'

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

4

Große PA

Jan 2014

0,10

5

Grundprojekt

Apr 2010

0,35

0,60

0,60

0,60

0,60

0,60

0,60

0,60

6

Derivat

Jan 2012

0,10

7

Kleine PA

Mai 2014

0,02

8

Derivat

Jan 2016

0,10

9

Grundprojekt

50%

Jul 2011

0,35

10

Kleine PA

Mrz 2015

0,02

11

Kleine PA

Jun 2010

0,02

0,08

0,08

0,08

0,08

0,08

12

Grundprojekt

Feb 2014

0,35

Name	Bezug
Projekt	=Datenbasis!$B$4:$B$11

Zelle	Formel
H4	=SVERWEIS($C4;Datenbasis!$B$4:$BK$11;51;0)
I4	=WENN(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-9;"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH('Daten Planung'!$C4;Datenbasis!$B$5:$B$11;0);VERGLEICH($G4;$I$2:$DR$2;0)))*WENN($E4="";1;$E4))
H5	=SVERWEIS($C5;Datenbasis!$B$4:$BK$11;51;0)
I5	=WENN(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G5;$I$2:$DR$2;0)<-9;"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH('Daten Planung'!$C5;Datenbasis!$B$5:$B$11;0);VERGLEICH($G5;$I$2:$DR$2;0)))*WENN($E5="";1;$E5))

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
C4	Liste		=Projekt

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Noch was: Ich kann xlsx-Dateien nicht öffnen!

Gruß

Bosko

 

Hallo nochmal,

anbei die Datei in xls (97-2003) format.

Das SVERWEIS in Spalte H4 ist keine Lösung für die gewünschte Datenübernahme.

Ich probiere mal diese formeln die du Angehängt hast.
Herzlichen Dank dafür.

Melde mich erneut.

LG Martin

 

Hallo Martin,

die SVERWEIS Formel in H ist von Dir da eingefügt worden, wird von mir nicht benötigt. So sieht es unter Excel 2003 aus:

Arbeitsblatt mit dem Namen 'Daten Planung'
	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Große PA				Jan 2014	0,10
5	Grundprojekt				Apr 2010	0,35	0,60	0,60	0,60	0,60	0,60	0,60	0,60
6	Derivat				Jan 2012	0,10	0,02	0,02	0,02	0,04	0,04	0,04	0,06
7	Kleine PA				Mai 2014	0,02

Name	Bezug
Projekt	=Datenbasis!$B$4:$B$11

Zelle	Formel
I4	=WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4)))
I5	=WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G5;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G5;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C5;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G5;-1;1)*DATEDIF(MIN($G5;I$2);MAX($G5;I$2);"M"))*WENN($E5="";1;$E5)))
I6	=WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G6;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G6;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C6;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G6;-1;1)*DATEDIF(MIN($G6;I$2);MAX($G6;I$2);"M"))*WENN($E6="";1;$E6)))

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
C4	Liste		=Projekt
C5	Liste		=Projekt
C6	Liste		=Projekt
C7	Liste		=Projekt

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Es gibt noch ein paar Probleme mit fehlenden Daten, die unter Excel 2003 nicht zu lösen sind.

Hier noch eine Version, die die auftretenden Fehler abfängt, da ich aber das nicht testen kann, mußt Du selbst das noch mals prüfen. Bei 2007 gibt es ja die Funktion WENNFEHLER, die macht das Ganze etwas einfacher.

Code:

=WENN(ODER(ISTFEHLER(VERGLEICH($C4;$Datenbasis!$B$5:$B$10;0));$G4[I]11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4))))

Gruß

Edgar

 

Werte übernehmen mit Zeitspiegel Verschiebung per VAB-Forme

Hallo Edgar,

das ist ja der HAMMER, es funktioniert. Ich danke dir vom ganzen Herzen ***DANKESCHÖN***

Die Formel in I4

=WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-48);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);48+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4)))

funktioniert.

Nur noch ein klitze kleines Problem :-)

Wenn Datum in Spalte G "Start" außerhalb der Zeitspiegel (z.B. 07.2007) liegt oder auch ganz leer ist, wird „#NV“ angezeigt.
Möchte das, wenn in Spalte C "Entfällt" oder "Keine Angeben" ausgewählt ist und das Datum in Spalte G "Start" leer oder außerhalb der Zeitspiegel liegt, dann sollen die Zellen im Zeitspiegel auch leer bleiben. Vor allem bei Datum.

Bei Auswahl "Entfällt" kann auch das Wort "Leer" und bei "Keine Angaben" das Kürzel „K.A.“ in den Zellen des Zeitspiegels stehen.
Aber wenn es leer ist wäre ich auch dankbar.

Mit den Formeln WENNFEHLER oder ISTFEHLER bekomme ich Fehlermeldung.

Bedanke mich erneut für deine große Hilfe.

LG Martin

 

Hallo Martin,

ich habe Dir noch eine Formel eingestellt, die eigentlich funktionieren müsste, ich kann sie aber unter Excel 2003 nicht testen, da sie zuviele verschachtelungen hat. Unter OpenOffice hat sie funktioniert. Gerade gesehen, die Forensoftware hat sie verstümmelt, hier noch mal:

Code:

WENN(ODER(ISTFEHLER(VERGLEICH($C4;$Datenbasis!$B$5:$B$10;0));$G4<I$2);"";WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4))))

Gruß

Edgar

 

Werte übernehmen mit Zeitspiegel Verschiebung per VAB-Forme

Halo Edgar,

danke,

Das mit den Zellen leer funktioniert, aber jetzt werden die 11 Zellen nach "Start" Spalte AZ Blatt "Datenbasis" nicht mehr übernommen.
Die Zellen bis -48 davor (links vom "Start") funktionieren.

Mit der Formel vorher ging es.

LG Martin

 

Hallo Martin,

sorry, habe ein $ vergessen:

Code:

WENN(ODER(ISTFEHLER(VERGLEICH($C4;$Datenbasis!$B$5:$B$10;0));$G4<$I$2);"";WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4))))

Gruß

Edgar

 

Hallo Edgar,

nein, das war schon drin. Den musste ich wegmachen, weil dann Fehlermeldung kam und mir $Datenbasis als Fehlerbereich markiert.

LG Martin

 

Hallo Martin,

mein Problem ist, dass ich es nicht mit Excel nachbauen kann. Hier noch ein Versuch:

Code:

=WENN(ODER(ISTFEHLER(VERGLEICH($C4;Datenbasis!$B$5:$B$10;0));$G4<I G4="?");"";WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*Datedif(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4))))

Wenn das nicht funktioniert muß jemand mit Office 2007 ran. Ansonsten kannst Du ja die Formel mit der Formelauswertung überprüfen.

Gruß

Edgar

 

Hallo Edgar,

jetzt bringt es nur #Bezug und übernimm auch keine Werte.

Es ist ja Kurz davor, das es funktioniert. Mit der Formel von heute Morgen funktioniert es ja bis auf die #NV anzeige,
wo mit dem Datum nicht mehr vergleichen kann. Hoffe das da eine Lösung gefunden wird.

Habe diese Datei unter der Version 1997-2003 (xls) angespeichert. Die Formeln eingegeben,
hier passiert das gleiche wie in Excel 2007. Denke das es daran wenige liegen dürfte.

Komisch ist, dass die Datei nach abspeichern auf Version 1997-2003 (xls) plötzlich 3.172 KB geworden ist.
Unter Excel 2007 waren es 228 KB.

Danke dir aber trotzdem für deine große Hilfe.

LG Martin

 

Hallo Martin,

ich habe die Formel etwas umgebastelt. Ich gehe mal davon aus, dass der Sverweis in Spalte H überflüssig ist. Daher habe ich die Spalte anderweitig benutzt und die Formel verkürzt. Dass bei Dir #Bezug kommt, liegt evtl. daran, dass die Formel in der falschen Zelle steht. Da sich die Formeln beim Ziehen mitändern, kann es dann schon mal vorkommen, dass die Bezüge aus der Tabelle rutschen und das mag Excel nicht.

Arbeitsblatt mit dem Namen 'Daten Planung'
	C	D	E	F	G	H	I	J	K	L	M
4	Große PA				Jan 2014	81
5	Grundprojekt				Apr 2010	36	0,60	0,60	0,60	0,60	0,60
6	Derivat				Jan 2012	57	0,02	0,02	0,02	0,04	0,04
7	Kleine PA				Mai 2014	85
8	Derivat				Jan 2016	105

Name	Bezug
Projekt	=Datenbasis!$B$4:$B$11

Zelle	Formel
H4	=DATEDIF(MIN($G4;$I$2);MAX($G4;$I$2);"M")+9
I4	=WENN((ISTZAHL(VERGLEICH($C4;Datenbasis!$B$5:$B$10;0)))*($G4>=I$2)*(ISTZAHL($G4));(WENN(ODER(SPALTE()-$H4>11;SPALTE()-$H4<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*DATEDIF(MIN($G4;I$2);MAX($G4;I2);"M"))*WENN($E4="";1;$E4))));"")
H5	=DATEDIF(MIN($G5;$I$2);MAX($G5;$I$2);"M")+9
I5	=WENN((ISTZAHL(VERGLEICH($C5;Datenbasis!$B$5:$B$10;0)))*($G5>=I$2)*(ISTZAHL($G5));(WENN(ODER(SPALTE()-$H5>11;SPALTE()-$H5<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C5;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G5;-1;1)*DATEDIF(MIN($G5;I$2);MAX($G5;I3);"M"))*WENN($E5="";1;$E5))));"")

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
C4	Liste		=Projekt
C5	Liste		=Projekt
C6	Liste		=Projekt
C7	Liste		=Projekt
C8	Liste		=Projekt

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]


Nachtrag:
Ich sehe gerade, dass in der Formel in meinem letzten Beitrag ein Semikolon und noch mehr fehlt, was vermutlich den Fehler verursacht hat. Reiche ich gleich nach.

Gruß

Edgar

 

Korrektur

Hallo,

Code:

=WENN(ODER(ISTFEHLER(VERGLEICH($C4;Datenbasis!$B$5:$B$10;0));$G4<I$2;$G4="?");"";WENN(ODER(VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)>11;VERGLEICH(I$2;$I$2:$DR$2;0)-VERGLEICH($G4;$I$2:$DR$2;0)<-49);"";(INDEX(Datenbasis!$D$5:$BK$10;VERGLEICH($C4;Datenbasis!$B$5:$B$11;0);49+WENN(I$2<$G4;-1;1)*Datedif(MIN($G4;I$2);MAX($G4;I$2);"M"))*WENN($E4="";1;$E4))))

Gruß

Edgar