Hallo Robert,
da du ja über mehrere Tabellenblätter die Stunden zusammenstellen willst, habe ich dir eine Function gebastelt.
Kopiere den folgenden Code in ein allgemeines Modul deiner Tabelle:
Function Stundenplan(ByVal strKuerzel As String) As String
Dim arrKlassen
Dim k As Integer
Dim t As Integer
'Klassenblätter, die durchlaufen werden sollen
arrKlassen = Array("1a", "1b", "1c", "2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c")
For k = 0 To UBound(arrKlassen)
'Alle Arbeitsblätter in Datei durchlaufen
For t = 1 To ThisWorkbook.Worksheets.Count
'und nur dann suchen, wenn das entsprechende Arbeitsblatt auch vorhanden ist
If ThisWorkbook.Worksheets(t).Name = arrKlassen(k) Then
'hier wird geprüft, ob das gesuchte Kürzel dem Inhalt der Zelle des Tages und der Stunde des aufgerufenen Stundenplan entspricht
'(da die Stundenplanbezeichnung über dem Kürzel steht, muss das Kürzel in einer Zeile tiefer verglichen werden)
If ThisWorkbook.Worksheets(t).Cells(Application.Caller.Row + 1, Application.Caller.Column) = strKuerzel Then
'falls ja, wird die Stundenbezeichnung in Variable geschrieben
Stundenplan = ThisWorkbook.Worksheets(t).Cells(Application.Caller.Row, Application.Caller.Column)
Exit Function
End If
End If
Next t
Next k
'Falls nichts gefunden, bleibt Stundenplan leer
Stundenplan = ""
End Function
Der Aufruf erfolgt einfach, indem du in die Zelle B5 die folgende Formel schreibst:
=Stundenplan($A$3)
Die Formel kannst du dann nach links ziehen und auch nach unten kopieren.
Voraussetzung ist, dass die Tabellen wie in deinem Beispiel immer gleich aufgebaut sind, da das Kürzel ausgehend von der Adresse der Zelle, aus der die Function aufgerufen wird, in den einzelnen Blättern gesucht wird. Wird die Formel z.B. aus der Zelle B5 aufgerufen, wird geprüft, ob in einem der angegebenen Tabellenblätter im Zelle B6 das gesuchte Kürzel steht und falls ja, dann der Wert aus Zelle B5 zurückgegeben.
Gruß
M.O.