Bereich.Verschieben - Zellbezug

Bild

Betrifft: Bereich.Verschieben - Zellbezug
von: Dominic
Geschrieben am: 21.01.2013 13:52:12

Hi zusammen,
ich versuche gerade verzweifelt eine Formel auf mehrere Zellen zu kopieren, der Bezug wird dabei aber jedesmal falsch gesetzt.
Folgende Formel verwende ich in Zelle B38:
=WENN(BEREICH.VERSCHIEBEN(Datensammlungen!$V5;ZEILE()-ZEILE($A38);) =BEREICH.VERSCHIEBEN(Datensammlungen!$V4;ZEILE()-ZEILE($A38););""; BEREICH.VERSCHIEBEN(Datensammlungen!$V5;ZEILE()-ZEILE($A38);))
Hier wird der korrekte gesuchte Wert aus dem Tabellenblatt Datensammlungen V5 genommen. Der Wert in dieser Zelle B38 wird noch korrekt angezeigt! Sagen wir zur Einfachheit "Kunde1".
Wenn ich diese Formel auf meine darunterliegenden Zellen erweitere funktioniert dies nicht. Excel sollte den nächsten Wert aus dem Tabellenblatt Datensammlungen, also V6 nehmen, was dem "Kunden2" entspricht, allerdings gibt er mir "Kunde6", sprich den Wert aus V11 aus.
Folgende Formel in Zelle B44 hätte beim übertragen durch Excel übernommen werden sollen. Denn diese zeigt den Wert "Kunde2" korrekt an:
=WENN(BEREICH.VERSCHIEBEN(Datensammlungen!$V$6;ZEILE()-ZEILE(A44);) =BEREICH.VERSCHIEBEN(Datensammlungen!$V$5;ZEILE()-ZEILE(A44););""; BEREICH.VERSCHIEBEN(Datensammlungen!$V$6;ZEILE()-ZEILE(A44);))
Allerdings ist noch folgendes zu beachten,
Die Tabelle weist folgende Werte/ Informationen auf:
B38 = "Kunde" Ermittlung über die eben genannte Formel und aus dem anderen Tabellenblatt.
B39 = Überschrift Soll
B40 = Überschrift Delta
B41 = Überschrift Zielerreichung
B42 = Überschrift Vorjahreswert
B43 = leere Spalte - mit hinterleger Farbe zur Verbesserung der Darstellung
B44 = "Kunde" Ermittlung über die eben genannte Formel
B45 = Überschrift Soll
B46 = Überschrift Delta
B47 = Überschrift Zielerreichung
B48 = Überschrift Vorjahreswert
B49 = leere Spalte
B50 = "Kunde"
Und immer so weiter, bis ca. Zeile 600!
Das heißt es gibt nur in dem "Kunden" Feld eine Formel, in der abwechselnd der Kunde aus dem Tabellenblatt Datensammlungen eingefügt werden soll. Die anderen Zellen beinhalten nur die Überschriften für meine Tabelle sind also statisch und immer gleich - die Auswertungen erfolgen jeweils rechts daneben von C-O für die Monate Januar-Dezember.
Wie kann ich nun Zeile B38 bis B43 markieren, und dann runter ziehen, so dass automatisch der Bezug korrekt übernommen wird?
Vielen Dank im Voraus!

Bild

Betrifft: AW: Bereich.Verschieben - Zellbezug
von: Dominic
Geschrieben am: 21.01.2013 13:55:22
Ich meinte natürlich bei B43 und B49 jeweils "leere Zeile".

Bild

Betrifft: Upload?
von: Klaus M.vdT.
Geschrieben am: 21.01.2013 14:00:47
Hi Dominic,
magst du das File eben hochladen, damit wir das nicht nachbauen müssen?
Vermutung:
ZEILE()-ZEILE($A38)
das ergibt immer das gleiche! (-29), egal wie lange du dass nach unten kopierst.
WEnn es in B38 funktioniert, dann müsste die Formel so in jeder Zeile funktionieren:
=WENN(BEREICH.VERSCHIEBEN(Datensammlungen!$V5;38-ZEILE($A38);) =BEREICH.VERSCHIEBEN(Datensammlungen!$V4;38-ZEILE($A38););""; BEREICH.VERSCHIEBEN(Datensammlungen!$V5;38-ZEILE($A38);))
Grüße,
Klaus M.vdT.

Bild

Betrifft: AW: Upload?
von: Dominic
Geschrieben am: 21.01.2013 14:12:38
Hi Klaus,
funktioniert leider nicht.
Hier die Datei: https://www.herber.de/bbs/user/83502.xlsx
Ist zwar noch eine ältere Version, hier fehlt z. B. die Zeile "Vorjahreswert" vollständig, aber für diese Formel passt es. Habe meine aktuelle Formel in Zelle B25 eingefügt. Hier wird der Kunde1 angezeigt. Wenn ich nun B25 bis B29 markiere und auf alle darunter liegenden Zellen übertragen möchte, funktioniert es wie erwähnt nicht.
Es geht hier wirklich nur um diese Formeln der Zelle B, die anderen C-O habe ich bereits hier in einem anderen Thread abklären können.

Bild

Betrifft: AW: Upload?
von: Klaus M.vdT.
Geschrieben am: 21.01.2013 15:34:32
Hi,
nochmal:
ROW()-ROW($A25)
macht schlicht nix. Ich habe jetzt verstanden, dass du in B25 einen Offset von 0 auf die Datensammlung brauchst, und in Zeile 31 (=6 Zeilen weiter) einen Offset von 1 auf die Datensammlung.
Das sollte diese Formel können:
=IF(OFFSET(Datensammlungen!$V$3;ROUNDDOWN((ROW()-25)/6;0);) =OFFSET(Datensammlungen!$V$4; ROUNDDOWN((ROW()-25)/6;0););""; OFFSET(Datensammlungen!$V$3;ROUNDDOWN((ROW()-25)/6;0);))
Deutsche Version der Formel ohne Funktionsgarantie :-)
'=wenn(bereich.verschieben(Datensammlungen!$V$3;abrunden((zeile()-25)/6;0);) =bereich.verschieben(Datensammlungen!$V$4;abrunden((zeile()-25)/6;0););""; bereich.verschieben(Datensammlungen!$V$3;abrunden((zeile()-25)/6;0);))
Grüße,
Klaus M.vdT.

Bild

Betrifft: AW: Upload?
von: Dominic
Geschrieben am: 21.01.2013 15:48:43
Super danke dir! Funktioniert. Merkwürdigerweise aber nur die deutsche Variante und nicht deine erste Variante.

Bild

Betrifft: Danke für die Rückmeldung! mT.
von: Klaus M.vdT.
Geschrieben am: 21.01.2013 15:50:25
Hi,
danke für die Rückmeldung!
und nicht deine erste Variante
Die hast du aber schon in ein englischsprachiges Excel reinkopiert, oder? Wenn du eine Englische Formel in ein deutsches Excel kopierts, gibts #NAME!.
Grüße,
Klaus M.vdT.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Bereich.Verschieben - Zellbezug"