relativer zellbezug bei tabellenblattwechsel

Question

Hallo Freunde,
da bin ich wieder ;)
Ich suche Formel um einer Zelle den Wert einer anderen Zelle in einem anderen Tabellenblatt zuzuweisen dessen name sich ändert.

Kann ich über die excel Formel ein Blatt ähnlich wie bei vba mit der Blattnummer ansprechen?
Also sowas wie
='sheets(1)'!A1 anstatt ='Tabelle1'!A1

Hintergrund ist, dass ich ausgewählte Tabellenteile in eine andere Datei kopoiern möchte. Der Tabellenkopf wird jedesmal übernommen. Alle folgenden Arbeitsblätter beziehen sich bei ihrem Tabellenkopf auf das jeweils erste Blatt. Die Arbeitsblätter beziehen sich bei ihrem Tabellenkopf alle auf das erste Blatt.
Wenn nun jemand in der neuen datei z.B. das Datum ändert, sollen sich die anderen Tabellenblätter anpassen. Funktioniert das über die Formelleiste oder mussich die Formel für jede Zelle in jedem Tabellenblatt manuell per vba ändern?

Oder wird der Zellbezug gar automatisch geändert wie wenn ich eine zelle mit einer formel um 3 zellen nach unten kopiere?

Herzlichen Dank schonmal!

Answer 1

Hallo,

soweit mir bekannt ist, kannst du in Formeln die Arbeitsblätter immer nur mit Namen ansprechen.
Aber leider verstehe ich nicht, was du damit meinst:

Wenn nun jemand in der neuen datei z.B. das Datum ändert, sollen sich die anderen Tabellenblätter anpassen.

Was hat das mit dem Bezug auf ein Tabellenblatt zu tun?
Wenn du einen Bezug zu einem Tabellenblatt eingerichtet hast und den Namen des betreffenden Tabellenblatts änderst, dann ändert sich auch der Name in der Formel.

Gruß

M.O.

Answer 2

Hallo M.O.
ich verändere nicht den Namen des Tabellenblattes, sondern ich kopiere jedes Tabellenblatt einzeln in eine neue Datei (bzw. Teile von jedem Tabellenblatt).
Beim kopieren übernehme ich die Formeln.
ab dem 2. Tabellenblatt bezieht sich der tabellenkopf (Datum, Projektnummer, Projektname usw.) auf das erste tabellenblatt, um fehler und schreibarbeit zu sparen.
Es kann jetzt sein, dass ich das datum in der neu erstellten Datei nochmal ändern muss.
Ab dem 2. Tabellenblatt soll sich der Tabellenkopf der neuen Datei wieder auf das erste Blatt der neuen Datei Beziehen.
d.h. entweder ich muss die Formeln alle manuell per vba ändern oder ich finde eine möglichkeit der zelle zu sagen: "beziehe dich auf das erste blatt" anstatt "beziehe dich auf das blatt mit namen "tabelle1""

gruß SirSolaris

Answer 3

Hallo SirSolaris,

ich würde das am einfachsten per Makro lösen: Per Makro die Bezüge im 2. Tabellenblatt einfügen. Bei allen anderen Tabellenblättern die Bezüge per Formel auf das 2. Tabellenblatt setzen.

Gruß

M.O.

Answer 4

hi all ^^

eine benutzerdefinierte funktion
einzufuegen alt+f11 in einem allgemeinen modul

gruss nighty

Function WKSwert(WksIndex As Integer, Rng As String) As Variant
WKSwert = Worksheets(Worksheets(WksIndex).Name).Range(Rng)
End Function

z.b.

=WKSwert(1;"A1")

Answer 5

hi all ^^

alternativ mit .Value ergaenzen

gruss nighty

Answer 6

Hallo,

Danke schonmal soweit.

M.O.
Daran hatte ich auch schon gedacht das ist allerdings recht viel aufwand. Ich habe einen großen Tabellenkopf daher würde ich das gerne vermeiden ;)

Nighty:
Ich kann dir nicht ganz folgen. Ich hatte 1 Semester Informatik im Rahmen meines Ingenieurstudiums und der Prof ist fast noch schlechter als ich ;). dementsprechend sind meine kenntnisse.
Kannst du mir das nochmal langsam mit vielen kommentaren erklären? bzw. Anhand eines Beispiels?

Gruß SirSolaris

Answer 7

hi all ^^

eine function uebergibt einen wert
der hier als variant definiert ist um eine zahl oder text darzustellen
die function fordert 2 zur verfuegung gestellte Parameter
eine zahl fuer den index des worksheets
einen string fuer die addressierung

anhand des worksheet Indexes wird der worksheet Name ermittelt anhand der angegebenen addressierung der wert ermittelt

gruss nighty

Darstellung fester werte(ohne .Value)

Function WKSwert(WksIndex As Integer, Rng As String) As Variant
WKSwert = Worksheets(Worksheets(WksIndex).Name).Range(Rng)
End Function

Darstellung flexibler werte(mit .Value)

Function WKSwert(WksIndex As Integer, Rng As String) As Variant
WKSwert = Worksheets(Worksheets(WksIndex).Name).Range(Rng).Value
End Function

Answer 8

Hallo,

und dann kann ich mit

WKSwert(1;"A1")

in der Formelzeile einer beliebigen Zelle den Wert der Zelle A1 im 1. Tabellenblatt anzeigen?

Gruß SirSolaris

Answer 9

Korrekt!

Allerdings würde ICH den Bezug nicht als Text sondern als normalen Excel-Bezug übergeben. Außerdem wird der zurückgegebene Wert nach Änderung der Basisdaten nicht immer aktualisiert, dazu ist ein weiterer Befehl nötig. Hier eine kleine Anpassung von nightys Code.

Function WKSwert(WksIndex As Integer, Rng As Range) As Variant
Application.Volatile
WKSwert = Worksheets(WksIndex).Range(Rng.Address).Value
End Function

Answer 10

hi all ^^

Application.Volatile

ist nicht notwendig,eher zu vermeiden
da nun jede beliebige function eine neuberechnung auslöst

gruss nighty