Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Bedingte Formatierung, Auslesen der Adresse aus einer Zelle

Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 11:49:03
Fenryr
In der
ZELLE 03!B2
befindet sich eine Adresse
URL!BQ8
, diese Adresse soll ausgelesen und angewendet werden.

Mit der Formel :
=ZÄHLENWENN(INDIREKT(B2);"U")
wird nur die erste Zelle des URL! ausgelesen.
Die Variante:
=ZÄHLENWENN(INDIREKT($B$2);"U")
markiert alle Zellen, auch wenn in garkeiner "U" im Sucbereich vorhanden ist
Die Grundformel:
=ZÄHLENWENN(URL!BQ8;"U")>0
markiert alle Zellen mit einem "U" im gewünschten Bereich 03!U10 bis 03!AY109.
Nach etlichen Versuchen habe ich bisher keine Lösung gefunden und hoffe daher das ihr mir helfen könnt.
Das ganze muss ohne VBA funktionieren.

Ich danke im Voraus.
Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 12:31:39
BoskoBiati2
Hi,

ohne die Tabelle ist das nur Rätselei.

=ZÄHLENWENN(URL!BQ8;"U")>0 kann nur dann einen Bereich markieren, wenn in der bedF bei "wird angewendet auf" auch ein entsprechender Bereich steht.

URL!BQ8 ist eine einzelne Zelle. Mit INDIREKT(B2) wird nur diese einzelne Zelle angesprochen.

Also zeige mal, was Du hast und was Du willst.
Anzeige
AW: Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 13:15:30
Fenryr
Sorry ist mir danach eingefallen das zuwenig Informationen vorhanden sind.

Das Blatt URL! ist ein Jahreskalender in den Spalten URL!I5 bis URL!NJ5 befinden sich die Tage(Datum)) .
Das Blatt 03! ist ein Monatskalender in den Spalten 03!08 bis 03!AY8 befinden sich die Tage(Datum) .
Insgesamt gibt es 12 Blätter für jeden Monat eines.
Die Lösung die mit der Formel erreicht werden soll ist die Problematik mit "Normalen und Schaltjahren" zu lösen, da sich die Spalte in URL! verschiebt in der der Monat startet (beginnt natürlich ab März)

Als erstes Vergleiche ich das Datum (Anfang des Monats)
=TEXTKETTE("URL!";WECHSELN(ADRESSE(1;VERGLEICH('03'!A2;URL!I$5:URL!NJ$5;0)+SPALTE(URL!I$5)-1;4);"1";"");"8")

diese Formel gibt mir als Ausgabe folgendes URL!BQ8.

Es gibt auch einen Vergleich des Datums (Ende des Monats)
=TEXTKETTE("URL!";WECHSELN(ADRESSE(1;VERGLEICH('03'!A3;URL!I$5:URL!NJ$5;0)+SPALTE(URL!I$5)-1;4);"1";"");"107")

diese Formel gibt mir als Ausgabe folgendes URL!CU107. (zum einbinden bisher nicht geeignet).

Die Formel soll jetzt den Bereich URL!BQ8 bis URL!CU107 überprüfen ob in den Zellen ein "U" ist, in dem die Adresse aus 03!A2 übernommen wird.
=ZÄHLENWENN(INDIREKT(B2);"U")

Wie du schon genau gesagt hast prüft diese Formel nur die erste Zelle nicht wie vorgesehen den gesamten Bereich.
Die Grundformel:
=ZÄHLENWENN(URL!BQ8;"U")>0

liest den Bereich aus, auch wie gewünscht Zelle für Zelle(sicherlich keine perfekte Lösung, da die Formel nicht weiß wo das Ende ist)

Ich hoffe die Informationen sind besser und hilfreich zum finden einer Lösung.




Anzeige
AW: Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 13:19:07
{Boris}
Hi,

lad doch mal bitte eine kleine Beispieldatei hoch.
Hier hapert es offensichtlich an der korrekten Stringverkettung für den finalen INDIREKT-Bereich.

VG, Boris
AW: Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 13:45:32
Fenryr
Sorry

Die Beispiel Datei folgt : https://www.herber.de/bbs/user/169256.xlsx
Anzeige
Wie vermutet...
05.05.2024 14:54:16
{Boris}
Hi,

...Du hast Dich da etwas verrannt.

1. Lösche Deine bisherige Bedingte Formatierung
2. Selektiere Zelle U10 und vergib dort folgende Bedingte Formatierung (Formel zu Ermittlung...):

=WVERWEIS(U$7;URL!$I$5:$NI$107;ZEILEN($1:2);0)="u"

Und "Wird angewendet auf": =$U$10:$AY$109

Das war es schon.

VG, Boris

Anzeige
AW: Danke für die perfekte Lösung
05.05.2024 15:15:59
Fenryr
=WVERWEIS(U$7;URL!$I$5:$NI$107;ZEILEN($1:2);0)="u" 
minimale Anpassung
=WVERWEIS(U$7;URL!$I$5:$NI$107;ZEILEN($1:4);0)="u" 
und es macht das was es soll.

Vielen Dank
Stimmt...
05.05.2024 15:21:55
{Boris}
Hi,

....hatte nicht gesehen, dass da 3 Zeilen ausgeblendet sind.

Deine SEQUENZ-Formel in U7 lässt sich übrigens auch eindampfen.

=SEQUENZ(;TAG(DATUM(D1;A1+1;0));DATUM(D1;A1;1))

Damit brauchst Du die separaten Datumsberechnungen in A2 und A3 nicht mehr.

VG, Boris
Anzeige
AW: Stimmt...
05.05.2024 15:43:46
Fenryr
Da du sehr gute Lösungen hast.
Vielleicht hast du auch hier die verkürzte Fassung zu dieser SEQUENZE:
=WOCHENTAG(SEQUENZ(1;MONATSENDE(DATUM(D1;MONAT(A2);1);0)-DATUM(D1;MONAT(A2);1)+1;DATUM(D1;MONAT(A2);1)))

mein Versuch scheitert schon im Ansatz
WOCHENTAG(SEQUENZ(1;MONATSENDE(DATUM(D1;A1+1;0);1);0)-DATUM(D1;A1;1);1)+1;DATUM(D1;A1;1);1)))


Diese Abfrage wird benötigt um die Wochentage in Zahlen auszugeben (1 - 7) eine weitere Formel benötigt diese um den Namen in verschiedenen Sprachen auszugeben (funktioniert bestens)



Anzeige
AW: Stimmt...
06.05.2024 10:39:31
{Boris}
Hi,

Monat in A1, Jahr in D1:

=WOCHENTAG(SEQUENZ(;TAG(DATUM(D1;A1+1;0));DATUM(D1;A1;1));2)

Mit dem Parameter ;2 für die Funktion WOCHENTAG fängt die Woche am Montag an - also 1 = Montag, 2 = Dienstag etc.

VG, Boris
Umgekehrte Abfrage keine Bedingte Formatierung
07.05.2024 12:09:14
Fenryr
Danke BoskoBiati2.

=WVERWEIS(U$7;URL!$I$5:$NI$107;ZEILEN($1:4);0)="u"
, sie spart 48 Formeln ein.
diese Formel funktioniert perfekt in der Bedingten Formatierung und hat mir geholfen weitere Formel zu verbessern.
Jetzt zu meiner Frage:
Wie muss die Formel aussehen, wenn ich das ganze umgekhrt machen möchte, aber nicht in der Bedingten Formatierung..
Ziel ist es aus allen 12 Blättern (01 bis 12) den Inhalt in dem Blatt URL! zu übertragen.

In 03!7 steht das Datum (01.03.2023, in der Zelle 03!10 steht ein "U" dieses soll jetzt im Blatt URL! in die Zelle übertragen wird welche genau in der Spalte mit dem identischen Datum liegt Diese Formel muss also das Datum vergleichen um den WERT an die richtige Zelle zu übergeben.

Danke Boris

=WOCHENTAG(SEQUENZ(;TAG(DATUM(D1;A1+1;0));DATUM(D1;A1;1));1) 
(Woche beginnt bei mir am Sonntag)
wie bekomme ich diese Formel auf diese Formel umgesetzt:
=WOCHENTAG(SEQUENZ(1; DATWERT("31.12." & D1) - DATWERT("01.01." & D1) + 1; DATUM(D1; 1; 1)))


Muster Datei: https://www.herber.de/bbs/user/169318.xlsx





Anzeige
AW: Umgekehrte Abfrage keine Bedingte Formatierung
07.05.2024 13:19:42
{Boris}
Hi,

Wie muss die Formel aussehen, wenn ich das ganze umgekehrt machen möchte

URL!I8:
=WENNFEHLER(LET(x;INDIREKT("'"&TEXT(I$5;"MM")&"'!U10:AY109");INDEX(x;ZEILEN($1:1);VERGLEICH(I$5;INDIREKT("'"&TEXT(I$5;"MM")&"'!U7:AY7");0)));"")

und komplett nach rechts und nach unten kopieren.

Die Monatsblätter müssen alle zweitstellig benannt werden: 01, 02, 03 ...bis 12

Zu Deine Zusatzfrage: Wo genau soll die Formel hin?

VG, Boris
Anzeige
AW: Umgekehrte Abfrage keine Bedingte Formatierung
07.05.2024 15:08:00
Fenryr
Danke erstmal Boris
Die Formel funktioniert (war nicht anders zu erwarten)
Allerdings zerlege ich die Formel in alles Mögliche, weil ich das
ISTLEER
nicht richtig einfüge.

Die andere Formel befindet sich eigentlich auf Blatt URL! Zeile 9 und beginnt in Spalte U.

Anzeige
Was für ein ISTLEER?
07.05.2024 15:36:56
{Boris}
Hi,

...wolltest Du mir noch irgendwas zeigen?

VG, Boris
AW: Was für ein ISTLEER?
07.05.2024 17:34:54
Fenryr
Sorry da ist wohl etwas Text nicht geschrieben wurden , aber im Gedanken war dieser.

Also die Formel von dir:
=WENNFEHLER(LET(x;INDIREKT("'"&TEXT(I$5;"MM")&"'!U10:AY109");INDEX(x;ZEILEN($1:1);VERGLEICH(I$5;INDIREKT("'"&TEXT(I$5;"MM")&"'!U7:AY7");0)));"") 

funktioniert, ist kein Wert vorhanden wird eine "0" angezeigt.
ISTLEER

sollte die "0" entfernen, also kein WERT ergibt leeres Feld.
Bei allen Versuchen bin ich bisher gescheitert.

=WOCHENTAG(SEQUENZ(;TAG(DATUM(D1;A1+1;0));DATUM(D1;A1;1));1)

(Woche beginnt bei mir am Sonntag)
wie bekomme ich diese Formel auf diese Formel umgesetzt ( Diese befindet sich in 03!U9):

=WOCHENTAG(SEQUENZ(1; DATWERT("31.12." & D1) - DATWERT("01.01." & D1) + 1; DATUM(D1; 1; 1)))


Die Datei (eingekürzt, da sie sonst zu groß ist) dazu : https://www.herber.de/bbs/user/169336.xlsx

Anzeige
AW: Was für ein ISTLEER?
07.05.2024 18:09:03
{Boris}
Hi,

die Nullwerte blendet man über das Zahlenformat aus:

Standard;;

wie bekomme ich diese Formel auf diese Formel umgesetzt ( Diese befindet sich in 03!U9):
=WOCHENTAG(SEQUENZ(1; DATWERT("31.12." & D1) - DATWERT("01.01." & D1) + 1; DATUM(D1; 1; 1)))


Deine Formel erzeugt eine wiederkehrende Zahlenfolge von 1 bis 7 - weshalb soll das genau so sein? Z.B. ist der 1.3. ein Mittwoch - wenn Deine Woche doch mit 1 (am Sonntag) beginnt, dann müsste das Ergebnis doch für diesen Tag 4 sein.
Was genau habe ich nicht verstanden?

VG; Boris
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige