Name des Tabellenblattes anzeigen

Question

Hallo,

Eigenversuche und Internet konnten bis jetzt nicht weiterhelfen, deshalb hätte folgende Frage:

Die Situation:

Ich habe Tabellenblätter in dieser Reigenfolge: Zusammenfassung; Anfang; Daten 1 - Daten XX; Ende.

In Zusammenfassung erfrage ich bspw: =max('Anfang:Ende!a1') und bekomme somit den Maximalwert auf A1 in den Grenzen der Daten 1 - Daten XX.

Nun würde ich gern wissen, ist es Daten 1, das den Maxwert hat, oder Daten 25, oder Daten 198?

Dazu wäre es super wenn jemand eine Formel kennt, die dazu führt, das mir der Name des Tabellenblattes, aus dem der Maxwert stammt, angezeigt wird.

Dann könnte ich per Sverweis weitere dazugehörige Daten auslesen.

Bisher bekannt ist mir das allgemeine Anzeigen des Tabellenblattes mit einer Formel:

=TEIL(ZELLE("Dateiname";$A$1);FINDEN("]";ZELLE("Dateiname";$A$1))+1;31)

Aber eine Verknüpfung gelingt mir noch nicht. Vielen dank für eure Hilfe.

Gruß Alex

Answer 1

Hallo Alex,

ich vermute, da wird Dir nur ein Makro helfen können.

Wäre aber schön zu wissen, was Du dann genau mit dem Ergebnis vor hast?

Gruß
Rainer

Answer 2

Hallo Rainer,

Ich habe jetzt nebenbei auch folgende Formel versucht:

=BEREICH.VERSCHIEBEN(MAX(Anfang:Ende!H90));-1;0;1;1)

Allerdings lässt sich der Bezug der Formel so nicht definieren.

=BEREICH.VERSCHIEBEN(MAX(DatenXX!H90));-1;0;1;1) würde funktionieren.

Mein eigentliches Ziel ist es, den Zelleninhalt rechts der maximalen Zelle anzuzeigen.

Max ist dabei beispielsweise eine Zeit, von 1- 5 Stunden, ich bekomme also 5 Stunden, und dann will ich angezeigt bekommen, wieviel Kilometer in den 5 Stunden angefallen sind. Diese Information steht aber nur im Tabellenblatt, wo auch die maximale Zeit steht.

Ich hoffe man kanns verstehen :p

Answer 3

Hallo Alex,

wie ich schon schrieb, ist das Problem nicht mit Formeln zu lösen, wobei ich mich auch gern vom Gegenteil überzeugen liese.

Auch Deine Formelvorschläge können nicht funktionieren, da diese vom Ansatz her schon falsch sind.
BEREICH.VERSCHIEBEN() verlangt als erstes Argument immer eine Zell- oder Bereichsadresse, also einen Range.

In Deinen Vorschlägen verwendest Du aber die Funktion MAX(), welche immer nur einen Zahlenwert zurück gibt.

Egal welche Tabellenfunktion Du verwenden möchtest, Du brauchst immer den relevanten Arbeitsblattnamen, den Du nach meinen Überlegungen nicht ohne VBA ermitteln kannst.

Vielleicht schreibt Dir jemand ein Makro, bei mir dauert das immer etwas lange, weil ich auf dem Gebiet kein Fachmann bin und im Moment wenig Zeit dazu habe.

Gruß
Rainer

Answer 4

Hallo Alex,

ohne mich mit VBA auszukennen würde ich dir den (einmalig) etwas umständlicheren Weg wie folgt vorschlagen:

1. Du erstellst ein Neues Tabellenblatt (Bsp. Tabelle6)
Hier erstellst Du dir eine Hilfsliste aller Blätter:

a. Einfügen -> Namen -> Definieren/Festlegen, Name: x
bezieht sich auf: =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())

b. In Zelle C1 diese Formel eintragen und runterkopieren:

=WENN(ZEILEN($1:1)>VERGLEICH("";x;-1);"";TEIL(INDEX(x;ZEILEN($1:1));FINDEN("]";INDEX(x;ZEILEN($1:1)))+1;31))

c. In Zelle B2 diese Formel eintragen und runterkopieren:

=VERKETTEN("=";C2;"!A1")

(B1 brauchst Du nicht, da in C1 Zusammenfassung stehen müsste)

d. Alle Zellen in Spalte B müssten markiert sein. Mit auf dem Rand gedrückter rechter Maustaste die Werte in die Spalte A kopieren.

e. Mit STRG H = ersetzen durch =

Nun hast Du eine Verbindung aller Zellen A1 von allen Blättern.

2. Nun in Zusammenfassung A2 einen SVERWEIS zur eben erstellten Hilfstabelle
Bei mir:
=SVERWEIS(MAX(Tabelle6!A2:A5);Tabelle6!A2:C5;3;FALSCH)

Sofern die Max immer in A1 eingetragen werden aktualisiert sich die Angabe selbstständig.

Ansonsten kannst Du den Bezug in der Hilfstabelle anpassen.

Ich hoffe dich nicht missverstanden zu haben.

Gruß Maik

Answer 5

Hallo Alex,

da Dir bisher niemand ein Makro geliefert hat, habe ich nun doch mal einen mehr oder weniger unprofessionellen Code geschrieben.
Kopiere diesen in ein allgemeines Modul

Option Explicit

Sub test()
Dim intI As Integer
For intI = 2 To Worksheets.Count
Worksheets(1).Range("Y" & Cells(Rows.Count, 25).End(xlUp).Row + 1) _
= Worksheets(intI).Range("A1").Value
Worksheets(1).Range("Z" & Cells(Rows.Count, 26).End(xlUp).Row + 1) _
= Worksheets(intI).Range("B1").Value
Next
For intI = 2 To Worksheets.Count
If Worksheets(1).Range("Y" & intI).Value _
= WorksheetFunction.Max(Worksheets(1).Range("Y:Y")) Then
Worksheets(1).Range("A1") _
= Worksheets(1).Range("Y" & intI).Offset(0, 1).Value
End If
Next
Worksheets(1).Range("Y:Z").ClearContents
End Sub

Deine Arbeitsblätter müssen so angeordnet sein, wie Du in der Ausgangsfrage beschrieben hast., nur musst Du auf die Blätter "Anfang" und "Ende" verzichten, es reichen die Blätter "Daten 1" bis "Daten x".

Das Makro liefert Dir in Zelle A1 des Arbeitsblattes "Zusammenfassung" den Wert aus B1, des Arbeitsblattes, dessen Wert in A1 das Maximum aus allen Blättern darstellt.

Gruß
Rainer

Answer 6

hi all ^^

etwas in der art ?

gruss nighty

einzufuegen
alt+f11/projektexplrer/allgemeinesmodul

Function Xanzahl() As String
Dim TbAnz As Long
Dim IndexWks As Long
For TbAnz = 1 To Worksheets.Count
If IsNumeric(Worksheets(TbAnz).Cells(1, 1)) = True Then
If Worksheets(TbAnz).Cells(1, 1) > Xanzahl Then
Xanzahl = Worksheets(TbAnz).Cells(1, 1)
IndexWks = Worksheets(TbAnz).Index
End If
End If
Next TbAnz
Xanzahl = Xanzahl & "-" & Worksheets(IndexWks).Name
End Function

die function ist unter function einfuegen benutzerdefiniert erreichbar

Answer 7

Hallo zusammen,

Ich hatte das Problem jetzt so gelöst, das ich mir die Daten separat auf ein neues Tabellenblatt geholt habe, in der Form:

=INDEX(INDIREKT("'Daten "&$M2&"'!$A:$M");1;2) - wobei M2 die Nummer der aktuellen Daten darstellt, also bei Tabellenblatt Daten 1, steht im Hilfsblatt in M2 der Wert 1.

Natürlich ist das kein Selbstläufer, die ich eigentlich immer bevorzuge und muss immer für jedes neue Tabellenblatt um eine Zeile erweitert werden.

Ich werde mich mal in den VBA reinlesen, da ich damit so gut wie noch gar nicht zu tun hatte und nicht einmal genau weiß, wo ich sowas eintragee. Daher danke Nighty, für die Anleitung wo´s hingehört.

Und Rainer, ist es zwingend erforderlich Anfang und Ende wegzulassen, oder optional? Weil ich doch darauf angewiesen bin, für andere Summenbildungen an Selbstläufer.

Ich möchte mich bei den beteiligten für eure Unterstützung bedanken.

Gruß Alex

Answer 8

Ich bins nochmal

Ich habe jetzt mal beide Sachen in den VBA eingeben und probiert. Dasvon Nighty lieferte mir nur A1 in die Zelle A1, aber vllt hab ich auch was falsch gemacht?! Das möchte ich auf keinen Fall ausschließen.

Zu dem von Rainer kann man nur sagen, super Sache, funktionert super, recht herzlichen Dank.

Gruß Alex

Answer 9

Hallo Alex,

mit den Arbeirtsblättern "Anfang" und "Ende" muss das Makro so lauten:

Option Explicit

Sub test()
Dim intI As Integer
For intI = 3 To Worksheets.Count - 1
Worksheets(1).Range("Y" & Cells(Rows.Count, 25).End(xlUp).Row + 1) _
= Worksheets(intI).Range("A1").Value
Worksheets(1).Range("Z" & Cells(Rows.Count, 26).End(xlUp).Row + 1) _
= Worksheets(intI).Range("B1").Value
Next
For intI = 3 To Worksheets.Count - 1
If Worksheets(1).Range("Y" & intI).Value _
= WorksheetFunction.Max(Worksheets(1).Range("Y:Y")) Then
Worksheets(1).Range("A1") _
= Worksheets(1).Range("Y" & intI).Offset(0, 1).Value
End If
Next
Worksheets(1).Range("Y:Z").ClearContents
End Sub

Gruß
Rainer