excel conditional formatting meerdere texten

[erikkallenberg]

Ik wil graag dat

in kolom D
Alle cellen die een E en een B bevatten een kleur geven.

De voorwaarde is:
Wanneer in kolom F een E of een B staat, dan moeten alle cellen met een E of B in kolom D een rode kleur staan.

Wanneer in kolom F(44,45 of 46) een C en B komen te staan wil ik alle cellen met een C of B gekleurd hebben in kolom D. (in kolom F44,45,46 staan e,e,b)




Dit probeer ik middels conditional formatting text. Hierbij krijg ik echter de volgende foutmelding wanneer ik meerdere cellen tegelijk selecteer. Zie de jpg.


Vraag
Welke formule kan ik gebruiken om de cellen in kolom D een kleur te geven aan de hand van de tekst die in kolom D staat?

Ik heb veel via youtube en google gezocht, maar kom er niet uit.
bijv dit geprobeerd (https://www.extendoffice....h-for-multiple-words.html) :



voor als de tekst niet goed leesbaar is:
=SUM(COUNTIF(A2;"*"&mylist&"*"))

mylist is in dit geval F44:F:46 --> Ik zou denken dat in rij a alle letters met een B bijvoorbeeld rood moeten worden.

[ Voor 20% gewijzigd door erikkallenberg op 26-07-2018 18:51 ]

[Lustucru]

Schrijf de regel eens precies uit? Zoals je het nu omschrijft is het voor meerdere interpretaties vatbaar. De meest simpele variant die ik erin lees is dat als een de waarde van een cel in kolom D voorkomt in kolom F dat je dan de cel in D gekleurd wilt hebben?

[erikkallenberg]

Lustucru schreef op donderdag 26 juli 2018 @ 19:27:
Schrijf de regel eens precies uit? Zoals je het nu omschrijft is het voor meerdere interpretaties vatbaar. De meest simpele variant die ik erin lees is dat als een de waarde van een cel in kolom D voorkomt in kolom F dat je dan de cel in D gekleurd wilt hebben?

Klopt.

Wanneer de waarde in kolom D overeenkomt met de waarde in kolom F wil ik dat die specifieke cel in D gekleurd wordt.

[Lustucru]

Dan wordt het iets als:

code:

=NIET(ISFOUT(VERGELIJKEN(D1;$F$1:$F$4)))

of (komt op hetzelfde neer)

code:

=AANTAL.ALS($F$1:$F$4;D1)>0

[erikkallenberg]

Lustucru schreef op donderdag 26 juli 2018 @ 19:54:
Dan wordt het iets als:

code:

=NIET(ISFOUT(VERGELIJKEN(D1;$F$1:$F$4)))

of (komt op hetzelfde neer)

code:

=AANTAL.ALS($F$1:$F$4;D1)>0

Ik heb dit geprobeerd:

1. conditional formatting
2. new rule
3. use a formula to determine which cells to formate
4.

code:

=COUNT.IF($F$1:$F$4;D1)>0

5. Bij format een kleur selecteren
6. Ok



Helaas worden de cellen in rij D niet gekleurd

Wat doe ik verkeerd?

Toevoeging:

Wanneer
in kolom F bijvoorbeeld aap staat
in kolom D bijvoorbeeld boordje aap staat in cell D1
In kolom D bijvoorbeeld broodje gehakt staat in cell D2

Wil ik dat cell D1 gekleurd is

[ Voor 15% gewijzigd door erikkallenberg op 26-07-2018 21:40 ]

[Boeryepes]

Is het al opgelost? Ik persoonlijk vind het 'moeilijk' om conditional formatting met ietwat ingewikkelde formules te doen omdat deze lastig te testen zijn. Mijn aanpak is daarom:
1. Als 'ingewikkeld' nodig is, zet ik deze in een hulpkolom die True of False oplevert en link de conditional formatting formule aan deze cell
2. Als niet zo ingewikkeld dan wel als formule in de conditional formatting maar dan schrijf ik deze altijd met de functies AND en OR zodat evt. combinaties elk zijn eigen check heeft wat beter onderhoudbaar is (vind ik)

In jouw geval is het niet ingewikkeld. Jouw combinatie van sum en countif werkt volgens mij niet. Mijn formule zou zijn (regel 1): =AND(MATCH (D1, $F$1:$F$4,0)>0)). Let er wel op dat de 'Applies to' range en de D1 uit de formule met elkaar corresponderen want Excel vertaald dit regelmatig verkeerd (althans bij mij -> ff handmatig corrigeren)

Match is sneller dan countif en sneller dan lookup).

Succes!

[Lustucru]

erikkallenberg schreef op donderdag 26 juli 2018 @ 21:32:
Wanneer
in kolom F bijvoorbeeld aap staat
in kolom D bijvoorbeeld boordje aap staat in cell D1
In kolom D bijvoorbeeld broodje gehakt staat in cell D2

Wil ik dat cell D1 gekleurd is

Dat is dus iets heel anders dan je oorspronkelijke voorbeeld. Je wilt weten of een van de trefwoorden uit de range F1:F4 voorkomt in cel D1. Als het idd maar enkele trefwoorden betreft kun je voor elke trefwoord dmv vind.spec nagana of het voorkomt en die resultaten bij elkaar optellen

code:

=(vind.spec($F$1;D1)+vind.spec($F$2;D1) +[...]+vind.spec($F$n;D1))>0

of voor een aparte regel aanmaken voor elke cel in je trefwoordenlijstje.

Zijn het er meer dan moet je naar een matrixformule die je in een hulpkolom zet. Die vraag is eerder beantwoord: Lustucru in "Spreadsheet: deel van tekst zoeken in lijst"

[ Voor 7% gewijzigd door Lustucru op 27-07-2018 16:03 ]

[dix-neuf]

Boeryepes schreef op vrijdag 27 juli 2018 @ 13:51:
In jouw geval is het niet ingewikkeld...Mijn formule zou zijn (regel 1): =AND(MATCH (D1, $F$1:$F$4,0)>0)).

@Boeryepes: hoewel volgens jou niet ingewikkeld, is je formule niet juist.
a. Hoewel niet altijd een fout opleverend, kun je spaties in formules best achterwege laten;
b. Je gebruikt slechts 1 functie, dus AND hoort er niet in thuis;
c. Ook als je AND eruit haalt, dan nog geeft je formule een fout resultaat.

@erikkallenberg,
op basis van je laatste bericht hierboven, waarin je deze gegevens gebruikt: https://image.ibb.co/nwJxuT/8.jpg , kun je onderstaande oplossing gebruiken (ik gebruik hierbij evenals Lustucru de functie Search (vind.spec) maar in combinatie met een tweede functie).

1. Verwijder alle ingestelde regels m.b.t. conditonal formatting (althans voor zover ze betrekking hebben op het bereik waarom het hier gaat);
2. Selecteer D1:D20 (of verder dan rij 20 als je meer cellen in kolom D gebruikt).
3. Kies in het menu (terwijl D1:D20 geselecteerd staat): "Conditional formatting --> New Rule" --> "Use a formula to ...").
4. Voer in als eerste regel: =SEARCH(F1;D$1:D$20) . Klik op "Format" ("Opmaak) en kies een opvulkleur. Klik op Ok en nogmaals op Ok.
5. Zorg dat D1:D20 geselecteerd blijft en kies wederom in het menu: "Conditional formatting --> New Rule" --> "Use a formula to ...", en voer in als tweede regel: =AND(D1=0;F1=0) . Geef deze regel geen opmaak mee (geen celkleur of iets anders), klik op Ok en nogmaals op Ok.
6. Normaliter moet nu het venster met de naam "Conditional formatting Rules manager" verschijnen, maar Excel wil dit wel eens overslaan. Als het niet verschijnt of verdwenen is, kies dan in het menu: "Conditional Formatting" --> "Manage Rules". Controleer of wat hierboven onder de punten 4 en 5 staat vermeld, in het 'Manage'-venster als eerste en tweede regel staan ingesteld (de volgorde heeft belang, met een pijltje bovenaan kun je die wijzigen indien nodig). Vergeet vervolgens niet om bij beide regels "Stop if true" aan te vinken. Klik daarna op "Toepassen" en "Ok".
Opm:
Het is niet nodig dat er in alle cellen van het gekozen bereik in de kolommen D en F tekst staat. Zijn de cellen leeg, dan krijgen ze ook geen opmaak.

[ Voor 3% gewijzigd door dix-neuf op 27-07-2018 15:49 ]

[Boeryepes]

dix-neuf schreef op vrijdag 27 juli 2018 @ 15:24:
[...]

@Boeryepes: hoewel volgens jou niet ingewikkeld, is je formule niet juist.
a. Hoewel niet altijd een fout opleverend, kun je spaties in formules best achterwege laten;
b. Je gebruikt slechts 1 functie, dus AND hoort er niet in thuis;
c. Ook als je AND eruit haalt, dan nog geeft je formule een fout resultaat.

Kijk even goed naar het screenshot onder deze link welke bovenstaande werkend toont.
https://1drv.ms/u/s!AtEPqOxH-AH9gv0trTOi0iJnPwzLAQ

Om toch even te reageren op je a, b en c
a. mijn advies via iphone is pseudo code, iedereen die met Excel formules werkt, weet hoe het zit met spaties
b. zoals ik aangaf werk ik altijd met AND en OR, dat hoef je niet te volgen
c. zie screenshot onder de link

Success!

[dix-neuf]

Voordat jij je eerste reactie plaatste, bleek uit een bericht van TS dat zijn probleem gewijzigd was in vergelijking met zijn eerste bericht, dat moet je hebben gezien. Het ligt dan voor de hand dat je op dat laatste probleem ingaat, voor het eerste had immers Lustucru al twee oplossingen gegeven (met o.a. 'Match'). Jouw 'oplossing' werkt niet voor dat laatste probleem, waarvan akte.

[Boeryepes]

dix-neuf schreef op vrijdag 27 juli 2018 @ 17:26:
Voordat jij je eerste reactie plaatste, bleek uit een bericht van TS dat zijn probleem gewijzigd was in vergelijking met zijn eerste bericht, dat moet je hebben gezien. Het ligt dan voor de hand dat je op dat laatste probleem ingaat, voor het eerste had immers Lustucru al twee oplossingen gegeven (met o.a. 'Match'). Jouw 'oplossing' werkt niet voor dat laatste probleem, waarvan akte.

Touché 😉, was er niet alert op dat wijzigingen niet in de openingspost kunnen staan.

Vervang de match Formule door deze matrix formule om op keywords uit kolom F te zoeken: OR(IFERROR(FIND($F$1:$F$4<>"",$F$1:$F$4)>0), FALSE)) // op iphone getikt ...

Ik zou deze (eerst) in een hulp kolom zetten om de logica te snappen en te testen en eventueeo daarna naar conditional formatting verhuizen.

Hopelijk niet nog iets gemist.

[Lustucru]

Boeryepes schreef op vrijdag 27 juli 2018 @ 18:06:


Hopelijk niet nog iets gemist.

Ja dus. Lustucru in "excel conditional formatting meerdere texten"

zoals ik aangaf werk ik altijd met AND en OR, dat hoef je niet te volgen
[...]
Vervang de match Formule door deze matrix formule om op keywords uit kolom F te zoeken: OR(IFERROR(FIND($F$1:$F$4<>"",$F$1:$F$4)>0), FALSE))

Dat jij het altijd doet is nog reden om anderen een foute gewoonte aan te leren. 'x or false' is gewoon gelijk aan 'x'. Daarnaast werkt je functie niet. Ik weet niet wat je doet, maar je zoekt of het een fout oplevert als je een stapeltje cellen vergelijkt met "" en het resultaat vergelijkt in dezelfde stapel die je vergelijkt met 0. Het zal de warmte wel zijn.

[ Voor 19% gewijzigd door Lustucru op 27-07-2018 18:37 ]