Voorwaardelijke opmaak. Welke functies combineren en hoe?

[Anoniem: 941809]

Het voorwaardelijk opmaken van cellen in een excelsheet. (Excel 2016)

Wanneer er een nieuwe aanvraag binnenkomt, wordt de klant om de deadline gevraagd.
We zijn de fase al voorbij dat er dan "gisteren" wordt geroepen

Afhankelijk van de week die de klant noemt, vullen we in het kolom Deadline het weeknummer in, gevolgd door een punt, gevolgd door het eerst opvolgende getal die nog niet gebruikt is bij die week.
(Kijkend naar onderstaand voorbeeld zou dat voor week 31 dan worden: 31.04)

Afhankelijk van het verzoek weet de medewerker hoeveel uur nodig is om het af te handelen.
Wanneer de nieuwste rij ertoe leid dat "Tijd nodig" in een bepaalde week, het aantal "Beschikbare uren" overschrijdt, moet die rij rood opgevuld worden.
Zo weet de medewerker direct dat het verzoek pas gedaan kan worden in een latere week.


Voorbeeld sheet 1
Rij Klantnaam Tijd nodig Deadline
135 Klant_A 4 31.01
136 Klant_B 6 35.01
137 Klant_X 8 31.02
138 Klant_R 4 32.01
139 Klant_D 2 31.03
140 Klant_A 4 32.02


Voorbeeld sheet 2
Week Beschikbare uren
31 16
32 32
33 32
34 24
35 16

Hoe realiseer ik dat met functies en welke?
...

Relevante software en hardware die ik gebruik:
Excel 2016

[F_J_K]

Welkom!

Er zijn hier op het forum en elders op het web al vaker dergelijke oplossingen besproken, waar loop je vast? Je lijkt nu alles over de muur te gooien zonder zelf formules / structuren te bedenken

De opzet van weeknr.volgnr is m.i. onhandig (liever twee losse velden), maar vooruit. Heel kort samengevat (want ik ga weer aan het werk) hoe ik het zou overwegen:
Met vert.zoeken of som-als en deel() voor de berekening zou je een eind moeten komen icm hulpkolom voor totaal per week en zonodig een hulpkolom voor de weeknummers. Als je alleen de laatste rij rood wilt zien worden, zal je steeds in sheet1 in een hulpkolom de totalen tot dan moeten berekenen en de eigen uren er bij optellen. Als alle rijden rood mogen dan wordt het een simpele som.als.

[loeberce]

Maar let op: voorwaardelijke opmaak werkt niet met verwijzigingen naar andere tabbladen. Dus of referentie-hulp-kolommen maken in Sheet1 die verwijzen naar het andere tabblad, of de bereiken op Sheet2 een naam geven.

vert.zoeken werkt niet goed als de rijen niet logisch zijn geordend. INDEX(VERGELIJKEN) of INDEX(MATCH) in Engelse Excel werkt beter.

Formule hulpkolom voor optellen tijd nodig per week:
=SUMPRODUCT((Sheet1!$C$2:$C$7)*(VALUE(LEFT(Sheet1!$D$2:$D$7;2))=A2))
Deze staat in kolom C op Sheet2. En maakt een array (formule na bewerken afsluiten met CTRL-SHIFT-ENTER).

Formule om tijd nodig per regel te toetsen aan totaal.
=IF(INDEX(Sheet2!$C$2:$C$6;MATCH(VALUE(LEFT(D7;2));Sheet2!$A$2:$A$6;0))<INDEX(Sheet2!$B$2:$B$6;MATCH(VALUE(LEFT(D7;2));Sheet2!$A$2:$A$6;0));TRUE;FALSE)

Ik heb dus de weeknrs niet uitgesplitst. Als je dit wel doet kunnen alle VALUE(LEFT() formules eruit. Voorwaarde is wel dat alle weeknummers (dus vooral 1-9) uit 2 cijfers bestaan.

[ Voor 47% gewijzigd door loeberce op 05-07-2017 09:28 ]

[Anoniem: 941809]

Hai F_J_K,
Dank je!

Heb pogingen gedaan met LINKS, SOMMEN.ALS, ALS, maar kreeg tot nu toe het optellen van alleen die rijen met hetzelfde weeknummer niet voor elkaar. Daarna ook nog combineren en afzetten tegen het aantal uren dat beschikbaar was die week.
Uitdagend werd het dat er gewoon rijen tussen gevoegd kunnen worden. Ook dan moet het werken.
Nu in soort van tunnelvisie en plaatsen topic om daaruit te komen.

Zal extra kolom t.b.v. splitsen week/volgnummer gaan uitproberen.

[Anoniem: 941809]

Ga ik mee aan de slag loeberce.
Wordt vervolgd.
Dank zover!

[Anoniem: 941809]

Werkt! Nog wat moeten filteren in het planningssheet, maar werkt geweldig zo. Dank F_J_K en loeberce!
Kan gesloten worden.