Supportnet / Forum / Tabellenkalkulation

Hallo! Folgende Frage an die „Excelenten“: In einer Mappe finden sich zwei Blätter in denen mittels Hyperlinks von bestimmten Zellen des einen auf bestimmte Zellen des anderen und zurück verwiesen werden soll. (s. unten) Das Erstellen der Zellbezüge manuell ist mir klar, aber lässt sich das automatisieren? und so soll es sein: Bl.1 <-> Bl.2 C2 <-> A3 E2 <-> A4 G2 <-> A5 I2 <-> A6 K2 <-> A7 ... Vielen Dank für die Hilfe! .w

Hallo wjw,

kannst Du die Ansicht der beiden Blätter nicht angleichen?

D.h. beide Arbeitsblätter horizontal oder beide Arbeitsblätter vertikal.

Bsp:

Bl.1 <-> Bl.2

C3 <-> A3
C4 <-> A4
C5 <-> A5
C6 <-> A6
C7 <-> A7
...

Dann brauchts Du nur in einer Zelle den Bezug manuell herzustellen und Kannst anschließend die Formel kopieren.

Gruß boarder

hallo boarder,

ja, wenn das ginge, wärs ja einfach. Leider muß diese Struktur aber so bleiben! Danke trotzdem.

.w

hallo Leute,

ich bin nochmal so frei ...

ist das wirklich sooo schwierig? Niemand ne Idee?

Gruß+Dank,

.w

Hallo wjw,

eventuell liegt es ja daran, dass niemand so richtig verstanden hat, was genau Du möchtest. Jedenfalls geht es mir so. Ich habe nicht verstanden, was Du möchtest.

Erklär das doch bitte mal an einem Beispiel, was Du meinst.

MfG,
Oliver
_{Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du}
^{ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.}

ok, danke coros für das feed back.

also ich möchte mittels Link (innerhalb Excel heißt das vielleicht anders)
bei Anklicken einer bestimmten Zelle in Blatt 1 zu einer bestimmten Zelle in Blatt2 gelangen (und von dort mittels Klick auch wieder zurück). Soweit, so einfach. Diesen "link" kann ich manuell eingeben (Rechtsklick->Hyperlink-> etc.)

Da ich jedoch sehr viele solcher links einfügen muss, suche ich nach einer Lösung, die das Verfahren beschleunigt. Das wird durch die Tatsache erschwert, dass diese Zellbezüge nicht einem einfachen Muster folgen, sondern in Blatt 1 stehen alle in der gleichen Zeile (sagen wir in 2) aber nicht in angrenzenden Spalten, sondern in der übernächsten, also in C, E, G, I, K usw., die Ziele auf Blatt2 sind dann aber in einer Spalte untereinander. Also:

[Bl.1 <-> Bl.2]
C2 <-> A3
E2 <-> A4
G2 <-> A5
I2 <-> A6
K2 <-> A7

ist das Problem jetzt verständlicher?
sorry, wenn ich unpräzise war.

Dank+Gruß,

.w

Hallo wiw,

sorry, aber mir fällt dazu im Moment leider nichts Produktives ein, da ich das so sehe, dass ein Automatismus für dieses Problem nicht erstellt werden kann. Denn woher soll das Makro wissen, welches Blatt aufgerufen und welche Zelle beim Anklicken einer anderen Zelle markiert werden soll? Wenn es eine Regelmäßigkeit irgendwo in Deiner Datei gäbe, die ich aber bei dem was Du schreibst, eher nicht sehe, könnte man da sicherlich etwas über eine Schleife realisieren. Aber wenn die Daten, die man für die Links benötigt, wild im Tabellenblatt vorkommen, geht das nicht.

Sollte da niemand eine Idee haben, wird Dir wohl nichts anderes als Handarbeit übrig bleiben.

MfG,
Oliver
_{Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du}
^{ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.}
,

Hallo wjw,

folgender Code fügt Dir in Zeile 2 von Tabelle1 (jede 2. Spalte) einen Hyperlink ein, der zu Tabelle2 Spalte A, beginnend ab A3 springt.

Option Explicit

Sub Test()
Dim intI As Integer, intZ As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
intZ = 3
For intI = 3 To 20 Step 2
Cells(2, intI).Select
ws1.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Tabelle2!A" & intZ, TextToDisplay:="A" & intZ
intZ = intZ + 1
Next
End Sub

Den Endwert der For-Next-Schleife (20) kannst Du nach Bedarf anpassen.

Gruß
Rainer

Hallo Rainer,

ich glaube ich war heute vormittag schon das erste mal wieder müde. Irgendwie hatte ich nicht richtig verstanden, wie die Frage gemeint war, bzw. anders interpretiert. Durch Dein Makro habe ich es jetzt aber gerafft und das Makro sollte das machen, was sich wjw vorgestellt hat.

Schönen Gruß, von dem nun aber wieder aufgeachten Oliver. ;-)
^{Jeder macht was er will, keiner macht was er soll, aber alle machen mit.}

Hallo Oliver,

da kannst Du ja gleich den Rücksprung von den Zellen der Tabelle2 zu den Zellen der Tabelle1 noch programmieren, dann ist die Sache komplett.
Hab im Moment keine Zeit.

Gruß
Rainer

na, da seh ich doch Licht!

Danke Rainer und auch dir Oliver fürs anscheinend produktive pennen ;-}

Natürlich muss ich euch noch gestehen, dass ich von all dem was ihr schreibt nicht wirklich Ahnung habe. Werde mich aber damit befassen und versuchen zu verstehen, worum es geht.

Die Sache mit dem Rücksprung wäre allerdings wirklich sehr nett, wenn da nochmal jemand weiterhelfen könnte?

Also vielen Dank noch mal an alle Beteiligten. ich probier mal rum und melde mich wieder.

Gruß,

Werner

Hallo Werner,

habe Dir den Rücksprung noch dazu gebastelt.

In einer Inputbox kannst Du eingeben, wieviel Hyperlinks Du erzeugen willst.

Oliver hätte es vielleicht eleganter gamacht, aber was soll's, es läuft.

Option Explicit

Sub Test()
Dim intI As Integer, intZ As Integer, intAnz As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
intAnz = InputBox("Wieviel Hyperlinks?") + 2
intZ = 3
ws1.Select
For intI = 3 To (intAnz - 1) * 2 Step 2
Cells(2, intI).Select
ws1.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Tabelle2!A" & intZ, TextToDisplay:="A" & intZ
intZ = intZ + 1
Next
ws2.Select
For intI = 3 To intAnz
Cells(intI, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Tabelle1!" & Chr(60 + intI * 2 + 1) & "2", TextToDisplay:=Chr(60 + intI * 2 + 1) & "2"
Next
End Sub

Gruß
Rainer

Hallo Rainer,


besten Dank für deine Mühe! Leider kann ich nicht so viel damit anfangen.

Hab also dein Makro(?) kopiert und in meine Datei eingefügt.
(Alt+F11 dann in beide Blätter)

Leider müscht!

Da ich jetzt so nicht weiter komme, habe ich die Datei: test_wjw.xls mal von allen unnötigen Daten gesäubert und veröffentlicht:
http://www.netupload.de/detail.php?img=61c6ccf2cb9273662527051770ca...
Die Struktur ist aber erhalten; ich möchte also in Tabelle1 per Klick von den Zellen mit fortlaufenden Ziffern (in Zeile2) zur jeweils gleichen Ziffer (Spalte1) im anderen Blatt springen können und zurück. Aber das war ja schon klar.

Eine genauere Anleitung dazu würde mir doch sehr weiterhelfen.

Vielen Dank nochmal,

werner

Hallo Werner,

habe Dir das Makro eingebaut und einen Startbutton eingefügt.
Nach dem Starten kannst Du in der InputBox eingeben wieviel Hyperlinks eingefügt werden sollen.
Ich hatte das Makro noch mal geändert, weil es bei mehr als 26 Hyperlinks logischerweise falsche Spaltenbuchstaben geliefert hat.


http://www.netupload.de/detail.php?img=54838eb14d6da72f34907901307d...

Gruß
Rainer

wow!!

das flutscht ja ganz schön!
alle Achtung und besten Dank, Rainer.

eine Kleinigkeit tät ich mir noch wünschen:

ist es denn auch möglich in den verlinkten Zellen einfach nur 1, 2, 3, usw. drinstehen zu haben, oder muss ich dafür eine Hilfszeile/-spalte benutzen?

Nochmal vielen, vielen Dank für deine schnelle Hilfe,

Gruß,

.w

Hallo Werner,

das ist kein Problem, im Gegenteil, das vereinfacht den Code sogar noch.
Lösche den alten Code gänzlich und füge den folgenden dafür ein.

Option Explicit

Sub Test()
   Dim intI As Integer, intZ As Integer, intAnz As Integer
   Dim ws1 As Worksheet, ws2 As Worksheet
   Set ws1 = Worksheets("2007")
   Set ws2 = Worksheets("Übersicht")
   intAnz = InputBox("Wieviel Hyperlinks?") + 2
   intZ = 3
   Application.ScreenUpdating = False
   ws1.Select
   For intI = 3 To intAnz * 2 - 2 Step 2
      Cells(2, intI).Select
        ws1.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Übersicht!A" & intZ, TextToDisplay:="" & intZ - 2
        intZ = intZ + 1
   Next
   ws2.Select
   For intI = 3 To intAnz
      Cells(intI, 1).Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="2007!" _
      & intI, TextToDisplay:="" & intI - 2
   Next
   ws1.Select
   Range("A5").Select
   Application.ScreenUpdating = True
End Sub

Gruß
Rainer

Guten Morgen, Rainer

danke für die schnelle Antwort. Hast du's ausprobiert? Bei mir läufts nicht ganz rund: Von Tab1 zu Tab2 kein Problem aber beim Rücksprung kommt immer die Meldung "Bezug ist ungültig" Die Quickinfo zeigt, dass bei der "Adresse" kein Buchstabe, sondern nur eine Zahl drin ist.

Da stimmt noch was nicht ganz.

Gruß, Werner

Hallo Werner,

sorry, das war ein Irrtum meinerseits und noch dazu ungetestet.

Jetzt habe ich getestet.

http://www.netupload.de/detail.php?img=6a38506103c4569659bb30cc61ee...

Gruß
Rainer

Entschuldige Rainer, wenn ich dich nochmal nerve!
Ich bin sicher du kannst mir noch zwei Petitessen ausbügeln:

1. in deiner letzten Datei funktioniert zwar das Hin und Her sehr gut, aber leider beginnt die Liste die durch das Makro erstellt wird mit "3" und ich habe es nicht geschafft das zu ändern. Also alles ist ok wie es ist, aber statt 3, 4, 5, ... sollte (beginnend an der gleichen Stelle, also C2 bzw. A3) 1, 2, 3, ... stehen.
Ist das möglich? und wo genau ist das im Code definiert?

2. Ich habe versucht deinen Code in eine angepasste Datei zu kopieren und habe wegen der Umbennung des Blattes in "2008" zwei Stellen im Code von "2007" zu "2008" geändert (unten fett markiert). Trotzdem geht dann der Rücksprung nicht mehr. Was hab ich da falsch gemacht?

Option Explicit

Sub Test()
    Dim intI As Integer, intZ As Integer, intAnz As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("2008")
    Set ws2 = Worksheets("Übersicht")
    Application.ScreenUpdating = False
    Range("A2003").FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"""")"
    Range("A2002").FormulaR1C1 = "=COLUMN()"
    Range("A2002:A2003").Copy
    Rows("2000:2001").Select
    ActiveSheet.Paste
    intAnz = InputBox("Wieviel Hyperlinks?") + 2
    intZ = 3
    ws1.Select
    For intI = 3 To intAnz * 2 - 2 Step 2
        Cells(2, intI).Select
        ws1.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Übersicht!A" & intZ, TextToDisplay:="" & intZ
        intZ = intZ + 1
    Next
    ws2.Select
    For intI = 3 To intAnz
        Cells(intI, 1).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="2008!" _
        & WorksheetFunction.HLookup(intI * 2 - 3, Rows("2000:2001"), 2, 0) & "2", _
        TextToDisplay:="" & intI
    Next
    ws1.Select
    Rows("2000:2003").Clear
    Range("A5").Select
    Application.ScreenUpdating = True
End Sub

Nochmal 1000Dank!

w.

ich würd dir ja die Datei schicken, die ist jetzt aber nicht mehr "leer" und irgendwann muss ich's ja auch mal selbst hinkriegen! ;-}

Hallo Werner,

folgender Code ist an 4 Stellen geändert (fett markiert) und getestet.

Option Explicit

Sub Test()
    Dim intI As Integer, intZ As Integer, intAnz As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("2008")
    Set ws2 = Worksheets("Übersicht")
    Application.ScreenUpdating = False
    Range("A2003").FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"""")"
    Range("A2002").FormulaR1C1 = "=COLUMN()"
    Range("A2002:A2003").Copy
    Rows("2000:2001").Select
    ActiveSheet.Paste
    intAnz = InputBox("Wieviel Hyperlinks?") + 2
    intZ = 3
    ws1.Select
    For intI = 3 To intAnz * 2 - 2 Step 2
        Cells(2, intI).Select
        ws1.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Übersicht!A" & intZ, TextToDisplay:="" & intZ - 2
        intZ = intZ + 1
    Next
    ws2.Select
    For intI = 3 To intAnz
        Cells(intI, 1).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="2008!" _
        & WorksheetFunction.HLookup(intI * 2 - 3, Rows("2000:2001"), 2, 0) & "2", _
        TextToDisplay:="" & intI - 2
    Next
    ws1.Select
    Rows("2000:2003").Clear
    Range("A5").Select
    Application.ScreenUpdating = True
End Sub

Gruß
Rainer

super!!!

ganz herzlichen Dank.

Gruß, .w

Guten Abend,

vor allem an Rainer, aber natürlich auch an alle anderen:

dein Makro funzt wunderbar, deshalb wollte ich dich auch bei der Lösung eines andern Problems mit dieser Datei um Rat bitten.

Vor einiger Zeit hatte ich deshalb hier schon mal gepostet

https://supportnet.de/threads/1266072 dort Punkt 2 (Punkt 3 ist dank Rainer mitterweile gelöst)

und von event ein Makro bekommen, das nie so richtig gut lief. Dabei ging es darum, die doppelte Vergabe einer Auftragsnummer zu verhindern.

Ich habe eine Datei vorbereitet,
http://www.netupload.de/detail.php?img=4238c52a635071e189971df00620...
in der alles Weitere genau erklärt ist. Kann mir jemand bitte erklären, warum sich dieses Makro so kapriziös verhält?

Vielen Dank an alle, die sich die Mühe machen zu helfen.

Viele Grüße,

.w

Hallo Werner,

zu Pkt. 1 habe ich keine Lösung

zu Pkt. 2 brauchst Du kein Makro, das geht mit der Gültigkeitsprüfung

Gehe wie folgt vor:
- markiere die gesamte Spalte F
- klicke auf Menü > Daten > Gültigkeit
- wähle unter "Zulassen: " die Option "Benutzerdefiniert"
- trage unter "Formel: " folgende Formel ein und klicke auf "OK"

=ZÄHLENWENN(F$1:F1;F1)=1

Habe das Zelformat in Spalte F geändert in 0000" "####
Die Eingaben müssen ohne führende Null(en) und ohne Leerzeichen erfolgen.

Habe schon allen in die Datei eingebaut.

http://www.netupload.de/detail.php?img=48c816ca8fe6e1f96347528349a0...

Gruß
Rainer

Moin Rainer,

vielen Dank! Ok die Gültigkeitsprüfung kannte ich nicht. So richtig schön finde ich aber das noch nicht, denn jetzt muß man die Meldung noch wegklicken, da ein völlig sinnloses "wiederholen" angeboten wird. Geht das irgendwie schlichter?

Wie müsste ich denn die Formel anpassen, wenn ich sie auf die Zeile 2 (ab C bis Ende) anwenden wollte?
Anders gefragt: wofür stehen in der Formel "F$1", "F1" und "1"

Übrigens: Gibt es eine Möglichkeit bei der Eingabe den Leerschritt irgendwie bezuhalten? Die führende Null scheint jedenfalls nicht zu stören.

Vielen Dank und Grüße,

.w

Hallo Werner,

wenn Dich die Meldung der Gültigkeitsprüfung stört, dann bleibt doch nur ein Makro.

Folgender Code funktioniert, wenn das Leerzeichen mit eingegeben wird.
Sollte eine führende Null gewünscht werden, wenn der vordere Teil nur aus drei Zahlen besteht, muss diese ebenfalls mit eingegeben werden.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strNeu As String
   If Not Intersect(Target, Range("F:F")) Is Nothing Then
      strNeu = Target.Value
      If WorksheetFunction.CountIf(Range("F:F"), strNeu) = 2 Then
         Target.Value = ""
         Exit Sub
      Else
         Target.Value = strNeu
      End If
   End If
End Sub

http://www.netupload.de/detail.php?img=75eb5c57c5065882163b7a6e1ca2...

Die Formel für Deine Anschlussfrage lautet:

=ZÄHLENWENN($C$2:$IV$2;A2)=1

Gruß
Rainer

ich bin in deiner Schuld, Rainer!

danke, dass du dir die Mühe machst.

Ich habe deinen Code angepasst und weiterverwendet; das Ergebnis verstehe ich jetzt gar nicht mehr! Schaust du bitte noch mal rein? Alles weitere steht in der Datei.
http://www.netupload.de/detail.php?img=9dc49ca86f0d58411be5eaf19392...

Bitte entschuldige meinen ausgeprägten Hang zum Perfektionismus.
(Du sagst einfach, wenn's dir reicht)

Viele Grüße,

.w

Hallo Werner,

ich glaube hier liegt ein Missverständnis vor.

Das Makro führt in Spalte F keine Korrekturen aus sondern verhindert lediglich die Eingabe einer bereits vorhandenen Nummer.

Und so habe ich eigentlich auch Deine Frage verstanden.

Bei gleicher Eingabe wird diese also wieder gelöscht, denn das Makro kann ja nicht wissen, was es nun korrigieren sollte.

Eine weitere Frage wäre, warum willst Du die gleichen Nummern im Blatt "2008" Zeile 1noch mal eingeben, sie könnten doch per Formel aus dem Blatt "Übersicht" übernommen werden???
Die gleiche Prozedur wäre auch umgekehrt möglich, müsstest Dich nur entscheiden in welchem Blatt Eingabe / Übernahme erfolgen sollen.

Weiterhin wäre es schön, wenn Du Dich entscheiden könntest, wie das Eingabeformat der Zahlen endgültig aussehn soll.
Günstig wäre, wie ich es Dir bereits geschrieben habe.

Ich warte jetzt erst mal Deine Antworten ab.

Gruß
Rainer

Hallo Rainer,

danke für deine Nachricht; ja, tatsächlich hab ich mich wohl missverständlich ausgedrückt.

Natürlich wollte ich keine Korrektur der 8stelligen Nr. per Makro; über die Formatierung hatte ich mir nur die Eingabe der Leerstelle erspart. Das Format sollte am Ende immer xxxx_xxxx sein, wobei die erste Stelle oft eine 0 sein muss.

Dein Makro sollte nur die Doppeleingabe verhindern, was ja auch auf dem Blatt "Übersicht" wunderbar funktioniert. Natürlich kann ich bei der manuellen Eingabe die dafür notwendigen Regeln (mit oder ohne Leerstelle) beachten.
(Da mehrere Benutzer am Werk sind, wäre es aber am Bequemsten wenn das Makro beide Varianten verarbeiten könnte und die Formatierung unabhängig davon für den Rest sorgte - aber das ist vielleicht zu viel verlangt.)

Als ich das Makro rüberkopiert hatte (nach 2008), war aber dort das Eingabeverfahren nicht mehr eindeutig, will heißen: mit der gleichen Methode hatte ich bei den beiden Typen der Nummern (führende 0 oder nicht) verschiedene Ergebnisse oder das Makro funktionierte nicht wie gewünscht! Das lag wahrscheinlich an einem Fehler bei der Anpassung des Makros.

Die Idee per Formel aus "Übersicht" zu übernehmen ist natürlich die beste Lösung, aber auch da hab ich keine Ahnung wie das geht. Hier stellte sich mir das gleiche Problem wie ganz zu Anfang mit den Hyperlinks.

Wenn ich das jetzt verständlicher machen konnte und du noch ein letztes Mal Hand anlegtest, bitte?

Vielen herzlichen Dank und liebe Grüße,

werner

Hallo Werner,

anbei die Datei zurück.

Die Formeln in "2008" Zeile 1 sind eingetragen, die gesamte Zeile muss das Format "Standard" haben.

In "Übersicht" Spalte F gibst Du eine 8-stellige Zifferfolge ein, das Leerzeichen wird vom Makro eingefügt.
Die Spalte F muss als "Text" formatiert sein.

http://www.netupload.de/detail.php?img=dc9bc7c6ad606e1fa57e240b9623...

Gruß
Rainer

Hallo Werner,

bevor Du mir sagst, dass die Berechnungen in der letzten Datei nur zeitverzögert erfolgen (was ich auch gemerkt habe), anbei noch eine Variante bei der die Auftragsnummern per Makro übertragen werden.

Du kannst das Makro immer dann starten, wenn Du in Spalte F eine Änderung vorgenommen hast.

Bei dieser Gelegenheit habe ich noch bemerkt, dass Du für die horizontale Summenbildung eine äußerst komplizierte und fehlerträchtige Monsterformel verwendest.
Ich habe Dir im roten Feld eine Formel eingetragen, die das Gleiche auf elegantere Art macht.

Diese Formel kannst Du auch für die anderen Summen verwenden, brauchst nur den zutreffenden Zeilenindex anzupassen.

http://www.netupload.de/detail.php?img=65882d1136868a29ba1d9d76da27...

Gruß
Rainer

Vielen Dank, Rainer!

ich werde mir jetzt alle deine Verbesserungen in Ruhe ansehen müssen, komme dazu aber nicht vor Montag.

Die Datei von gestern war schon eine große Hilfe!

Viele Grüße,

.w

• 2 Zellen in Tabellenblatt 1in Tabellenblatt 2 automatisch übernehmen
• Excel tabellen verweis
• nachtstunden in mehren bereichen ausrechnen????
• Verweis auf Zellen mit Formeln
• Excel: Funktion Verweis mit Text statt Zahlen