Verknüpfte Werte übernehmen ohne Formel zu löschen

[sens.sash]

Hallo liebe Forumsnutzer,

ich habe soeben sehr lange nach einer passablen Lösung geschaut, jedoch waren alle für einen anderen Zweck.

Zum Problem:

Ich bin im Besitz einer Masterdatei.xls mit 5 Tabellenblättern 1-5. Alle Tabellenblätter sind miteinander verknüpft.
Tabellenblatt 1 hat einen Wert der in Tabellenblatt 3 zum weiterrechnen genutzt wird.
Tabellenblatt 5 hat ebenfalls Werte die in 3 zum weiterrechnen genutzt werden.

Die Weiterberechnung erfolgt in Formeln. z.B. =A1*Summe(Tabelle1!SpalteA:A)*Tabelle5!C6)

Nun möchte aus der Masterdatei.xls von 5 Tabellenblättern nur 3 in eine Zweite Datei Masteratei2.xls übertragen.
Nach der Übertragung sollen die Werte aus den nicht übertragenen Tabellenblättern als harte Werte in der Formel auftauchen.

Es soll hierbei aber nicht das komplette Formelergebnis als harter Wert ersetzt werden, sondern nur der Wert aus der Verlinkung.

Wie in oben genanntem Beispiel:

=A1*Summe(harter Wert für Tabelle1!SpalteA:A)* harter Wert für Tabelle5!C6)
=A1*Summe(5000)*300)

Ich hoffe ich konnte es einigermaßen verständlich beschreiben und jemand kann mir helfen.

Vielen Dank

 

[CHaos.Gentle]

Moin,
eine Standardfunktion dafür gibt es leider nicht. Wenn ich das richtig verstanden habe, willst du alle Referenzen auf andere Tabellenblätter durch ihre Werte ersetzen.
Eine Lösung per VBA ist möglich, ungefähr so:

Sub asdf()
Dim tterm
Dim term
Dim erg As String
Dim ref

For i = 3 To 3
  For j = 1 To 3
    erg = ""
    If InStr(1, Me.Cells(i, j).FormulaLocal, "!") Then
      tterm = Split(Me.Cells(i, j).FormulaLocal, "*")
      For Each term In tterm
        If Not InStr(1, term, Me.Name) = 1 Then
          term = Replace(term, "=", "")
          ref = Split(term, "!")
          If erg = "" Then
            erg = Worksheets(ref(0)).Range(ref(1)).Cells(1, 1).Value
          Else
            erg = erg & "*" & Worksheets(ref(0)).Range(ref(1)).Cells(1, 1).Value
          End If
        Else
          If erg = "" Then
            erg = erg & term
          Else
            erg = erg & "*" & term
          End If
        End If
      Next term
      Me.Cells(i, j).FormulaLocal = "=" & erg
    End If
  Next j
Next i
End Sub

Problem ist hier, dein Beispiel war mit Multiplikation, so ist es auch meine Antwort. Wenn du andere mathematische Operatoren nutzt, musst du den ersten Split durch eine Funktion ersetzen, die das kann.
Und natürlich musst du i und j so setzen, dass sie deinen Bereich ergeben.

 

[sens.sash]

Hallo CHaos.Gentle,

ich glaube du hast das richtig verstanden. Danke schonmal für die Antwort.
Leider bin ich nicht der krasse VBA crack und verstehe nur ein paar Zeilen, von denen ich glaube, dass sie genau das machen, was sie sollen.

Jetzt habe ich nur das Problem, dass meine Tabelle 3 so viele Verlinkungen erhält, dass es nicht nur in Multiplikationen, sondern auch in allen anderen Rechenoperationen münden kann.

Gibt es einen (allgemeineren) Weg?

Also grob gesprochen:

1. Wenn Verlinkung
2. Dann nimm Wert der Verlinkung
3. und Ersetze die Verlinkung mit dem Wert innerhalb der Formel

 

[cumulonimbus8]

Ich denke, ich habe begriffen was du willst. Mir ist da aber ein Aspekt unklar.
OK, mit dem gegebenen Makro sollte ich alle Referenzen durch die Werte der Quellzellen ersetzen.

Wenn aber aus 3 der 5 Blätter ein neuer Master werden soll - könnte es da sein, dass (orientieren wir uns an oben) das mittlere Blatt weiter Referenzen auf 1 und 3 enthalten soll? In dem Fall wäre das radikale Makro womöglich keine Hilfe.

Lässt sich die Aufgabe genauer stellen um vielleicht mit einem passend frisierten Makro eine Punktlandung mit Mehrwert zu machen?

CN8

 

[CHaos.Gentle]

@sens.sash Ich gehe jetzt mal davon aus, dass du mit "Verlinkung" Referenzen auf andere Tabellenblätter meinst. Ansonsten erschließt sich mir der Sinn des Ganzen einfach nicht. Wenn du alle Referenzen entfernen willst, dann kannst du auch einfach kopieren und einfügen als Wert.
Bei dem Makro oben wird eben aus

=Tabelle1!A1*Tabelle2!A1*Tabelle3!A1

=Wert aus Tabelle1!A1 * Wert aus Tabelle2!A1 * Tabelle3!A1

Also alle Referenzen auf anderen Tabellenblätter werden durch ihren Wert ersetzt.

Um das Ding auf weitere mathematischen Operatoren zu erweitern muss man sich nur noch ne Split-Funktion bauen.

Das könnte ich machen, allerdings muss ich dazu wissen, ob zwischen den einzelnen Referenzen tatsächlich nur mathematische Operatoren stehen. Wenn du auch noch irgendwelche Funktionen (Durchschnitt, Summe, oä.) nutzt, muss man komplett umdenken.

 

[sens.sash]

Hallo Danke für die Antwort.

Ich probiere es nochmal so einfach ich kann:

Ursprung:
Masterdatei.xls

• Tabelle 1
• Tabelle 2
• Tabelle 3
• Tabelle 4

Result:
Masterdatei2.xls

• Tabelle 2
• Tabelle 3

Im Ursprung zieht sich Tabelle 2 aus den jeweiligen anderen Tabellen (1,3,4) ganz unterschiedliche Werte und rechnet mit diesen in Formeln weiter.

Im Result sollen nun alle Formeln der Tabelle 2 erhalten bleiben.

Die Fomeln können hierbei folgenden Charakter aufweisen:

Formel mit Bezug auf Tabelle 1, 3 und/oder 4 + irgendeine Berechnung (z.B. 3600/60)



Ich bin kein VBA Crack aber könnte mir auch vorstellen:

1. Bevor du das Blatt kopierst, schau nach allen Bezügen außerhalb des Tabellenblattes
2. Schreibe diese Bezüge der Reihe nach, in separate Zeilen der Tabelle2
3. Ersetze alle Bezüge auf andere Tabellen, mit den soeben geschaffenene Zeilen (Natürlich den richtigen)

Vielen lieben Dank

Ergänzung (26. September 2018)

@sens.sash Ich gehe jetzt mal davon aus, dass du mit "Verlinkung" Referenzen auf andere Tabellenblätter meinst. Ansonsten erschließt sich mir der Sinn des Ganzen einfach nicht. Wenn du alle Referenzen entfernen willst, dann kannst du auch einfach kopieren und einfügen als Wert.
Bei dem Makro oben wird eben aus

=Tabelle1!A1*Tabelle2!A1*Tabelle3!A1

=Wert aus Tabelle1!A1 * Wert aus Tabelle2!A1 * Tabelle3!A1

Also alle Referenzen auf anderen Tabellenblätter werden durch ihren Wert ersetzt.

Um das Ding auf weitere mathematischen Operatoren zu erweitern muss man sich nur noch ne Split-Funktion bauen.

Das könnte ich machen, allerdings muss ich dazu wissen, ob zwischen den einzelnen Referenzen tatsächlich nur mathematische Operatoren stehen. Wenn du auch noch irgendwelche Funktionen (Durchschnitt, Summe, oä.) nutzt, muss man komplett umdenken.

Ja es sind Referenzen gemeint.

Summe/Mittelwert/alle mathematischen Operatoren
Es ist nicht definiert, durch welche Zeichenfolge eine Referenz "beginnt" oder "endet"

 

[CHaos.Gentle]

Okay, damit nimmt das Ganze langsam Formen an. Ich denke die Idee von oben kann man verwerfen.
Wesentlich sinnvoller wird es sein, den kompletten String zu durchlaufen und die einzelnen Stringteile durch die Werte zu ersetzen...klingt einfach - ist es "fast" auch
Komme ich heute nicht mehr zu...vielleicht morgen.

Deshalb ist es im Übrigen gerade bei solchen Excelfragen absolut sinnvoll, wenn man einen Auszug liefert.

 

[sens.sash]

Okay, damit nimmt das Ganze langsam Formen an. Ich denke die Idee von oben kann man verwerfen.
Wesentlich sinnvoller wird es sein, den kompletten String zu durchlaufen und die einzelnen Stringteile durch die Werte zu ersetzen...klingt einfach - ist es "fast" auch
Komme ich heute nicht mehr zu...vielleicht morgen.

Deshalb ist es im Übrigen gerade bei solchen Excelfragen absolut sinnvoll, wenn man einen Auszug liefert.

Hi CHaos.Gentle,

leider ist die Datei so umfangreich, dass ich sie nicht hochladen kann.
Ich könnte dir aber eine nachbauen, falls das (noch) gewünscht ist?

Mit freundlichem Gruß

 

[Tarkoon]

Ich hab im Web glaube ich eine Lösung entdeckt, die allerdings so direkt nur auf englischem Office läuft und sie ist mir jetzt erstmal zu komplex, um kurzfristig herauszufinden, was umzuschreiben wäre, um sie auf deutschem Office lauffähig zu machen.
http://www.vbaexpress.com/forum/sho...-values-in-other-language&highlight=precedent
Lies dir den Thread mal durch, ggf. bringt der dich ja weiter.

 

[sens.sash]

Ich hab im Web glaube ich eine Lösung entdeckt, die allerdings so direkt nur auf englischem Office läuft und sie ist mir jetzt erstmal zu komplex, um kurzfristig herauszufinden, was umzuschreiben wäre, um sie auf deutschem Office lauffähig zu machen.
http://www.vbaexpress.com/forum/sho...-values-in-other-language&highlight=precedent
Lies dir den Thread mal durch, ggf. bringt der dich ja weiter.

Hallo,

also ich habe es am Wochenende versucht nachzuvollziehen, aber irgendwie komme ich hier nicht weiter...
Wäre evtl. jemand so lieb und könnte sich der Sache nochmal annehmen?

Danke

 

[DPXone]

Ich glaube, da führt kein Weg an Regulären Ausdrücken vorbei.

Hier mal ein Pattern, der für Verweise auf andere Tabellenblätter funktionieren sollte, sofern kein Sonderzeichen im Tabellennamen enthalten ist (lässt sich aber auch ergänzen):
https://regex101.com/r/QNCEkU/3

'?[a-zA-Z0-9 ]*'?\![a-zA-Z]{1,3}[0-9]{1,7}(:[a-zA-Z]{1,3}[0-9]{1,7})?

Problem:
Wie wird mit Verweisen auf Bereiche umgegangen?
z. B. =Summe(Tabelle2!A1:Z300)
Oder gibt es sowas nicht?

 

[Tarkoon]

Das mit den Bereichen wäre natürlich echt aufwändig, aber an sich ginge das über die Matrixschreibweise, die man aufbauen könnte, indem man den Bereich mit Schleifen durchläuft.
Das würde ich als separate Funktion coden, die nen übergebenen Bereich in einen String mit der Matrixschreibweise überführt.

 

[zindelino]

Ich glaube ich hab den Sachverhalt noch nicht komplett verstanden, aber wäre es nicht einfach eine Möglichkeit, dass du die Datei unter neuem Namen abspeicherst, dann die nicht mehr benötigten Tabellenblätter löscht und alle Formeln und Bezüge die "harte Zahlen" erhalten sollen, holst du dir aus der 1. Datei über kopieren -> Wert einfügen?