Office: Dynamisches Formel vertikal ziehen mit Spalten-Bezug

Hallo liebes Forum,

Ich habe folgendes Problem, welches ich gerne loesen wuerde, da ich es bisher manuell gemacht habe...

Ich habe in Sheet 2 immer x Trainings pro Tag. Diese Anzahl kann sich jedoch jederzeit aendern. Die Variable ist unter "number of trainings " zu finden.
In Sheet 3 will ich eine Auflistung der Trainings haben. Sheet 3 Spalte C und D sind derzeit noch manuell eingetragen und angepasst... Was ich also brauche:

Eine Formel, die das Datum in Spalte C genau so oft ausgibt untereinander wie die Anzahl der Trainings (Variable).
Eine Formel in Spalte D, die die Werte aus Sheet 2 ausgibt und zwar so viele Zeilen, wie Anzahl der Trainings. Nach dieser Anzahl, soll sie in die naechste Spalte springen und wieder von oben nach unten ausgeben.

Waere super dankbar wenn es dazu eine Moeglichkeit gibt... Ich habe Stunden verbracht das manuell anzupassen *frown.gif*.


Liebe Gruesse,

Melissa

:)

 

Hallo Melissa,

hier mal meine Lösungsvariante. Den Vorgang, den Du realisieren willst, nennt sich Entpivotieren und ist eigentlich eine Aufgabe für PowerQuery; siehe: h i e r
Natürlich kann man es auch mit Excel-Formeln machen, dazu habe ich im Namensmanager 2 Namen definiert: 'Sp_Nr' und 'Z_Nr'.
Diese berechnen den jewiligen Spalten- und Zeilenindex, bezogen auf die jeweilige Zelle, in der die Formel steht. Dazu benutze ich die allseits ungeliebte Bereich.Verschieben()-Funktion.

Gruß von Luschi
aus klein-Paris

PS: Nachteil solcher Excel-Formel-Lösungen: die Formeln für die beiden Indizes müssen nachjustiert werden, sobald die Auswertung auf Sheet3 nicht in Zeile 2 beginnt.

 

Hallo Luschi,

Vielen lieben Dank! Das funktioniert ja wunderbar *Smilie... Du hast mir einige Stunden erspart!!!
Ich frage mich grad nur, wie ich dir Parameter und Bezuege veraendern kann um es der richtigen Datei anzupassen?

=IF(MOD(ROW(Sheet3!I23),Sheet2!$B$10)=0,Sheet2!$B$10,MOD(ROW(Sheet3!I23),Sheet2! $B$10))

Warum steht hier Sheet3!I23?
Ich gehe davon aus, dass Sheet2!$b$10 der Link zu der dynamischen Zahl ist, richtig?




Liebe Gruesse,

Melissa

 

Hallo Melissa,

Deine Vermutung ist richtig, es muß 'Sheet2!$B$10' heißen. Die Anpassung an Deine Originaldatei kann man nur machen, wenn die Demodatei dem Aufbau der Originaldatei entspricht, dabei sind die Daten selbst natürlich 'Schall & Rauch'.
Ich überlege gerade, ob man die 2 definierten Namen auch noch dynamisch machen kann.

 

Hallo Melissa,

Deine Vermutung ist richtig, es muß 'Sheet2!$B$10'oder 'Sheet3!$B$11' heißen. Die Anpassung an Deine Originaldatei kann man nur machen, wenn die Demodatei dem Aufbau der Originaldatei entspricht, dabei sind die Daten selbst natürlich 'Schall & Rauch'.
Ich überlege gerade, ob man die 2 definierten Namen auch noch dynamisch machen kann.

Gruß von Luschi
aus klein-Paris

 

Hallo Luschi,

Das ist sehr suess von dir hab vielen lieben Dank fuer deine ganzen Muehen, auch schon bei vorherigen Themen *Smilie!

Liebe Gruesse,

Melissa

 

Hallo Luschi!

Ich hab das mal probiert in meine Datei zu uebertragen. Leider ohne Erfolg und ich hab echt ueberhaupt keine Ahnung warum... Verstehe leider die Formel nicht so recht...*rolleyes.gif*

Wurdest du mir helfen zu verstehen wie ich deine Formel mit den Namensmanager uebertrage?
Im Sheet "Global Variables" findest du nun die Zelle, in der man die Anzahl der Trainings eintragen kann.

Vielen lieben Dank im Voraus! (nebenbei, du kannst mal reinschauen wie ich das damals gemacht habe... mit viel manueller Arbeit im Sheet "LookUp". Und ich musste es bei jeder Aenderung von der Variablen wieder aendern)

Liebe Gruesse,

Melissa

 

Einen Zusatz haette ich noch, der muesste aber warscheinlich mit gleicher Logik funktionieren. In "LookUp" soll in Spalte E noch BY24 erscheinen und sich auch so verhalten wie die Auflistung der Trainings

 

Hallo Mackie Messer, hör mal bitte mit dem Fullquoten auf. Danke.

 

Hallo Melissa,

kannst Du die Übungsdatei noch einmal senden, aber vorher die Verknüpfungen in der Tabelle Sheet1!BY1:ET1 in feste Werte umwandeln; ich sehe nur ##### statt Werte, obwohl die Frage nach Aktualisierung verneint wurde.

Die Info 'Sheet1!BY24' habe ich so nicht verstanden - was soll damit passieren und wo sollen die Daten dann stehen?

fragt sich Luschi
aus klein-Paris

 

Hi Luschi,

Einen Link hatte ich vergessen rauszunehmen. Sorry!

 

Hallo Melissa,

hier mal die Anpassung an die neuen Gegebenheiten. Infos zu den von mir definierten Namen siehe Tabellenblatt 'Infos'.

Gruß von Luschi
aus klein-Paris

 

Hallo Luschi,

Vielen lieben Dank! Funktioniert wunderbar und dank deinem Aufwand im Info-Sheet verstehe ich den Vorgang auch! Fuehl dich gedrueckt *Smilie!

Liebe Gruesse,

Melissa