Mit SVERWEIS auch Zellfarbe übertragen

[smily44]

Hallo zusammen
Vor einiger Zeit hat mir ein Forumsteilnehmer mit der folgende Matrix-Formel ein Problem gelöst.

{=WENNFEHLER(INDEX(Teilnehmer!$H:$H;KKLEINSTE(WENN(Teilnehmer!$K$1:$K$68=B$49;ZEILE($1:$68));ZEILE(F1)));"")}

Nun gibt es in "Teilnehmer!$H_$H" Zellen mit einer bedingten Formatierung --> Zellfarbe abhänig eines Zeichens in Teilnehmer!$A:$A, z.B. "z = Zellfarbe gelb, z1= grün, z2= blau.

In einem anderen Forum habe für solche "Zellfarben-Mitnahme" folgende Verweisformel gefunden. Nun scheitere ich mit der Verbindung dieser zwei Formelstrings.

=WENN(ISTFEHLER(SVERWEIS(A6;Tabelle2!$A$2:$B$4;2;FALSCH));"";SVERWEIS(A6;Tabelle2!$A$2:$B$4;2;FALSCH))

Ich hoffe, dass mir jemand helfen kann und bedanke mich schon jetzt für die Unterstützung.

Best Grüsse
Smily44

 

[sirion]

Hallo Smily44

Mit Sverweis (oder ganz allgemein mit irgendeiner Art Formel) ist es meines Wissens nicht möglich, die Zellfarbe auszulesen und zu übertragen. Dazu ist wäre ein Makro (VBA) nötig.

Ich weiss nicht, wie deine Datei aussieht, aber du könntest evtl. die selbe Art von bedingter Formatierung, die du in deiner Teilnehmer-Tabelle hast, anwenden (allenfalls mittels einer Hilfsspalte).

 

[smily44]

Hallo Sirion,
Vielen Dank für deine Hinweise!

Aufgrund des zweiten Verweises den ich in meinen Beitrag reingestellt habe müsste es eigentlich mit SVERWEIS gehen. Natülich muss ich dazu den Verweis auf meine Spaltensituation anpassen - das schaffe ich, aber nicht die Verbindundund der zwei Verweis-Strings.

Gruss Smily44

 

[sirion]

Du meinst diese Formel ?
=WENN(ISTFEHLER(SVERWEIS(A6;Tabelle2!$A$2:$B$4;2;FALSCH));"";SVERWEIS(A6;Tabelle2!$A$2:$B$4;2;FALSCH))

Nein, ich fürchte, damit lässt sich kein Format auslesen, diese kann lediglich Werte (aber keine Formate) wiedergeben, die in Tabelle 2 in Spalte B stehen. Es wird somit auch nichts helfen, diese mit deiner bestehenden Matrix-Formel zu verbinden.

Vielleicht hat hier jemand anderes eine Lösung parat.

 

[smily44]

Hallo an alle,

Gemäss diesem Beispiel funktioniert aber der SVERWEIS

http://hajo-excel.de/vba_fuellfarbe.htm

in dieser Tabelle Beispiel "Füllfarbe1 übernehmen"

Gruss Smily44

 

[sirion]

Wie ich mir gedacht habe, wurde das in diesem Beispiel mit VBA (Makro) gelöst.
Der Sverweis übernimmt auch in diesem Beispiel nur die Werte (Texte), nicht die Formatierungen. Die Formatierungen werden mittels des im Excel-File eingebetteten Makros übertragen.

Das Makro anzeigen kannst du über die Registerkarte "Entwickleroptionen" -> "Visual Basic" oder über die Tastenkombination Alt+F11.
Im dortigen VBAProject findest du dann den Quellcode:

Option Explicit                             ' Variablendefinition erforderlich

Private Sub Worksheet_Change(ByVal Target As Range)
    '**************************************************
    '* H. Ziplies                                     *
    '* 18.08.10                                       *
    '* erstellt von HajoZiplies[at]web.de             *
    '* http://Hajo-Excel.de/                          *
    '**************************************************
    Dim RaFound As Range                    ' Variable für Suchergebnis
    ' von Nepumuk Anzahl der ausgewählten Zellen
    If CallByName(Selection, IIf(Val( _
        Application.Version) > 11, "CountLarge", "Count"), VbGet) = 1 Then
        If Target.Column = 1 Then
            With Worksheets("Tabelle2")
                ' Zellinhalt in Tabelle2 suchen
                Set RaFound = .Columns(1).Find(Target, .Range("A1"), _
                    , xlPart, , xlNext)
                If RaFound Is Nothing Then      ' Prüfen ob Wert gefunden
                    ' keine Farbe da Begriff nicht gefunden
                    Target.Offset(0, 1).Interior.ColorIndex = xlNone
                Else
                    ' Farbe und Unterstreichung übernehmen
                    Target.Offset(0, 1).Interior.ColorIndex = _
                        RaFound.Offset(0, 1).Interior.ColorIndex
                    Target.Offset(0, 1).Font.Underline = _
                        RaFound.Offset(0, 1).Font.Underline
                End If
            End With
            Set RaFound = Nothing           ' Variable leeren
        End If
    End If
End Sub

 

[smily44]

Hallo Sirion,
Vielen Dank für diesen wichtigen Hinweis.
Auch wenn ich weder unter Makros noch Visual Basic ein Makro in der xlsm-Mappe sehe, glaube ich dir natürlich.

Für Marko-Ausführung braucht es ja soviel ich weiss eine xlsm-Mappe. Da ich nicht der einzige bin der mit der von mir gestalteten Mappe arbeitet, möchte ich lieber eine Datei im "normalen" Format, also xls oder xlsx weitergeben.

Somit meine Frage - kann ein Makro auch in einer xls, bzw. xlsx-Mappe aktiv sein? Oder ist es so, dass das Dateiformat xlsm nur nötig ist, wenn Makro bearbeitet werden müssen, aber die Ausführung eines Makros auch eine "normales" Exceldateiformat verwendet werden kann?

Nochmals besten Dank - ich bin wenigstens einen Schritt weiter.

Gruss Smily44

 

[sirion]

Hallo Smily44

Mit Alt+F11 öffnest du den Visual Basic Editor. Auf der linken Seite solltest du "VBAProject (farben_uebernehmen.xls)" sehen. Wenn du im Ordner darunter (Ordner "Microsoft Excel Objects") auf "Tabelle1 (Tabelle 1)" doppelklickst, sollte sich das entsprechende Fenster mit dem VBA-Code öffnen, den ich oben eingefügt habe

Bis und mit Office 2003 war die Dateiendung 3-stellig (xls). Solche xls-Mappen können Makros enthalten oder auch "Makro-frei" sein. Wurde die Datei in einer neueren Office-Version gespeichert, wird unterschieden: in "xslx"-Dateien können keine Makros enthalten sein (es können damit auch keine ausgeführt werden). Für Makros sind dann zwingend "xlsm"-Dateien nötig.
Es ist natürlich auch möglich, mit neuen Office-Versionen noch das alte Dateiformat "xls" zu verwenden, allerdings funktionieren dann keine Formeln oder Funktionen, die erst in späteren Office-Versionen neu hinzukamen.

 

[smily44]

Bedingte Formatierung mit VERWEIS

Hallo Zusammen,
Aus Tabellenblatt Teilnehmerliste (siehe angehängte Mappe) generiere ich in einem weiteren Tabellblatt Tischplan eine "halbgrafische" Übersicht, d.h. die Kabinennummer und die Anzahl Gäste der Kabine werden beim entsprechenden Tisch eingefügt. Die Tischordnung wird vom Büro mal vorgegeben, muss aber oft vor Ort optimiert werden. Um nun nicht fälschlicherweise Gäste die zusammen reisen an getrennte Tische zu setzen, werden mithiilfe von Codes in Spalte A die Zellen mit den Kabinennummern (Spalte H) eingefärbt. Im Beispiel sind es zwei Farben, könnte in Zukunft aber auch auf drei Farben erweitert werden (mehr als drei bedingte Formeln sind ja in Excel nicht möglich).

Die ersten Schritte für eine Opitmierung werden mit dem Tabellenblatt Tischplan gemacht, da dort am übersichtlichsten ist wo noch optimiert werden kann. Somit wäre es sehr hilfreich, wenn nicht nur in der Teilnehmerliste sondern auch im Tischplan die Zellen der Kabinennummern der zusammen Reisenden farblich hinterlegt sind - diese dürfen ja nicht an separate Tische getrennt werden.

Am tollsten wäre es natürlich, wenn die Zellfarbe direkt aus Teilnehmer übernommen werden könnte. Aber auch die Lösung via der Hilsspalten T + U im Tischplan wäre sehr hilfreich und elegant, da ja auch diese Hilfsspalten automatisch gefüllt werden. Bitte wenn möglich keine Lösungen mit VBA-Makros, da die Mappe auch auf PCs geladen werden muss, auf denen der Systemadministrator das Speichern solcher Dateien unterbunden hat.

Schon jetzt herzlichen Dank für die Unterstützung und Hilfe!

Gruss Smily44

 

[smily44]

Hallo Sirion,
Vielen Dank für deine Hinweise.

Mit deiner Hilfe konnte ich den VBA-Code öffnen. Aber ich schaffe es trotzdem nicht, diesen so anzupassen, dass er auch in meiner Mappe funktioniert. VBA-Code ist ohnehin eher ungünstig, da ich die Mappe auch auf Geräten speichern muss, für die der zuständige Systemadministrator das Speichern solcher Dateien unterbunden hat.

Ich habe nun versucht das ganze mit Hilfsspalten zu lösen - komme als Laie aber auch nicht weiter.

Ich habe nun einen neuen Thread eröffnet [Mod edit: Beide Threads zusammengeführt] mit einer Bespieildate drinn.

Vielen Dank für deine Unterstützung und Hilfe.
Gruss Smily44

 

[smily44]

Problem gelöst

Von ausserhalb dieses Forums habe ich die Lösung für die tabellenübergreifende, bedingte Formatierung erhalten. Es geht nciht mit SVERWEIS sondern mit INDEX und VERGLEICH. Siehe angehängte Datei, Tabelle Tischplan.

Für Profis mag diese Info trivial sein, aber für viele Anwender mit ähnlichem Problem ev. doch hilfreich.

Beste Grüsse
Smily44