Voorwaardelijke opmaak bij meerdere cellen

[Cader]

Mijn vraag
Ik ben een excel aan het bouwen waarin ik de voorraad van verschillende marketplaces wil controleren t.o.v. de voorraad in ons boekhoudsysteem. Ik wil aan de hand van een groene en oranje kleur direct kunnen zien wat klopt of wat aangepast moet worden. Inmiddels heb ik 2 werkende regels maar ik wil graag de volgende stap maken in het excel.

De werkende regels:
formule (=D2:I881<$C2:C881) --> maak cellen rood --> van toepassing op (=$D$2:$I$881)
formule (=D2:I881>=$C2:C881) --> maak cellen groen --> van toepassing op (=$D$2:$I$881)

Regels die ik nog wil maken maar wat mij niet lukt:
Als D2:I881 hoger is dan $C2:C881 maar $C2:C881 is gelijk aan of lager dan 0 --> maak cellen rood
Als D2:I881 lager is dan $C2:C881 maar verschil met $C2:C881 is niet 0 en verschil tussen D2:I881 en $C2:C881 is minder dan 20% --> maak cellen groen

Ik heb zelf het gevoel dat het misschien komt door het gebruik van de $-tekens maar hier heb ik al flink mee lopen puzzelen.

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Vraag: Als D2:I881 hoger is dan $C2:C881 maar $C2:C881 is gelijk aan of lager dan 0 --> maak cellen rood
Geprobeerde formule: =EN($C2:$C881<=0;$D2:$I881>0) --> maak cellen rood --> van toepassing op (=$D$2:$I$881)

[Teun_2]

Ik vermoed dat je oorspronkelijke formule niet ideaal is.
=D2:I881<$C2:C881 -> geeft een array van true/false, wat minder handig werkt in combinatie met voorwaardelijke opmaak. Het werkt wel, maar is minder 'standaard'

Wat je wellicht wil hebben is de formule
=$D2<$C2 van toepassing op $C$2:$I$881

De dollartekens werken als volgt.
De formule die je typt, is de formule van de linksbovenste cel in je range. In dit geval C2.
De formule trek je dan door naar de andere cellen in de reeks zoals je dat ook zou doen in een normaal excel werkblad. In E4 heb je dan =$D4<$C4 (kolommen vershuiven niet, want vastgezet met $, rijen wel want staan niet vast.

Wellicht helpt bovenstaande ook om je formule voor de andere zaken correct te krijgen.

[Cader]

Bedankt voor je antwoord maar hoe trek je dan een formule door bij een voorwaardelijke opmaak? Als ik in de cellen werk dan trek je hem met de muis naar beneden maar dat werkt niet hier.

Ik heb de formule overigens wel werkend gekregen op een enkele rij maar op de kolommen lukt het mij niet:
=EN($C348:$C374<=0;$D348:$I374>0) van toepassing op =$D$348:$I$374

[dix-neuf]

Cader schreef op donderdag 10 augustus 2023 @ 16:18:
De werkende regels:
formule (=D2:I881<$C2:C881) --> maak cellen rood --> van toepassing op (=$D$2:$I$881)
formule (=D2:I881>=$C2:C881) --> maak cellen groen --> van toepassing op (=$D$2:$I$881)
Regels die ik nog wil maken maar wat mij niet lukt:
Als D2:I881 hoger is dan $C2:C881 maar $C2:C881 is gelijk aan of lager dan 0 --> maak cellen rood
Als D2:I881 lager is dan $C2:C881 maar verschil met $C2:C881 is niet 0 en verschil tussen D2:I881 en $C2:C881 is minder dan 20% --> maak cellen groen. Ik heb zelf het gevoel dat het misschien komt door het gebruik van de $-tekens maar hier heb ik al flink mee lopen puzzelen.

Nee, daar ligt het niet aan. Waar het wél aan ligt: Je hebt eerst ingesteld dat, als D2 <$C2, de cel D2 rood moet worden. Dan doet het er daarna niet meer toe dat je voor rood een extra regel instelt waarbij D2 onder bepaalde voorwaarden groter mag zijn dan$C2, die cel blijft dan rood. Wat je moet doen is de voorwaarden waarbij een cel rood moet worden, combineren. Hetzelfde geldt voor de kleur groen, ook daarvoor moet je de voorwaarden combineren tot 1 regel. Je krijgt dan uiteindelijk slechts 2 regels: 1 voor rood en 1 voor groen, beide regels bestaande uit meer dan1 voorwaarde. Heb je daarbij nog hulp nodig, meld het dan.

[Teun_2]

Cader schreef op vrijdag 11 augustus 2023 @ 08:47:
Bedankt voor je antwoord maar hoe trek je dan een formule door bij een voorwaardelijke opmaak? Als ik in de cellen werk dan trek je hem met de muis naar beneden maar dat werkt niet hier.

Ik heb de formule overigens wel werkend gekregen op een enkele rij maar op de kolommen lukt het mij niet:
=EN($C348:$C374<=0;$D348:$I374>0) van toepassing op =$D$348:$I$374

Het doortrekken is iets dat excel doet. Je schrijft de formule voor de cel linksboven. Als die voor de cel 'waar' geeft, dan wordt de regel voor die cel geactiveerd. Voor de andere cellen in het bereik wordt de formule geëvalueerd alsof ze wordt doorgetrokken.

In jouw voorbeeld zet je daar weer een array-formule. Dat maakt het echt nodeloos complex. Probeer eens geen array in je forumle te gebruiken?

In je eerste scenario: kleur alle cellen in het bereik D2:I881 die een waarden hebben die lager is dan de waarde op dezelfde rij in kolom C, heb je volgende eenvoudige formule:
=D2<$C2 die je toepast op =$D$2:$I$881

Voor cel D2 is het dan D2<C2, voor E2 E2<C2, voor F3 F3<C3 etc.

Je formule schrijf je voor één cel. Je bereik voor meerdere cellen.

[ Voor 28% gewijzigd door Teun_2 op 11-08-2023 13:40 ]

[Cader]

Dag dix-neuf,

Ik heb het getest maar dat is helaas niet de oplossing. Maar het is mij wel gelukt om de formule op een rij toe te passen. Deze formule werkt namelijk wel:
=EN($C348:$C374<=0;$D348:$I374>0) --> dan wordt rood --> van toepassing op =$D$348:$I$374

Maar als ik hem wil toepassen op alle gegevens (dus ook de kolommen) dan werkt die niet meer:
=EN($C2:$C881<=0;$D2:$I881>0) --> dan wordt rood --> van toepassing op =$D$2:$I$881

[dix-neuf]

Cader schreef op vrijdag 11 augustus 2023 @ 13:29:
Dag dix-neuf, ik heb het getest maar dat is helaas niet de oplossing.

Ik weet niet op welke oplossing je doelt, maar ik heb nog geen oplossing gegeven, wel aanwijzingen die tot een correct resultaat leiden. Zie onderstaande instellingen voor vw. opmaak en het Excelvoorbeeld daaronder.
Let er op dat de regels in de volgorde moeten staan zoals je ze in de afbeelding ziet !


De formules zijn helaas niet volledig zichtbaar (dat venster kan ik in mijn Excel niet groter maken), daarom zal ik ze hier vermelden:
Regel 1:

code:

=OF((D2>$C2);EN($C2*0,8<D2;$C2<>0))

en regel 2:

code:

=OF(EN(D2<$C2;D2<>"");EN(D2>$C2;$C2<0;D2<>""))

Zie het voorbeeld hieronder bij toepassing van bovenstaande regels.

'

[dix-neuf]

En toen liet Cader niets meer van zich horen....

[Cader]

Hey @dix-neuf,

Het weekend ging eroverheen en er kwamen wat andere werkzaamheden tussen. Ik heb zojuist jouw regels toegepast en het heeft mij heel erg geholpen.

Ik moest alleen wel de regels omdraaien anders wordt de toepassing niet groen wanneer deze boven 0 was en C<=0.

In ieder geval heel erg bedankt! Ik snap nu ook een stuk beter hoe te werken binnen in de voorwaardelijke opmaak waardoor ik op jouw regels weer verder heb kunnen werken.

[dix-neuf]

Cader schreef op dinsdag 15 augustus 2023 @ 13:30:
Ik moest alleen wel de regels omdraaien anders wordt de toepassing niet groen wanneer deze boven 0 was en C<=0.

Toch wel ! Als je de regels in omgekeerde volgorde plaatst, zullen de regels, zoals je die in je eerste bericht beschreef, niet correct worden toegepast. Maar als jij tevreden bent met je oplossing, dan moet je die natuurlijk zo laten.

[Cader]

dix-neuf schreef op dinsdag 15 augustus 2023 @ 16:07:
[...]

Toch wel ! Als je de regels in omgekeerde volgorde plaatst, zullen de regels, zoals je die in je eerste bericht beschreef, niet correct worden toegepast. Maar als jij tevreden bent met je oplossing, dan moet je die natuurlijk zo laten.

Je hebt gelijk maar ik vindt het iets belangrijker om de producten te zien die ergens op voorraad staan terwijl we die in het magazijn niet meer hebben liggen. Uiteraard ga ik nog wel verder kijken maar voor nu heb je mij al erg goed geholpen waardoor ik het zelf ook beter begrijp.