Office: Zum ersten alphabetischen Wert springen

Hi,

das Ganze geht auch ohne VBA!
A1: =HYPERLINK("#"&ADRESSE(ZEILE(A4)+20;1);"A")
B1: =HYPERLINK("#"&ADRESSE(ZEILE(A7)+20;1);"B")
etc.
Dabei ist A4 die Zelle mit dem ersten "A", A7 die Zelle mit dem ersten "B".
Die +20 ist der Offset dazu. Wenn du diesen Offset in Zelle einträgst, kannst du in der Formel die 20 durch die Zelle ersetzen und so den Offset jederzeit ändern.
Die 1 ist die Nummer der Spalte, in die gesprungen werden soll (1=A, 2=B etc.)

Beim Einfügen und Löschen von Zeilen passt Excel die Formeln automatisch an.

 

Hi,

noch eine Möglichkeit:
B1: =HYPERLINK("#"&ADRESSE(XVERGLEICH(A1&"*";A4:A15;2;1)+20+ZEILE(A4)-1;1);"Gehe zu")
A1 ist der Wert nach dem gesucht wird
A4:A15 ist der Bereich, der durchsucht wird (also deine Namen)
;2;1 gibt an, dass mit Platzhalter und von oben nach unten gesucht wird. Es wird also der erste Eintrag gefunden. Gibst du statt dessen ;2;2 an, so wird von unten nach oben gesucht und entsprechend der letzte Eintrag gefunden (dann kannst du dir den Offset schenken).
+20 ist der Offset
A4 ist nochmals die erste Zelle des zu durchsuchenden Bereichs (muss mit der ersten Zelle von A4:A15 übereinstimmen)
Die letzte 1 ist wieder (wie vorher) die Spalte A.

In A1 gibst du den Anfangswert ein, also "A", "B" etc. oder auch z.B. "ST". Danach klickst du auf B1.
In A1 kann man auch per Datenüberprüfung ein DropDown hinterlegen.

Vorteil dieser Formel: Du brauchst nur 1 Formel und du kannst nach beliebigen Anfängen suchen.
Nachteil: Nach Eingabe des Buchstabens musst du noch auf B1 klicken.


Und dann habe ich noch was:
Verwende doch einfach den Autofilter. Der ist extrem flexibel und erfordert keinerlei Formel oder VBA.

 

Hallo HKindler,

ich hoffe sehr, es geht Dir gut.
Gang, ganz lieben Dank auch für Deine Lösung(en). Ich werde diese in
meine Datei einmal einbasteln und zeitnah Rückmeldung geben.
Aber auf jeden Fall tausend Dank auch an Dich!

Herzliche Grüße
Thomas

 

..ach, und Danke auch für die ausführlichen Erklärungen zu Deiner Lösung.

 

Hallo HKindler,

liege ich mit meiner Vermutung richtig, wenn ich die erste Lösung anwende und ich z.B. bei E ein, zwei
Namen hinzufüge, dann muss ich auch die Formeln wieder anpassen, weil ja dann F sich weiter nach
unten verschiebt, richtig?
Lieben Gruß,
Thomas

 

Hi,
Nein,du musst nur zu Beginn einmalig alle Buchstaben aufführen, so wie du jetzt bereits deine Hyperlinks übers Menu erzeugen musstest. Solltest du bei einem Buchstaben noch nichts haben, dann empfiehlt es sich, den einzelnen Buchstaben zu verwenden, so dass du für alle Buchstaben eine eigene Zelle hast.
Die Formeln passen sich automatisch an, wenn du Zeilen einfügst und Müller&Co. Dadurch von A20 auf A21 rutscht.
Automatisch sortieren ist dann aber nicht drin. Bei der zweiten Methode schon.

 

Hallo HKindler,

Danke nochmals für Deine Rückmeldung. Guter Hinweis noch mit der Sortierung, die man ja doch hin und wieder einmal machen möchte.

Alles Gute, viele Grüße
Thomas

 

Hi,

übrigens kannst du auch folgende Formeln verwenden:
A1: =HYPERLINK("#"&ADRESSE(XVERGLEICH("A*";A4:A15;2;1)+20+ZEILE(A4)-1;1);"A")
B1: =HYPERLINK("#"&ADRESSE(XVERGLEICH("B*";A4:A15;2;1)+20+ZEILE(A4)-1;1);"B")
etc.
Damit hast du dasselbe "Bild" wie in deiner Ursprungsdatei. Aber die Funktionalität meiner zweiten Formel. Sprich du kannst wild drauf los sortieren etc. Wichtig ist nur zu wissen, dass halt immer der erste Eintrag des Buchstabens verwendet wird und dann 20 Zeilen tiefer hingesprungen wird.

 

Hallo Helmut,

ich werde auch Deinen dritten Vorschlag ausprobieren und mich melden. Kann aber sein,
dass ich erst am Wochenende dazu komme.

Lieben Gruß
Thomas

 

Moin allerseits,

Eure erfreulich konstruktive Diskussion hat mich zu einer VBAstelei inspiriert, die ohne jede Formel auskommt. Man nehme seine vorhandene Tabellen für Kunden, Socken oder Hobbies, füge ein paar Leerzeilen ein und ergänze das Workbook um ein bisschen VBA-Code.

Wie's im Detail geht, findet Ihr hier

Schöne Grüße

 

Hallo bastler,

es freut mich, wenn meine "schrägen" Ideen zu solchen VBAsteleien führt und hier auch
darüber dann kreativ diskutiert wird. Tausend Dank auch an Dich für Deine Zeit und Mühen.
Vielleicht kann der eine oder andere auch davon profitieren.

Wünsche Euch alles Liebe und Gute,
Thomas

PS:

Euer Wissen beeindruckt mich wirklich!!!!! Hut ab.

Vielen Dank für Deine Lösungsmöglichkeit. Leider scheint mein PC dafür nicht so sehr geeignet,
da ich gute 2 Minuten vor einem "Ladekringel" sitze

Alles Gute und vielen Dank für Deine Mühe und Zeit,

 

Huch?! bekommst Du irgendeine Fehlermeldung, oder hast Du einfach Listen mit Mio von Einträgen?
Bei sehr großen Tabellen gibt es natürlich Alternativen zu meinen Schleifen.

Lass wissen

lg

 

Hallo bastler,

nein, momentan sind es knapp 500 Einträge.

LG

 

Hallo,

da du am 17.2.2024 angegeben hast, Office365 zu besitzen, müsste auch folgende Lösung bei dir funktionieren.
Ich habe dir in den Namensmanager 2 benutzerdefinierte Funktionen eingefügt
ErzeugeZiel(Letter; Bereich; [Offset]; [ErstLetzt])
HoleZielzelle(Letter; Bereich; [Offset]; [ErstLetzt])
wobei die 1. Funktion die 2.Funktion intern verwendet.

Wie sie zu verwenden sind, ist im Arbeitsblatt Tabelle1 im Bereich B2:G6 zu sehen.
Wie sie konstruiert sind, ist im Bereich A10:A33 zu sehen. Dieser Bereich kann natürlich auch gelöscht werden, da diese Inhalte im Namensmanager enthalten sind.

Die Funktion "ErzeugeZiel" setzt voraus, dass die Tabellen mit den Sprungzielen stets sortiert sind. Kommen also neue Einträge dort hinzu, kann/sollte/müsste sofort wieder sortiert werden. Die Sprungziele, die durch die 3 gelben Zellen vorgegeben werden, updaten sich dann von selbst auf die korrekte Position (siehe B2:B6). Durch Klick auf diese Hyperlink-Zellen gelangt man ans Ziel.

 

Moin Bommel,

Dein Ladekringel hat mich nicht losgelassen und ich habe eine Sub noch mal geringfügig überarbeitet. Es sind nur zwei Zeilen Application... an passender Stelle einzufügen. Vielleicht magst Du testen

Code:

Private Sub ClickOnShape()
Dim i As Integer, r As Integer, sName As String

sName = ""
Application.ScreenUpdating = False
With ActiveSheet
    Cells.EntireRow.Hidden = False
    r = .Cells(Rows.Count, 1).End(xlUp).Row
    sName = .Shapes(Application.Caller).Name
End With

For i = 9 To r
    Cells.EntireRow(i).Hidden = True
Next i
'MsgBox sName, , r

If Right(sName, 1) = "*" Then
    Cells.EntireRow.Hidden = False
    Exit Sub
Else
    For i = 9 To r
        Cells.EntireRow(i).Hidden = True
        If Left(Cells(i, 1), 1) = Right(sName, 1) Then
            Cells.EntireRow(i).Hidden = False
        End If
    Next i
End If
Application.ScreenUpdating = True
End Sub

Schönes Wochenende!