Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
25.04.2024 12:55:42
Funktionsparameter auslesen
25.04.2024 11:49:37
Array verwenden
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1752to1756
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1752to1756
1752to1756
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellbezug nach verschieben kaputt

Zellbezug nach verschieben kaputt
13.04.2020 16:43:10
Frank
Hallo liebes Forum,
ich bin gerade dabei mit Excel 2007 einen Belegungsplan für eine Ferienanlage zu erstellen.
Was nicht funktioniert ist wenn ich die Buchung eines Gastes mit Drag&Drop auf ein anderes Datum ziehen möchte, Die Formeln, die in jeder Datumsspalte hinterlegt sind, werfen dann plötzlich eine Bezugsfehlermeldung aus, es scheint als würden sich beim Verschieben die neuen Bezugsdaten nicht automatisch akualisieren, da beim ursprünglichen Datum noch die alten Werten drin stehen, die dort eigentlich nichts mehr zu suchen haben.
Zur Verdeutlichung seht bitte bei meinem Upload nach. "Frank" war ursprünglich vom 20. Apr. für 4 Nächte gebucht. Jetzt habe ich ihn auf 15. April für 4 Nächte gezogen und die Fehler sind dann unter dem Belegungsplan z. B. in der Berechnung der prozentualen Belegung.
https://www.herber.de/bbs/user/136677.xlsx
Vielen Dank im Voraus für Eure Hilfe
Grüsse
Frank

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:21:44
onur
1) "Jetzt habe ich ihn auf 15. April für 4 Nächte gezogen" - WIE denn genau?
2) Wenn du sowieso ALLE Buchstaben in diese "Super-Formel" nimmst, warum nicht einfach: 
=WENN(C6"");1;"")

AW: Zellbezug nach verschieben kaputt
13.04.2020 17:29:46
Frank
Hallo,
danke für den Tip, die "Superformel" zu reduzieren. Gezogen wird die "Buchung", in dem ich alles was orange ist markiere und dann auf das neue gewünschte Datum ziehe.
Übrigens die Formel geht auch kaputt bei "Auschneiden" und "Einfügen". Seltsamersweise klappt es aber mit "Kopieren" und "Einfügen". Letzteres will ich aber vermeiden, weil zu große Fehlerquelle bei mehreren Benutzern.
Grüsse
Frank
Anzeige
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:31:54
onur
NOCHMAL: "Jetzt habe ich ihn auf 15. April für 4 Nächte gezogen" - WIE denn genau?
Bitte Schritt für Schritt.
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:35:42
Frank
pardon, sehe gerade, dass ich auf den 16. April, nicht den 15. April gezogen haben.
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:40:29
Frank
Es wird ja nicht die Formel unterhalb des Planes augeschnitten, sondern die Eingaben im Belegungsplan - und dort ist doch keine Formel drin, sondern nur die Daten. - Verstehe ich das was falsch?
Aber wie gesagt, markieren und dann die gesamte Markierung auf das neue gewünschte Datum ziehen -
Grüsse
Frank
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:46:48
onur
Schau dir doch mal die Formeln von V43 bis Y43 an: Die zeigen auf R43 bis U43.
Die dürften doch normalmerweise nix anzeigen aber sie zeigen eine 1 an - also die früheren Werte vor dem Ändern.
Keine Ahnung, was du da genau für einen Mist baust beim Kopieren und Einfügen.
Du must die Zellen oben markieren und Kopieren und dann mit der rechten Maustaste woanders einfügen (z.B. mit"Werte einfügen")
Anzeige
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:34:41
onur
Du musst ja auch KOPIEREN UND EINFÜGEN und nicht AUSSCHNEIDEN UND EINFÜGEN !
So machst du doch alle Formeln kaputt!
AW: nutze eine Formel mit INDEX() ...
13.04.2020 17:45:02
neopa
Hallo Frank,
... und mit CODE(). Dann bei Beibehaltung Deiner Datenstruktur in C41:
=WENNFEHLER((CODE(INDEX($A:$ZZ;3+ZEILE(C1)*3;SPALTE()))&gt96)*(CODE(INDEX($A:$ZZ;3+ZEILE(C1)*3;SPALTE()))&lt123);"")
und diese nach rechts und unten ziehend kopieren. Nun kannst Du Deine Kennung sowohl innerhalb der Zeile als auch in eine gleichartige andere beliebig verschieben.
Gruß Werner
.. , - ...
AW: nutze eine Formel mit INDEX() ...
13.04.2020 17:56:54
Frank
Hallo Werner,
auf die Schnelle verstehe ich zwar nur "Bahnhof" bei Deiner Formel - aber es funktioniert!! Super vielen Dank!
Jetzt muss ich nur noch in Ruhe schauen, wie geht wenn eine Buchung auch noch zusätzlich in ein anderes Zimmer verschoben werden soll - ich denke das sollte auch dann klappen.
Grüsse
Frank
Anzeige
AW: nutze eine Formel mit INDEX() ...
13.04.2020 18:58:44
Frank
Hallo Werner,
ich habe Deine Formel jetzt auf den ganzen Monat und in alle Zimmer kopiert und dann die "Buchung Frank" wild durch den Belegungsplan gezogen - auch in verschiedene Zimmer.
In einem bestimmten Bereich werden allerdings keine korrekte Anzeigen gemacht und zwar wenn ich ein Anreisedatum im Bereich 2.April bis einschl. 11. April nehme und bei den Zimmern ab "18-SA-ba-de" und darunterliegend (das sind die Positonen 7-12 in der darunterliegenden "1"-Tabelle).
Hast Du dazu noch eine Erklärung?
Danke
Frank
AW: kann ich so nicht bestätigen owT
13.04.2020 19:10:05
neopa
Gruß Werner
.. , - ...
AW: kann ich so nicht bestätigen owT
13.04.2020 19:12:25
Frank
merke gerade, dass es auch davon abhängt, wie ich die zweite "Buchung Klaus" über den Plan schiebe, dass sich dann die Ergebnisse bei "Frank" verändern. Wenn ich z.B. die "Buchung Klaus" komplette lösche und dann nur noch "Frank" übrig habe, dann passier das wie zuvor geschrieben?!
Anzeige
AW: so für mich nicht nicht nachvollziehbar ...
14.04.2020 07:43:29
neopa
Hallo Frank,
... stell einmal Deine entsprechend neue Beispieldatei hier ein. Vielleicht wird es dann nachvollziehbar.
Gruß Werner
.. , - ...
AW: so für mich nicht nicht nachvollziehbar ...
15.04.2020 02:03:42
Frank
Hallo Werner,
hier die Datei, wo Du sehen kannst, dass bei Verschiebung auch der "Klaus-Buchung", manchmal falsche Daten bei den "1" unterhalb des Belegungsplanes angezeigt werden:
https://www.herber.de/bbs/user/136729.xlsx
Wie muss ich Deine Formel verändern, damit weiter unten in der Tabelle bei den Umsätzen auch dort die richtigen Werte aus dem Belegungsplan übernommen werden?
Danke!
Grüsse Frank
Anzeige
AW: offensichtlich ist, ...
15.04.2020 09:43:32
neopa
Hallo Frank,
... wenn Du de Formel aus C41 nochmal richtig nach rechts und unten ziehend kopierst, Du auch ein korrektes Ergebnis erhältst.
Gruß Werner
.. , - ...
AW: offensichtlich ist, ...
15.04.2020 16:29:34
Frank
Hallo Werner,
wunderbar! Jetzt sind alle Bezugsfehler ausgemerzt.
Könntest Du bitte Dir noch den 2. Teil der Frage anschauen?
Dank von Frank!
AW: welcher Frageteil ist noch offen? owT
15.04.2020 16:42:34
neopa
Gruß Werner
.. , - ...
AW: welcher Frageteil ist noch offen? owT
15.04.2020 18:59:20
Frank
dieser hier:
"Wie muss ich Deine Formel verändern, damit weiter unten in der Tabelle bei den Umsätzen auch dort die richtigen Werte aus dem Belegungsplan übernommen werden?"
Ursprünglich dachte ich könnte das selbst lösen, aber kapier leider Deine Formel nicht vollständig :-(
Grüsse
Frank
Anzeige
AW: dafür dann ...
16.04.2020 09:23:26
neopa
Hallo Frank,
... bei Beibehaltung Deiner Datenstruktur (gleiche Zeilenanzahl je Block und Beginn bei Zeile 56)
in C56:
=WENNFEHLER((INDEX($A:$ZZ;REST(ZEILE()-4;13)*3+6;SPALTE())=VERWEIS(9;1/($A$56:$A56"");$A$56:$A56))*INDEX($A:$ZZ;REST(ZEILE()-4;13)*3+5;SPALTE());"")
und Formel nach unten und rechts ziehend kopieren.
Gruß Werner
.. , - ...
AW: dafür dann ...
17.04.2020 02:01:45
Frank
Hallo Werner,
danke, das klappt nun auch.
Leider muss ich noch etwas zu den Umsätzen nachschieben:
Wenn ich z.B. im Belegungsplan zwischen dem "18-Zimmernblock" und dem "20-Zimmerblock" eine Leerzeile einfüge (als optischen Trennungsbalken - also eine neue Zeile 27), kann ich die obige Formel nach dem Zeilensprung nun nicht mehr nur einfach für die folgenden Zimmer reinkopieren.
Wie ändere ich die Formel dann ab, dass für das Zimmer "20-ES-al-de" die Formel nicht ins Leere läuft? Ich habe es schon mit "1+Zeile" und ähnlichem probiert, funktioniert aber nicht.
Danke
Frank
PS.: ich hoffe, dass war dann die letzte Frage hierzu ;-)
Anzeige
AW: genau deswegen hatte ich geschrieben ...
17.04.2020 17:37:39
neopa
Hallo Frank,
... "bei Beibehaltung Deiner Datenstruktur (gleiche Zeilenanzahl je Block und Beginn bei Zeile 56)".
Wenn das jetzt anders geplant ist, muss entweder sicher sein, dass das dann so bleibt (wann ist das schon der Fall) oder müsste einer variabler Auswertung vornehmen. Eine solche könnte dann allerdings etwas aufwendiger werden. Wie wird es ?
Gruß Werner
.. , - ...
AW: genau deswegen hatte ich geschrieben ...
17.04.2020 22:39:00
Frank
Hallo Werner,
das Problem ist, dass ich immer denke, ich kann die Formel dann selbst noch anpassen und habe deshalb auch nicht die komplette Datei hier veröffentlicht. Aber es ist wohl doch besser dies zu machen - siehe bitte hier nochmals den neuen Link-
https://www.herber.de/bbs/user/136827.xlsx
Die Größe des Bereichs, wo die Formeln hinkommen sollen, ist beispielhaft bei Buchstabe "P" zu sehen: mit Zeilen für insgesammt 25 Zimmer sowie einer Summenzeile darunter.(mehr Zimmer werden nicht dazukommen!)
Für alle anderen Buchstaben unter des Belegungsplanes werde ich dann den Bereich jeweils auch auf 25 ausweiten.
Das einzige was noch passieren kann ist, dass die Zimmer in den beiden neuen Abschnitten "VS" und "PC" (ab Zeile 41 im Belegungsplan) irgendwann mal wieder wegfallen können und dass ich später irgendwann alles auf ein komplettes Jahr mit 356 Tagen (2021 etc.) kopieren muss. Ich denke für diese beide Änderungen sollte es aber keine Komplikationen geben.
Grüsse
Frank
Anzeige
AW: hierzu nun ...
18.04.2020 10:41:10
neopa
Hallo Frank,
... wenn Du Deine neuen Zwischenzeilen auch in "3er Blöcken" einfügen würdest (die ungenutzten kannst Du j ausblenden) kann das von aufgezeigte Formelprinzip entsprechend angepasst auch wieder angewendet werden.
Unabhängig davon erschließt sich mir nicht, wozu Du überhaupt diese Datenwiederholung vornimmst. Wenn es Dir nur um die getrennten Summen für die "Kategorien" p; b; i etc. je Tag geht würdest Du dies nicht benötigen, die kann man auch ohne diese ganzen Zusatzzeilen ermitteln. Ist das in Wirklichkeit Deine angestrebte Zielstellung? Oder?
Gruß Werner
.. , - ...
Anzeige
AW: hierzu nun ...
18.04.2020 12:35:49
Frank
Hallo Werner,
die Buchstaben stehen für verschiedene Buchungskanäle, über die die Gäste gebucht haben. Hierfür möchte ich zunächst wissen wieviel Umsatz pro Tag über jeden einzelnen Kanal erwirtschaftet wird. Zusätzlich möchte ich diese Daten später noch für eine Zimmerkontingentauslastung verwenden. Das heißt ich muss wissen welches Zimmer über welchen Kanal gebucht wurde. Ich denke das geht doch dann nur mit dieser detaillierten Aufschlüsselung im Umsatz-Bereich.
Wenn es die Programmierung einfacher macht, die Zimmer im Belegungsplan ein 3er-Blocks zu gruppieren, dann nur zu.(für die Belegungsprozent-Berechnung sollte dies ja keinen Einfluss haben). Sehen möchte ich halt nur die momentanen grauen Trennzeilen, die einfach drei getrennte Häuser kennzeichnen (VM, VS und PC).
Grüsse
Frank
AW: hierzu nun ...
20.04.2020 01:56:02
Frank
Hallo Werner,
kannst Du mir bitte noch schreiben wie die Formel für Deine vorgeschlagenen "3er Blöcke" umzuschreiben ist?
Vielen Dank
Frank
AW: dieser thread liegt schon im Forumsarchiv ...
20.04.2020 17:07:55
neopa
Hallo Frank,
... damit kann ich diesen normalerweise nicht mehr in der Forumsliste sehen. Durch Zufall bin ich eben in der Beitragsliste des Forum auf Deinen Beitrag hierzu von heute Nacht gestoßen.
Nun hab ich festgestellt, dass Du in Deiner zuletzt eingestellten Datei den Zeilenbereich 86 bis 110 ausgeblendet hast. Wenn das noch gebraucht werden sollte, dann reicht dieser Zeilenbereich aber mE nicht mehr aus. Oder? Wenn dies nicht mehr gebraucht, dann lösche die Zeilen.
Unklar ist mir immer noch, warum Du die Bezeichnungen aus A4:A82 nicht in Spalte A unterhalb von "Umsatz" übernommen hast. Deine Kategoriebezeichnungen kannst Du ja in Spalte B übernehmen.
Das macht die Auswertungsformel nicht nur etwas einfacher sondern verbessert auch die Transparenz und erleichtert Dir dadurch evtl. spätere Anpassungen.
Vorschlag deshalb: Eröffne einen neuen thread in dem Du in den Betreff schreibst: "@neopa C, Fortsetzungsthread ..." (weil der alte thread für mich normal nicht sichtbar ist und ich so den neuen schneller erkenne) und im Text schreibst Du einen Verweis auf diesen thread hier und zwar mit folgenden Link: https://www.herber.de/forum/archiv/1752to1756/t1752273.htm
Stelle in den neuen thread Deine nun evtl. nochmals geänderte Datei ein oder schreibe das diese genau so bleiben soll, wie die Datei hier, also die 136827.xlsx.
Bis demnächst dann.
Gruß Werner
.. , - ...
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:54:08
Frank
Hallo Klaus,
zunächt mal Entschuldigung,dass ich Dich schon in meiner Ferienanlage eingebucht hatte ;-).
Ich glaube aber, da gibt es doch noch eine Lösung -siehe bei "neopa C".
Grüsse
Frank
AW: Zellbezug nach verschieben kaputt
13.04.2020 17:40:43
KlausFz
Hallo Frank,
ne, so einfach geht das leider nicht mit dem Verschieben.
Wie Du schon festgestellt hast, werden die Auswertezellbezüge in den alten Tagesspalten an den neuen Eintragungsort angepasst - damit hast Du dann unten die 1-en, obwohl oben in dem Belegungsplan in den Tagen nix mehr drinsteht.
Und zu allem Überfluss werden die Auswertezellbezüge der neuen Eintragungsspalten überschrieben und bringen die bekannte Fehlermeldung.
Finger weg vom Verschieben der Eintragungen in Zellbereichen, die per Formeln ausgewertet werden!
Hier hilft nur Kopieren/Einfügen/Löschen! oder eine Auswertung über VBA.
(Als absoluter Notbehelf ginge nach jedem Verschieben die Auswerteformeln zu reparieren, indem Du die Formeln C41:c123 rüberkopierst bis Spalte AF).
Gruß!
Klaus

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige