Verknüpfung per Formel mit Übernahme der Formate

Forumthread: Verknüpfung per Formel mit Übernahme der Formate

22.04.2015 21:29:20

Vetter

Hallo zusammen und einen schönen guten Abend.
Wieder ein neuer User, der Fragen stellt für die es keine einfache Lösung im Netz gibt?
Habe alle Schlagwörter ausprobiert, aber keine Lösung gefunden.
Ich importiere eine Tabelle aus dem Netz bei der auch Formatierungen übergeben werden, diese Tabelle steht in einem Bereich den ich nur als Zwischenablage verwende. Im Tabellenblatt werden dann die benötigeten Spalten (Beispiel N:R) auf die Spalten A bis L unterschiedlich verteilt. WIchtig war mir die Spalte N denn dort sind Formatierung vorhanden die eine einfache optische Zuordnung erkennen lassen.
Beispiel: GER RegW die Schriftfareb ist Gelb und die Zellfarbe ist Schwarz
AUT D2 ist die Schriftfarbe Weiß und die Zellfarbe Rot

Wenn ich nun mit =N4 arbeite wird der Inhalt also die Werte in die Zelle A4 geschrieben, aber die Formatierungen nicht. Nun könnte ich die ganze Spalte immer wieder erneut kopieren und per Urspungsformatierung einfügen durchführen. Da es aber 7 Tabellen sind und diese sich mehrmal am Tag ändern können, ist es etwas lästig dieses jedesmal durchzuführen. Gibt es eine einfache Möglichkeit nicht nur den die Werte per Formel zu verknüpfen sondern auch die Formatierungen?
Bedingte Formatierung ist zwar eine Option, dann müsste ich aber mehr als 80 bedingungen einfügen, wäre zwar eine einmalige Sache, aber davor wollte ich mich noch drücken.
Leider habe ich überhaupt keine Ahnung von VBA, denn davon habe ich genügend Hilfe gefunden. Als letzte Möglichkeit könnte ich die wenigen Schritte per Makro aufzeichnen und dann abspielen lassen, denn der Tabellenanfang ist immer in der gleichen Zeile, nur das Ende ist immer unterschiedlich.
Ich würde mich über eine Antwort freuen und ob ich in Excel wirklich gut bin, ist Ansichtssache. Hintergund ist meine Excelbasierende Auswertung zu den Fußballspielen die es jedes Wochenende gibt. Hier versuche ich über die Form und andere Kriterien die besten 40 Spiele zu finden um dann einen kleinen Kombi mit 2 zu tippen ist halt ein Hobby von mir.
Mit freundlichen Grüßen
Hans Holger

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Verknüpfung per Formel mit Übernahme der Formate

23.04.2015 13:19:19

fcs

Hallo Holger,
Zellformate kann man nicht per Formel aus den Quellzellen mit übernehmen - Zahlenformaten passiert es manchmal. aber bei mir dann meist unerwünscht.
Wenn deine Formeln so einfach gestrickt sind (z.B.: =N4) dann kannst du mit nachfolgendem Makro die Zellformate in der Spalte N in die Zellen mit Formeln in Spalten A bis L übernehmen.
Gruß
Franz

Sub Holger_Formateuebernehem()
Dim wks As Worksheet, Zeile_L, Zelle As Range, strZelle_N As String
Application.ScreenUpdating = False
On Error GoTo Fehler
With ActiveWorkbook
For Each wks In .Worksheets
Select Case wks.Name
Case "Tab1", "Tab2", "Tab3", "Tab4", "Tab5", "Tab6", "Tab7"
'Namen der Tabellen, in denne Formate aus Spalte N übernommen werden sollen
With wks
Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
'Spalten A bis L - Formate zurücksetzen
With .Range(.Cells(2, 1), .Cells(Zeile_L, 12))
.Interior.ColorIndex = xlColorIndexNone
.Font.Bold = False
.Font.ColorIndex = xlColorIndexAutomatic
.Font.Italic = False
End With
'In Spalten A bis L für Zellen mit Formel Formate aus Spalte N übernehmen
For Each Zelle In .Range(.Cells(2, 1), .Cells(Zeile_L, 12)).Cells
If Zelle.HasFormula Then
strZelle_N = Zelle.Formula
strZelle_N = Mid(strZelle_N, 2)
strZelle_N = VBA.Replace(strZelle_N, "$", "")
.Range(strZelle_N).Copy
Zelle.PasteSpecial Paste:=xlPasteFormats
End If
NextZelle:
Next
Application.CutCopyMode = False
End With
End Select
Next
End With
Fehler:
With Err
Select Case .Number
Case 0 'alles OK
Case 1004
Resume NextZelle
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description, _
vbOKOnly, "Makro: Holger_Formateuebernehem"
End Select
End With
Application.ScreenUpdating = True
End Sub

AW: Verknüpfung per Formel mit Übernahme der Formate

23.04.2015 14:22:52

Hans

Guten Tag Franz und vielen Dank für die VBA-Lösung,
dachte mir schon das ich an VBA nicht vorbei komme, da es im Netz keine Lösung per Formel zu finden war.
Nur eben die bedingte Formatierung hatte eine Lösung über "Zellwert enthält" aber eben 80mal, da ich mehrere Textpassage habe die mit der gleichen Farbe einfärbt werden, dachte ich das könnte ich dann mit einer Oder-Formel lösen. Aber hier wurden dann alle Zellen die nicht genau dem entsprochen haben mit einer Farbe gefüllt.
Da ich im Bezug auf VBA leider ein vollkommener Anfänger bin (habe mich davor immer gedrückt) kann ich das Skrip nun einfach kopieren und in einem Modul einfügen? Es soll ja für die Gesamte Datei gültig sein aber eben nur für die 7 Tabellenblätter Montag bis Sonntag. Die Blätter mit anderen Namen dürfen nicht berührt werden, wobei es wohl keine Probleme geben dürfte, wenn dort dann in der Spalte A Farben erscheinen, denn dort hole ich mir nur Werte für die anderen Tabellen.
Zu einfache Formel:
In den Tabellen arbeite ich mit SVerweis, Index, Zählewenn,Teil, Links, Rechts und anderen Funktionen um ein Kennziffer zu einem Spiel zu erstellen.
nur zur Info obwohl das wohl keinen interessieren wird:
Spiel Bayern München vs. Hertha meine Kennzifferen die errechnet wurden, geben folgende Tipparten aus:
Heimsieg mit mehr als ein Tor, Team Tore Heim minimum 2+, HC1-1 mit ?, Over 2,5 zu 75%, trifft in beiden Halbzeiten 80%, Bayern ohne Gegentor 50%, beide Goal im Spiel 50% und dann gibt es noch weitere Tipparten. Die Tippart mit der größten Chance durchzukommen im Verhältnis zur angeboten Quote wird in einen Kombi gepackt mit eben anderen Spielen die mir Excel als Wahrscheinlichkeit rausfiltert. Nur die falschen das kann Excel noch nicht :-(
Vielen Dank für weitere Informationen und für die o.a. Lösung :-)

AW: Verknüpfung per Formel mit Übernahme der Formate

23.04.2015 14:43:56

Hans

Hallo Franz,
zu dem Teil welche Tabellenblätter....., hätte das mal alles lesen sollen was im Skript steht, habe nun Tab1 - Tab7 gegen die Tabellennamen ausgetauscht und es klappt super.
Vielen vielen Dank
Mit freundlichen Grüssen Hanas HolgerV

Infobox / Tutorial

Excel-Verknüpfung mit Formatierungen: So geht's!

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Öffne deine Excel-Datei und navigiere zu dem Tabellenblatt, in dem du die Verknüpfungen erstellen möchtest.
Stelle sicher, dass die gewünschte Tabelle, von der du die Daten übernehmen möchtest, bereits vorhanden ist.
Verwende die einfache Zellverknüpfung, um Werte zu übernehmen. Beispielsweise kannst du =N4 in die Zelle A4 eingeben, um den Wert aus Zelle N4 zu übernehmen.

Um auch die Formatierungen zu übernehmen, benötigst du ein VBA-Skript. Kopiere den folgenden Code in ein neues Modul:

Sub Holger_Formateuebernehem()
   Dim wks As Worksheet, Zeile_L, Zelle As Range, strZelle_N As String
   Application.ScreenUpdating = False
   On Error GoTo Fehler
   With ActiveWorkbook
       For Each wks In .Worksheets
           Select Case wks.Name
               Case "Tab1", "Tab2", "Tab3", "Tab4", "Tab5", "Tab6", "Tab7"
                   With wks
                       Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
                       With .Range(.Cells(2, 1), .Cells(Zeile_L, 12))
                           .Interior.ColorIndex = xlColorIndexNone
                           .Font.Bold = False
                           .Font.ColorIndex = xlColorIndexAutomatic
                           .Font.Italic = False
                       End With
                       For Each Zelle In .Range(.Cells(2, 1), .Cells(Zeile_L, 12)).Cells
                           If Zelle.HasFormula Then
                               strZelle_N = Zelle.Formula
                               strZelle_N = Mid(strZelle_N, 2)
                               strZelle_N = VBA.Replace(strZelle_N, "$", "")
                               .Range(strZelle_N).Copy
                               Zelle.PasteSpecial Paste:=xlPasteFormats
                           End If
                       Next Zelle
                   End With
           End Select
       Next
   End With
Fehler:
   With Err
       Select Case .Number
           Case 0 'alles OK
           Case 1004
               Resume Next
           Case Else
               MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description, _
               vbOKOnly, "Makro: Holger_Formateuebernehem"
       End Select
   End With
   Application.ScreenUpdating = True
End Sub

Passe die Namen der Tabellenblätter im Skript an deine Bedürfnisse an.
Führe das Makro aus, um die Formatierungen von den Quellzellen zu übernehmen.

Häufige Fehler und Lösungen

Format wird nicht übernommen: Stelle sicher, dass die Zellen in den Quellbereichen tatsächlich formatiert sind und das Makro korrekt auf die Zellen zugreift.
Fehlermeldung beim Ausführen des Makros: Überprüfe, ob die Tabellennamen im Makro genau mit deinen übereinstimmen. Achte auch darauf, dass keine Leerzeichen oder Tippfehler vorhanden sind.

Alternative Methoden

Eine alternative Option zur Übernahme von Formatierungen wäre die Verwendung der bedingten Formatierung. Du kannst Bedingungen basierend auf Zellwerten festlegen, um die Formatierung zu steuern. Für sehr viele Bedingungen könnte dies jedoch unpraktisch werden.

Praktische Beispiele

Ein Beispiel für die Verwendung der Verknüpfung könnte eine Tabelle sein, in der Spielstatistiken aus einer anderen Tabelle übernommen werden. Du kannst die Zellen für Heimsieg oder Auswärtssieg farblich hervorheben, basierend auf bestimmten Werten, indem du die bedingte Formatierung nutzt.

Tipps für Profis

Nutze die Formatierung auf alle Tabellenblätter übertragen-Funktion, um ein einheitliches Design über mehrere Blätter hinweg zu gewährleisten.
Wenn du oft mit verschiedenen Formaten arbeitest, speichere häufig genutzte Formatierungen in eigenen Vorlagen oder nutze VBA, um diese automatisiert zu übernehmen.

FAQ: Häufige Fragen

1. Kann ich die Formatierung auch ohne VBA übernehmen?
Leider ist es in Excel nicht möglich, Zellformate per Formel zu übernehmen. VBA ist hier die beste Lösung.

2. Wie kann ich die bedingte Formatierung effizient nutzen?
Du kannst die Funktion "Format übertragen" nutzen, um Formatierungen schnell von einer Zelle auf andere zu übertragen, ohne sie manuell neu einzustellen. Achte dabei auf die richtigen Zellbezüge.

Beliebteste Forumthreads (12 Monate)

Verknüpfung per Formel mit Übernahme der Formate

Forumthread: Verknüpfung per Formel mit Übernahme der Formate

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Excel-Verknüpfung mit Formatierungen: So geht's!

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Verknüpfung per Formel mit Übernahme der Formate

Forumthread: Verknüpfung per Formel mit Übernahme der Formate

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Excel-Verknüpfung mit Formatierungen: So geht's!

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread