Excel: SVERWEIS mit Hyperlink (mailto)

[aero]

Liebe Community,

Ich habe eine Excel-Datei mit 2 Blättern. In Blatt 1 wird ein Name über die Funktion SVERWEIS aus einer Tabelle in Blatt 2 gezogen. Ich habe in der Tabelle auf Blatt 2 die Namen mit den Email-Adressen verbunden (Strg+K => Email-Anschrift hinterlegt).

Durch die SVERWEIS-Funktion wird nun der Name übernommen, nicht aber die mit diesem Namen verknüpfte Email-Adresse. Das bekomme ich allenfalls nur dann hin, wenn ich in der Tabelle in Blatt 2 statt des Namens direkt die mail-Adresse erfasse.

Mein Ziel: im Blatt 2 sollen die Namen der Mitarbeiter stehen und ein Klick auf diese soll eine Email an sie öffnen. Genau so soll es dann über den SVERWEIS auch auf das Blatt 1 übernommen werden - aktuell wird nur der Name ohne die mailto-"Verknüpfung" übernommen. Ich bin recht ratlos.

ich bin für Hinweise dankbar. =)
aero

 

[tollertyp]

Ich glaube es schadet nicht, für so eine Frage noch eine vereinfachte Beispiel-Excel-Datei hochzuladen, die das Problem kurz und prägnant zeigt. Einfach, um Interpretationsspielraum zu vermeiden und auch den Helfern ein gewisses "Raten" zu ersparen. Sonst muss jeder seinen Lösungsvorschlag posten und du musst es prüfen.

 

[Scientist]

Den Link mit Hyperlink() und den Zusatz "mailto" erstellen.

=HYPERLINK("mailto:vorname-nachname@firma.de";"Vorname Nachname")

 

[aero]

Anbei eine sehr simple-Beispieldatei. in Blatt 2 ist er Blog für SVERWEIS mit den hinterlegtn Emails, welche auf Blatt 1 nicht übernommen werden.

@Scientist
Ich möchte die Formel (welche im realen Kontext bereits durch WENN-Funktionen komplex ist) so straff wie möglich halten und schlicht und ergreifend nur, dass die bereits hinterlegte Email-Adresse mit rübergenommen wird. Deine Formel bringt mich da nicht hin oder ich stehe einfach auf dem Schlauch?

 

[tollertyp]

Er meint wohl, dass du in Tabelle2 eben die Personen/E-Mails in der Form rein schreibst. Funktionieren tut es bei mir aber nicht :-)

Und danke für die Beispiel-Datei.

 

[aero]

Wenn es der Lösungsfindung dient, mache ich das gern. die "richtige Tabelle" besteht aus 999+ Einträgen und da würde ich gern, dass der Name direkt mit der Email übernommen wird. noch eine neue Tabelle nur mit den Email-Adressen, die dann gezogen werden über neue Funktionsteile - das möchte ich vermeiden. Die Lösung wirkt so naheliegend, weil logisch - allein der Weg dortin ist mir nicht klar. ich habe mehrere Stunden danach gegooglet und keine Lösung finden können.

 

[tollertyp]

Wenn es dir wichtig ist, würde ich wohl an deiner Stelle über eine VBA-Lösung nachdenken.

Dort kannst du auf Änderungen reagieren, das kann auch vollkommen transparent für den Nutzer ablaufen, es müssen halt Makros erlaubt sein.

Das Problem ist, dass es wohl nicht möglich ist, über normale Excel-Funktionen an den Hyperlink zu kommen, und SVERWEIS übernimmt ihn auch nicht. Die ganzen Tipps, die man im Internet findet, bauen eben einen neuen Hyperlink mit Hilfe von SVERWEIS zusammen - das ist halt eine andere Problemstellung.

 

[aero]

Mit VBA kenne ich mich noch garnicht aus - das wollte ich daher vorerst gern vermeiden, aber es scheint wohl keine andere Lösung zu geben. Schade, aber dennoch danke.

Falls doch noch jemanden eine Lösung einfällt - immer gern her damit. =)

 

[Scientist]

Ich hatte es so verstanden, dass E-Mail Adresse und Namen getrennt sind.
Dann koennte man entsprechend mit Hyperlink die Verknuepfung herstellen.
Aber wie bereits schon tollertyp schrieb, wird das unter den Voraussetzungen ohne VBA wohl nicht funktionieren.

Ansonsten waere die Frage, ob die E-Mail Adressen nach einem festen (berechenbaren) Schema definiert sind.
Dann koennte man die E-Mail Adresse reproduzieren, wird bei der Masse aber wohl nicht fehlerfrei funktionieren.

 

[tollertyp]

Also nicht schön, aber funktioniert für mich...

Die Formel in Tabelle 1 sieht dann so aus: =HYPERLINK(GETHYPERLINK(A2;Tabelle2!$A$2:$B$7);GETTEXT(A2;Tabelle2!$A$2:$B$7))

Der Code muss als neues Modul rein, und als .XSLM-Datei dann abspeichern. Angeblich kann man den VBA-Editor mit Alt-F11 öffnen, ging bei mir aber nicht. Dann evtl so: https://www.techonthenet.com/excel/macros/visual_basic_editor2016.php
(ich hab manuell ein Makro aufgezeichnet und das dann bearbeitet, das öffnet den Editor auch)

Public Function GetText(searchValue As Integer, rng As range)
    For Each rw In rng.Rows
      If searchValue = rw.Cells(1, 1).value Then
        GetText = rw.Cells(1, 2).value
        Exit Function
      End If
    Next rw
    GetText = ""
End Function

Public Function GetHyperlink(searchValue As Integer, rng As range) 
    For Each rw In rng.Rows
      If searchValue = rw.Cells(1, 1).value Then
        If rw.Cells(1, 2).Hyperlinks.Count > 0 Then
          GetHyperlink = rw.Cells(1, 2).Hyperlinks(1).Address
        Else
          GetHyperlink = ""
        End If
        Exit Function
      End If
    Next rw
    GetHyperlink = ""
End Function

Ich hab die Beispiel-Datei auch mal hochgeladen nach den Änderungen... ist halt mit Makro, da wird Excel zurecht warnen.

Wie du es hinbekommst, dass die Datei beim Öffnen nicht immer für Makros bestätigt werden muss, kann ich dir gerade nicht sagen.

Edit: Das habe ich glaub via Add-In gelöst früher...

Edit2: @Scientist:
Genau wegen solchen Annahmen (gar nicht böse gemeint) habe ich ja um das Beispiel-Excel gebeten.

 

[tollertyp]

Das funktioniert aber nur, wenn die Werte Zahlen sind. Wenn es auch Texte sein können, muss ein wenig angepasst werden.

 

[aero]

Ich bedanke mich recht herzlich. Habe es nun aber so gelöst, dass ich eine eigene Zelle in Blatt 2 definiert habe ("Hier für Email klicken") und diese in die Formel eingebaut, so dass nun Text und Email selbst 2 Zellen darstellen und das geht dann gut. Ich habe die Datei gerade nicht hier, so dass ich die Formel für interessierte nicht posten kann. Mache ich bei Bedarf gern die Tage.

Habt nochmal vielen Dank für eure Hilfe.