Tabellenblattadressierung

Computer: Software & Programmierung Office Software
1

Hallo Excel-Experten,

Software: Excel 2007

Ich habe folgendes Problem:
Meine Arbeitsmappe besteht aus einer Startseite und mehreren Tabellenblätter (2010, 2011, 2012,…)
Wenn ich Daten von 2010/A1 benötige, kann ich mir diese mit folgendem Befehl _ =‚2010‘!A1 _ holen.
Da ich mich auf mehrere Werte in den Tabellenblättern beziehen muss, wäre es für mich sehr hilfreich, wenn ich mit der Namensfunktion arbeiten könnte.
Leider schaffe ich es nicht, mit einem vergebenen Namen [auf der Startseite](z.B. Test) mit dem zugewiesenen Wert (z. B. 2010) die entsprechende Seite zu adressieren. Der Befehl _ = Test!A1 _ ist nicht durchführbar. Der Wert von Test kann auf allen Tabellenblättern abgerufen werden.
Kann mir jemand einen Tipp geben, wie ich eine Adressierung der Tabellenblätter mit Hilfe des Namens oder einer Variablen vornehmen kann.
Für Eure Unterstützung wäre ich sehr dankbar.
Beste Grüße
Renier

2

Hallo Renier.

Meine Arbeitsmappe besteht aus einer Startseite und mehreren
Tabellenblätter (2010, 2011, 2012,…)

Kann mir jemand einen Tipp geben, wie ich eine Adressierung
der Tabellenblätter mit Hilfe des Namens oder einer Variablen
vornehmen kann.

Ich weiß nicht, wie Deine Startseite aussieht, aber wenn ich das alles richtig interpretiere, dann könnte für Dich durchaus eine VBA-Lösung sinnvoll sein. Es könnte dann so aussehen, dass Du auf Deiner Startseite eine kleine Auswahl-Liste hast (sei es in Form einer ListBox oder einer ComboBox), in der Du das gewünschte Tabellenblatt auswählen kannst (so, wie Du z.B. die Schriftart für eine Zelle auswählen kannst), wodurch sich dann der Bezug in einer Formel aktualisieren würde.

Vielleicht ist so etwas ja interessant für Dich.

VG
Carsten

3

Hallo Renier,

Wenn ich Daten von 2010/A1 benötige, kann ich mir diese mit
folgendem Befehl _ =‚2010‘!A1 _ holen.

die Hochkommas brauchste nur wenn im Blattnamen Leerzeichen sind.

Da ich mich auf mehrere Werte in den Tabellenblättern beziehen
muss, wäre es für mich sehr hilfreich, wenn ich mit der
Namensfunktion arbeiten könnte.

Warum? Ob du schreibst =2010!A1 oder Test!A1 ist für mich erstmal gleich.
(Daß das mit Test so nicht klappt erstmal weglassend)
Einen Unterschied sehe ich dazwischen, wenn du das Blatt/mappe kopierst bräuchest du nur dem namen test die 2011 zuweisen und die Formeln gelten für das andere jahr ohne daß du Tabellenformeln anpassen mußt.

Gibt noch andere Gründe für die namensbenutzung aber bei dir bin ich mir unschlüssig warum du da Namensvergabe benutzen willst.
Beschreibe mal genauer was du da willst, may be durch Nutzung der Namensvergabe für andere Objekte ist man effektiver.

Leider schaffe ich es nicht, mit einem vergebenen Namen [auf
der Startseite](z.B. Test) mit dem zugewiesenen Wert (z. B.
2010)

Je nachdem wie du zuweist ist Test dann die Zahl 2010 oder der String 2010.

die entsprechende Seite zu adressieren. Der Befehl =
Test!A1 ist nicht durchführbar.

Dann wandelt Excel Test nicht um bzw. erkennt es nicht als Blattnamen.
Probier mal ungetestet
=Indirekt(Test&"!A1")

Gruß
Reinhard

4

Hallo Reinhard und Carsten,

zuerst vielen Dank für die schnellen Antworten.
Ich werde versuchen, klar zu legen was ich vor habe.
Die Startseite beinhaltet alle nötigen Angaben die ich für einen Bestandsausdruck unseres Vereins benötige. Auf den Tabellenblättern (2010, 2011,…) sind die Bestandslisten erstellt. Jedes Jahr wird ein neues Tabellenblatt (Bestandsliste) hinzu kopiert. Im Laufe des Jahres werden diese monatlich aktualisiert. Wenn ich jetzt einen Ausdruck benötige, würde ich gerne durch die einfache Zuweisung der Jahreszahl meinen entsprechenden Ausdruck erstellen.
Weiterer Vorteile sind:
Bei Erstellung von Vergleichen ist es bei Verwendung von Namen übersichtlicher und effektiver. Auch die Programmpflege ist wesentlich leichter und weitere User sind leichter einzuarbeiten (da ich inzwischen Rentner bin, muss man auch daran denken).
Der Befehl =Indirekt(Test&"!A1") funktioniert auch nicht. Ich hatte die ganzen Umwandlungsfunktionen auch schon ausprobiert.
Leider muss ich gestehen, dass ich mit VBA noch nichts anfangen kann. Wenn es keine andere Lösung gibt, werde ich mich wohl damit beschäftigen müssen.
Ich hoffe, dass meine Erklärung ausreichend ist.
Beste Grüße
Renier

5

Grüezi renier

Meine Arbeitsmappe besteht aus einer Startseite und mehreren
Tabellenblätter (2010, 2011, 2012,…)
Wenn ich Daten von 2010/A1 benötige, kann ich mir diese mit
folgendem Befehl _ =‚2010‘!A1 _ holen.
Da ich mich auf mehrere Werte in den Tabellenblättern beziehen
muss, wäre es für mich sehr hilfreich, wenn ich mit der
Namensfunktion arbeiten könnte.
Leider schaffe ich es nicht, mit einem vergebenen Namen [auf
der Startseite](z.B. Test) mit dem zugewiesenen Wert (z. B.
2010) die entsprechende Seite zu adressieren. Der Befehl =
Test!A1 ist nicht durchführbar. Der Wert von Test kann auf
allen Tabellenblättern abgerufen werden.

Wo oder wie gibst Du denn den Wert deines Namens ‚Test‘ vor, wenn du das Jahr ändern willst?

Generell passt das mit INDIREKT() schon, aber das kannst Du meiner Ansicht nach auch direkt und Ohne Namens-Zuweisung tun.

Lege eine Zelle fest, in der Du die Jahreszahl auf dem ersten Tabellenblatt eingibst - ich nehme hier mal an, dass das A1 ist.
Dann kannst Du die folgende Formel verwenden um von eben diesem Jahres-Tabellenblatt Daten zu verknüpfen:

=INDIREKT("’"&A1&"’!B10")

Damit wird der Wert der Zelle B10 aus dem Tabellenbatt verknüpft, dessen Namen Du in der Zelle A1 angegeben hast (also deine Jahreszahl).

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
6

Zugriff über Namen auf anderes Blatt mit Indirekt
Hallo Renier,

Der Befehl =Indirekt(Test&"!A1") funktioniert auch nicht.
Ich hatte die ganzen Umwandlungsfunktionen auch schon
ausprobiert.

grad getestet, Excel ist es in dem Fall egal ob du den Namen Test
der Formel
=2010
oder
=„2010“
zuweist. In beiden Fällen funktioniert =Indirekt(Test&"!A1")

Die Startseite beinhaltet alle nötigen Angaben die ich für
einen Bestandsausdruck unseres Vereins benötige. Auf den
Tabellenblättern (2010, 2011,…) sind die Bestandslisten
erstellt.

Wenn ich jetzt einen Ausdruck
benötige, würde ich gerne durch die einfache Zuweisung der
Jahreszahl meinen entsprechenden Ausdruck erstellen.

Das geht ja wie gezeigt durch Indirekt().
Du mußt für dich und deine „Anlernlinge“ entscheiden was für die dann einfacher ist. Also ob sie um im Übersichtsblatt von 2010 auf 2011 zu
wechseln, zur Namensdefinition gehen müssen.
Oder wie Thomas es anregte in eine festgelegte Zelle (z.B. H1) halt 2011 reinschreiben.

Die Formel ist dann
=Indirekt(H1&"!A1")
Bei einer Zelle kannst du einen Kommentartext dazu schreiben mit
Erklärungen, dann können Excel-Ungeübte dort nachlesen…

Wenn die Indirekt-Funktion weiterhin bei dir nicht klappt so lade eine Kopie deiner mappe hoch,
Perönliche daten kannst du ja vorher anonymisieren, wie hie gezeigt:
**/t/zwei-listen-vereinen/6734019/5

Gruß
Reinhard**

7

Hallo Renier.

Leider muss ich gestehen, dass ich mit VBA noch nichts
anfangen kann. Wenn es keine andere Lösung gibt, werde ich
mich wohl damit beschäftigen müssen.

Die Lösung mit der Indirekt-Funktion, wie Thomas und Reinhard es schrieben, ist dann wahrscheinlich die sinnvollste. Die VBA-Lösung würde eigentlich nichts anderes machen, nur der Bedien-Komfort wäre etwas höher, weil man nicht die Jahreszahl in eine Zelle eintragen müsste, sondern man könnte sie einfach auswählen.

VG
Carsten

8

Grüezi Carsten

Die Lösung mit der Indirekt-Funktion, wie Thomas und Reinhard
es schrieben, ist dann wahrscheinlich die sinnvollste. Die
VBA-Lösung würde eigentlich nichts anderes machen, nur der
Bedien-Komfort wäre etwas höher, weil man nicht die Jahreszahl
in eine Zelle eintragen müsste, sondern man könnte sie einfach
auswählen.

…wenn die Jahreszahlen um die es geht in einem inem Gültigkeits-DropDown eingebunden sind, funktioniert das auch mit INDIREKT() über eine einfache Auswahl daraus…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
9

Hallo Thomas.

…wenn die Jahreszahlen um die es geht in einem inem
Gültigkeits-DropDown eingebunden sind, funktioniert das auch
mit INDIREKT() über eine einfache Auswahl daraus…

Bei Gültigkeit komme ich an einen Punkt, wo ich absolut passen muss. Damit habe ich mich noch nie beschäftigt. Könnte man denn dort auch alle Tabellenblätter außer der „Startseite“ automatisch „einlesen“?

VG
Carsten

10

…wenn die Jahreszahlen um die es geht in einem inem
Gültigkeits-DropDown eingebunden sind, funktioniert das auch
mit INDIREKT() über eine einfache Auswahl daraus…

Bei Gültigkeit komme ich an einen Punkt, wo ich absolut passen
muss. Damit habe ich mich noch nie beschäftigt. Könnte man
denn dort auch alle Tabellenblätter außer der „Startseite“
automatisch „einlesen“?

Hallo Carsten,

du sprichst da verschiedene Dinge gleichzeitig an.
Wenn du dich damit beschäftigen willst nimm irgendeine Zelle, dann Daten—Gültigkeit—Liste (ab 2007 anderer Weg dahin).

Bei Quelle kannst du dann z.B. eingeben
=H1:H10
oder
a;b;c

Zellbezüge sind nur für das blatt der Gültigkeitsliste erlaubt.
Aber kein Akt, dann vergibst du für AnderesBlatt!H1:H15 den Namen Auswahl und schreibst bei Quelle
=Auswahl.

Soll die Auswahl dynamisch sein, also z.B. nur die obersten gefüllten zellen in H anzeigen, dann in etwa so als Formel der zu den namen Auswahl zuweist:

=Bereich.Verschieben($H$1;;;Anzahl2(H:H);1)

[ich hab jetzt den Blattnamen weggelassen, der muß da noch rein]

Zur vollautomatischen Liste aller Blätter:
Das geht mit dem alten Makro4-Befehlen.

Weise den Namen Blaetter dieser Formel zu:
=Arbeitsmappe.zuordnen(1)

Dann hast du in „Blaetter“ alle Blattnamen drin als Matrix.

Für die Liste aller Blaetter, schreibst du irgendwo, am besten in Zeile 1:
=index(Blatter,zeile())
und koperst das weit nach unten.

Diese Liste aktualisiert sich dann wenn man Blätter hinzufügt.
Bei geschickter Nutzung all dieser Dinge ist dann der Endeffekt, man
hat dann in der Gültigkeitszelle immer die aktuelle Liste aller Blattnamen, egal mit oder ohne Startseite.

Klingt alles kompliziert, geht aber in Wahrheit mit Routine sehr schnell.
Garantiert schneller als diesen Beitrag zu schreieben :smile:

Gruß
Reinhard

11

Grüezi Carsten

…wenn die Jahreszahlen um die es geht in einem inem
Gültigkeits-DropDown eingebunden sind, funktioniert das auch
mit INDIREKT() über eine einfache Auswahl daraus…

Bei Gültigkeit komme ich an einen Punkt, wo ich absolut passen
muss. Damit habe ich mich noch nie beschäftigt.

Ja, das ist aber nicht weiter schlimm, geht mir auch manchmal so.

Könnte man denn dort auch alle Tabellenblätter außer der „Startseite“
automatisch „einlesen“?

Man kann das noch etwas angepasst machen, aber im Prinzip genau so wie Reinhard es beschrieben hat.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
12

Hallo Reinhard, Carsten und Thomas,
nochmals vielen Dank für Eure Antworten.
Die Beispiele haben mir gezeigt, wo ich meinen Fehler gemacht habe.
Ich habe das &-Zeichen nicht gemacht.
Auch der Hinweis mit dem Kommentar war noch einmal sehr wichtig, den vernachlässigt man meistens.
Ich werde jetzt erst einmal mein Programm benutzerfreundlicher und pflegeleichter gestalten.
Sicherlich werden bei mir noch manche Probleme auftreten, und dann werde ich mich sicherlich wieder an das Forum wenden.
Beste Grüße
Renier

13

Hallo Renier,

Sicherlich werden bei mir noch manche Probleme auftreten, und
dann werde ich mich sicherlich wieder an das Forum wenden.

Gerne.
Teste bitte aber jetzt schon wie das geht deine mappe zu anonymisieren
um sie dann ggfs. auch hochladen zu können.
Es könnte ja auch ein andrer kleiner Fehler sein wie da dieses fehlende
&.

Dann geht das doch lustig hin und her in der Beitragsfolge :frowning:
Es kann etliche Gründe haben, beileibe nicht alles etwas was an dir liegt, warum etwas bei mir klappt und bei dir nicht.
Oder umgedreht, ist aber egal.

Dann ist eine beispielmappe sehr hilfreich.

Bis denne.

Gruß
Reinhard

14

Hallo Reinhard und Thomas.

Vielen Dank für die Infos. Das muß ich mir 'mal genauer zu Gemüte führen.

VG
Carsten