Bezug der Zellen (einer zeile) zum Wert in erster Zelle

[Ande86]

Hallo,
ich habe lange im Internet gesucht und bin verzweifelt, da ich keine Lösung gefunden habe. Nun dachte ich mir, dass ich mich hier anmelden könnte um Hilfe von Experten zu bekommen.
Ich arbeite nur hin und wieder mit Excel, also bitte nicht steinigen, falls die Lösung ganz einfach ist :)
Es geht um folgendes Problem:
Ich habe eine Tabelle erstellt, um den Lehrereinsatz zu planen und vereinfachen. Auf dem ersten Blatt (Übersicht) stehen in der ersten Spalte, alle Namen. In der zweiten die Anzahl der Stunden (gesamt), dann die Klassen (GOST11, GOST12, 10a, 10b usw.)
Die weiteren Blätter sind die einzelnen Klassen, wo in der ersten Zeile wieder die Namen stehen und danach die Fächer. Wenn ich also Lehrer A in Klasse GOST11 2 Stunden in Englisch eintrage und in GOST12 ebenfalls 2 Stunden Englisch, erscheint auf dem ersten Blatt (Übersicht) bei den Stunden 2 in GOST11 und 2 in GOST12 und in Anzahl der Stunden (gesant) dann 4 (errechnet aus den Stunden auf dem ersten Blatt). Das hat alles super im ersten Jahr geklappt. Im nächsten kamen neue Kolleg:innen dazu und andere gingen. Somit war die Reihenfolge nicht mehr nach dem ABC sortiert.
Nun zu meinem Problem. Das erste Blatt ist als Tabelle formatiert und wenn ich es nach dem ABC sortieren lasse, nimmt es auch die Zeilen passend zum Namen mit und die Zahlen stimmen. Nun muss ich aber, in jeder Klasse (und es sind sehr viele) den Namen neu eintragen, als Tabelle formatieren und nach dem ABC sortieren lassen. Ich habe nun einen Bezug der ersten Spalte (in den Klassen) zur Übersicht gemacht, so dass ich den Namen nur einmal (in der Übersicht) eingebe und es nciht in jeder Klasse von Hand machen muss. Allerdings wandern die Zeilen mit den Angaben der Stunden natürlich nicht mit. Gibt es eine Möglichkeit einen Bezug jeder Zeile zum Namen in der ersten Spalte in allen Klassen zu schaffen, so dass, wenn ich in der Übersicht nach dem ABC sortiere, er in allen anderen Blättern die Namen sortiert und die dazugehörige Zeile mit den Stunden mitnimmt?

Vielen Dank
LG Andreas

[{Boris}]

Hi,

eine Kombination aus (dynamischen) Formelergebnissen und manuellen Eingaben ist mit Bordmitteln nicht möglich.
Tatsächlich klingt Dein bisheriger Ansatz aber zu kompliziert.
Vielleicht magst Du mal eine Beispieldatei hochladen - dann kann man besser sehen, was wie ist, wie sein soll und wie konkret umgesetzt werden könnte.

[Ande86]

Hallo Boris,

danke für Deine schnelle Antwort. Anbei hab ich die Datei
  kleine Version Test.xlsx (Größe: 24,23 KB / Downloads: 5) deutlich reduziert damit man schnell sieht was ich meine. Sollte es eine bessere Lösung (wovon ich stark ausgehe) als die, die ich mir autodidaktisch zusammengebastelt habe, bin ich immer offen für Verbesserungen :)
In meiner Datei, ist jetzt Lehrer A auf dem ersten Blatt (Übersicht) (der neu dazu gekommen ist) in der Mitte der Tabelle und Lehrer G ganz oben (kam für einen Lehrer der vorher ganz oben in der Tabelle war weil mit A anfing und ging in Rente). Wenn ich nun auswähle, nach ABC sortieren, wird in der Übersicht die Reihenfolge richtig dargestellt und die dazugehörigen Zeilen werden mitgenommen (Stundenanzahl). In den weiteren Blättern, bleibt die Reihenfolge jedoch gleich. Somit müsste ich von Hand in jeder Klasse erstmal auf "Sortieren nach ABC" in dem Drop-down Menü klicken, was bei sehr vielen Klassen eine zeitlang dauern würde. Gäbe es eine Möglichkeit das so zu gestalten, dass ich nur im ersten Blatt (Übersicht) nach ABC sortiere, er das für alle Blätter übernimmt und in allen Blättern die jeweilige Zeile zum Namen in der ersten Spalte in Bezug setzt und diese dann mitzieht?

[HKindler]

Hi,

B6: =SUMME(INDEX(Tabelle3[[DE]:[SeK]];VERGLEICH([@Name];Tabelle3[Name];0);0))

Allerdings solltest du in den einzelnen Tabellen die Lehrernamen nicht mit einer Formel holen und dann sortieren wollen. Das wird nicht gut gehen. Kopiere die Namen einmalig von der Übersichtstabelle in die Klassen und füge sie dort als Werte ein. Wenn neue Lehrer hinzukommen, musst du diese jeweils ergänzen.

Insgesamt ließe sich das sicherlich sinnvoller aufbauen. Ich denke man könnte eine Tabelle machen, in der stehen dann die Spalten "Klasse", "Kurs", Lehrer und "Stundenzahl". Daraus könnte man dann per Pivot oder per Formel deine einzelnen Blätter generieren.

z.B. Pivot für die Übersicht: Lehrer in Zeilen, Klasse in Spalten und Stundenzahl in Werte
oder Pivot für die Klassen: Lehrer in Zeilen, Kurse in Spalten, Stundenzahl in Werte und Klasse in Filter
Für die gewünschte Klasse dann einfach den Filter wechseln.

[Ande86]

Hallo Helmut,

vielen Dank für Deine Hilfe!
Werde gleich morgen Deine Formel ausprobieren.
Ich dachte mir schon, dass es bestimmt eine viel elegantere Variante gibt. Von Pivot hab ich mal gehört, damit aber noch nie gearbeitet (wie gesagt, bin bei Excel ein völliger Anfänger). Werde mich diesbezüglich mal belesen und schauen, wie ich das ganze mit Pivot vereinfachen kann.

Viele Grüße
Andreas

[HKindler]

Hi,

Pivot ist einfacher als es den Anschein hat. Spiel einfach mal ein wenig damit herum. Wenn du meine Anweisungen wörtlich befolgst (auch wenn diese sich für dich sicherlich "komisch" bzw. ungewohnt anhören), solltest du schnell zum Ziel kommen. Das "in Zeilen, Spalten, Werte, Filter ziehen" erschließt sich von selbst, sobald man mal den Mut hat eine Pivot-Tabelle zu erzeugen.