Cellen in blad A markeren gebaseerd op gegevens uit blad B

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
Blad A (voorjaar) is een dynamisch werkblad waarin ik de dagen automatisch gemarkeerd wil hebben waarop een persoon op blad B (personeel) gemarkeerd staat als vrij (V).

Afbeeldingslocatie: https://tweakers.net/i/iRKIVk5SfRLmaTtl9SzktE1nYhM=/800x/filters:strip_exif()/f/image/NDLZIJnh78y9Owd4UKscyFU1.webp?f=fotoalbum_large...

Ik wil hiervoor graag "Voorwaardelijke opmaak" gebruiken met de optie "Een formule gebruiken", maar in dit geval helpt Google mij niet erg op weg.
In het geval dat de opmaak niet gebaseerd kan worden op gegevens uit een secundair werkblad, dacht ik dat het misschien ook een optie is om de personeelplanning onderaan in blad A te zetten en van daaruit te verwijzen. https://1drv.ms/x/s!Amu33WYWGGFgbed5HGz7LXkYf4U?e=JuWY2k

Beste antwoord (via mboonstra_jb op 20-11-2024 15:34)


  • Reptile209
  • Registratie: Juni 2001
  • Laatst online: 01:16

Reptile209

- gers -

mboonstra_jb schreef op donderdag 31 oktober 2024 @ 12:25:
[...]


Ik heb een poging gedaan maar:
=EN(Voorjaar!$E47=$D7;K7="v") op bereik =$I$7:$FB$64 in tabblad "Personeel" geeft geen resultaat.
=EN(Personeel!$E47=$D7;FB7="v") op bereik =$F$46:$EY$220 in tabblad "Voorjaar" ook geen resultaat.

Doelbestand
Voorjaar: Kolom met naam werknemer Kolom E (Rijen 47 t/m 999)
Kolommen met planbare dagen F t/m EY (Rijen 47 t/m 999)

Bronbestand
Personeel: Kolom met naam werknemer Kolom D (Rijen 7 t/m 999)
Kolommen met geplande dagen I t/m FB (Rijen 7 t/m 999)
Doe eens wat ik hier voorstelde: Reptile209 in "Cellen in blad A markeren gebaseerd op gegevens uit blad B" Dat helpt je te troubleshooten waar het mis gaat.

Zo scherp als een voetbal!

Alle reacties


Acties:
  • 0 Henk 'm!

  • dixet
  • Registratie: Februari 2010
  • Laatst online: 18:30
Waar loop je precies tegen aan en wat heb je al geprobeerd?
Verwijzingen naar andere tabbladen kunnen gewoon in de formule van de voorwaardelijke opmaak gebruikt worden.

De link in je bericht gaat denk ik naar de betreffende sheet? Niet veel mensen in dit forum zullen niet zomaar willekeurige links openen van onbekende bron.

Acties:
  • 0 Henk 'm!

  • dix-neuf
  • Registratie: Juli 2018
  • Niet online
Zie op ditzelfde forum dit topic: klik.

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
dix-neuf schreef op dinsdag 29 oktober 2024 @ 09:24:
Zie op ditzelfde forum dit topic: klik.
Deze methode gebruik ik al over de hele linie, wel bedankt voor de tip.

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
dixet schreef op dinsdag 29 oktober 2024 @ 09:22:
Waar loop je precies tegen aan en wat heb je al geprobeerd?
Verwijzingen naar andere tabbladen kunnen gewoon in de formule van de voorwaardelijke opmaak gebruikt worden.

De link in je bericht gaat denk ik naar de betreffende sheet? Niet veel mensen in dit forum zullen niet zomaar willekeurige links openen van onbekende bron.
Ik heb eerlijk gezegd geen idee hoe ik deze formule zou moeten formuleren:
ALS op werkblad "Voorjaar" de naam van een persoon in kolom E wordt genoemd, moet de opmaak van deze rij in corresponderende kolom range O tm EY worden veranderd als deze waarde "V" bevat in werkblad "Personeel".

De link in mijn bericht betreft inderdaad een Voorbeeld template, als er een andere manier van delen is die hier acceptabel is hoor ik dat graag :$

[ Voor 1% gewijzigd door mboonstra_jb op 29-10-2024 10:16 . Reden: fautje ]


Acties:
  • 0 Henk 'm!

  • dix-neuf
  • Registratie: Juli 2018
  • Niet online
mboonstra_jb schreef op dinsdag 29 oktober 2024 @ 10:00:
Deze methode gebruik ik al over de hele linie, wel bedankt voor de tip.
Als je die gebruikt en je krijgt niet de gewenste vw. opmaak, dan klopt er iets niet aan je formule(s) voor de vw. opmaak. Maar die formule(s) laat je niet zien, daarom valt daar niets van te zeggen.

Acties:
  • 0 Henk 'm!

  • Reptile209
  • Registratie: Juni 2001
  • Laatst online: 01:16

Reptile209

- gers -

mboonstra_jb schreef op dinsdag 29 oktober 2024 @ 10:07:
[...]


Ik heb eerlijk gezegd geen idee hoe ik deze formule zou moeten formuleren:
ALS op werkblad "Voorjaar" de naam van een persoon in kolom E wordt genoemd, moet de opmaak van deze rij in corresponderende kolom range O tm EY worden veranderd als deze waarde "V" bevat in werkblad "Personeel".

De link in mijn bericht betreft inderdaad een Voorbeeld template, als er een andere manier van delen is die hier acceptabel is hoor ik dat graag :$
Tip: zet je formule tijdelijk op in een cel op je werkblad. De uitkomst moet true/false of waar/onwaar zijn. Dat is veel makkelijker troubleshooten. Als de formule naar wens is (let op welke cel verwijzingen wel of geen $ nodig hebben in het adres!), dan kopieer je hem naar de voorwaardelijke opmaak.

Zo scherp als een voetbal!


Acties:
  • 0 Henk 'm!

  • dix-neuf
  • Registratie: Juli 2018
  • Niet online
Waarom de regels voor vw. opmaak bij 7 januari zouden moeten beginnen en niet bij 1 of 2 januari maak je niet duidelijk. Evenmin is duidelijk voor welk aantal rijen de opmaak moet gelden. Daarom geef ik hier als voorbeeld een zelfbedacht aantal datums (van 1 t/m 17 januari 2025) en rijen (20 rijen).
Selecteer in het blad 'Personeel' K7:W27 en stel in hetzelfde blad in als vw. opmaak:
code:
1
=en(Voorjaar!$E46=$D7;K7="v")


Wijzig W27 in het werkelijk aantal gebruikte rijen en kolommen.

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
dix-neuf schreef op dinsdag 29 oktober 2024 @ 10:49:
[...]

Als je die gebruikt en je krijgt niet de gewenste vw. opmaak, dan klopt er iets niet aan je formule(s) voor de vw. opmaak. Maar die formule(s) laat je niet zien, daarom valt daar niets van te zeggen.
Dat klopt inderdaad, ik zal vandaag toch even de tutorial doorlopen en kijken of ik iets kan produceren wat er op lijkt. Bedankt voor het meedenken!

Acties:
  • 0 Henk 'm!

  • dix-neuf
  • Registratie: Juli 2018
  • Niet online
mboonstra_jb schreef op woensdag 30 oktober 2024 @ 08:46:
ik zal vandaag toch even de tutorial doorlopen en kijken of ik iets kan produceren wat er op lijkt.
Maar je kunt de vw. opmaak die ik in mijn vorig bericht vermeldde voor het bereik K7:W27 toch al testen? Als die blijkt te voldoen, moet je in die formule alleen het bereik (de cellen waarvoor de vw. opmaak moet gelden) nog slechts aanpassen.

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
dix-neuf schreef op dinsdag 29 oktober 2024 @ 13:03:
Waarom de regels voor vw. opmaak bij 7 januari zouden moeten beginnen en niet bij 1 of 2 januari maak je niet duidelijk. Evenmin is duidelijk voor welk aantal rijen de opmaak moet gelden. Daarom geef ik hier als voorbeeld een zelfbedacht aantal datums (van 1 t/m 17 januari 2025) en rijen (20 rijen).
Selecteer in het blad 'Personeel' K7:W27 en stel in hetzelfde blad in als vw. opmaak:
code:
1
=en(Voorjaar!$E46=$D7;K7="v")


Wijzig W27 in het werkelijk aantal gebruikte rijen en kolommen.
Ik heb een poging gedaan maar:
=EN(Voorjaar!$E47=$D7;K7="v") op bereik =$I$7:$FB$64 in tabblad "Personeel" geeft geen resultaat.
=EN(Personeel!$E47=$D7;FB7="v") op bereik =$F$46:$EY$220 in tabblad "Voorjaar" ook geen resultaat.

Doelbestand
Voorjaar: Kolom met naam werknemer Kolom E (Rijen 47 t/m 999)
Kolommen met planbare dagen F t/m EY (Rijen 47 t/m 999)

Bronbestand
Personeel: Kolom met naam werknemer Kolom D (Rijen 7 t/m 999)
Kolommen met geplande dagen I t/m FB (Rijen 7 t/m 999)

Acties:
  • Beste antwoord ✓
  • 0 Henk 'm!

  • Reptile209
  • Registratie: Juni 2001
  • Laatst online: 01:16

Reptile209

- gers -

mboonstra_jb schreef op donderdag 31 oktober 2024 @ 12:25:
[...]


Ik heb een poging gedaan maar:
=EN(Voorjaar!$E47=$D7;K7="v") op bereik =$I$7:$FB$64 in tabblad "Personeel" geeft geen resultaat.
=EN(Personeel!$E47=$D7;FB7="v") op bereik =$F$46:$EY$220 in tabblad "Voorjaar" ook geen resultaat.

Doelbestand
Voorjaar: Kolom met naam werknemer Kolom E (Rijen 47 t/m 999)
Kolommen met planbare dagen F t/m EY (Rijen 47 t/m 999)

Bronbestand
Personeel: Kolom met naam werknemer Kolom D (Rijen 7 t/m 999)
Kolommen met geplande dagen I t/m FB (Rijen 7 t/m 999)
Doe eens wat ik hier voorstelde: Reptile209 in "Cellen in blad A markeren gebaseerd op gegevens uit blad B" Dat helpt je te troubleshooten waar het mis gaat.

Zo scherp als een voetbal!


Acties:
  • +1 Henk 'm!

  • dix-neuf
  • Registratie: Juli 2018
  • Niet online
mboonstra_jb schreef op donderdag 31 oktober 2024 @ 12:25:
Ik heb een poging gedaan maar:
=EN(Voorjaar!$E47=$D7;K7="v") op bereik =$I$7:$FB$64 in tabblad "Personeel" geeft geen resultaat.
=EN(Personeel!$E47=$D7;FB7="v") op bereik =$F$46:$EY$220 in tabblad "Voorjaar" ook geen resultaat
Natuurlijk geeft dat niet het gewenste resultaat, je hebt ook niet uitgevoerd wat ik schreef !
De juiste volgorde mboonstra_jb, is deze: Als iemand een oplossing geeft (of denkt te geven), dan controleer je eerst of die oplossing tot een goed resultaat leidt en als dat zo is, dan (pas dán) pas je die oplossing aan aan je eigen situatie.
Omdat je in je eerste bericht niet volledige duidelijkheid gaf van zaken op welke rijen en kolommen de vw. opmaak van toepassing moest zijn, gaf ik een oplossing voor een bereik dat ik zelf bedacht (citaat uit mijn bericht van 29/10/24, 13.03 uur: "Daarom geef ik hier als voorbeeld een zelfbedacht aantal datums (van 1 t/m 17 januari 2025) en rijen (20 rijen)." (einde citaat). In het door mij in dat bericht vermelde bereik geeft mijn antwoord van 29/10/24, 13.03 uur het juiste resultaat !
mboonstra_jb schreef op donderdag 31 oktober 2024 @ 12:25:
Doelbestand
Voorjaar: Kolom met naam werknemer Kolom E (Rijen 47 t/m 999)
Kolommen met planbare dagen F t/m EY (Rijen 47 t/m 999)
Bronbestand
Personeel: Kolom met naam werknemer Kolom D (Rijen 7 t/m 999)
Kolommen met geplande dagen I t/m FB (Rijen 7 t/m 999)
Nu de vw. opmaak moet gelden voor andere rijen en kolommen dan op grond van je gegevens in je 1e bericht verondersteld kon worden, kun je een correcte oplossing bereiken als volgt:
1. Selecteer het volledige blad 'Personeel' en verwijder alle vw. opmaak voor welk bereik dan ook.
2. Selecteer in het blad 'Personeel' het bereik i7:FB999.
3. Kies in het menu van Excel: "Voorw. opmaak"- 'Nieuwe regel", klik op "Een formule gebruiken om...." en voer in als formule:
code:
1
=en(Voorjaar!$E47=$D7;$D7<>"";i7="v")

4. Klik in hetzelfde venster op 'Opmaak', kies een opvulkleur, klik op 'Ok' en in het onderliggende venster nogmaals op 'Ok'.
Opm1: In vergelijking met de vorige formule heb ik een extra voorwaarde toegevoegd voor het geval in de kolom met namen lege cellen zouden voorkomen en je (al dan niet per ongeluk) in zo'n rij zonder naam toch ergens een v zou hebben ingevuld.
Opm2: Je beseft hoop ik wel dat voor een correcte werking van de vw. opmaak, de namen in de bladen 'Voorjaar' en 'Personeel' (vanaf resp. E47 en D7) in dezelfde volgorde moeten staan.
Succes ermee !

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
dix-neuf schreef op donderdag 31 oktober 2024 @ 20:18:
[...]

Natuurlijk geeft dat niet het gewenste resultaat, je hebt ook niet uitgevoerd wat ik schreef !
De juiste volgorde mboonstra_jb, is deze: Als iemand een oplossing geeft (of denkt te geven), dan controleer je eerst of die oplossing tot een goed resultaat leidt en als dat zo is, dan (pas dán) pas je die oplossing aan aan je eigen situatie.
Omdat je in je eerste bericht niet volledige duidelijkheid gaf van zaken op welke rijen en kolommen de vw. opmaak van toepassing moest zijn, gaf ik een oplossing voor een bereik dat ik zelf bedacht (citaat uit mijn bericht van 29/10/24, 13.03 uur: "Daarom geef ik hier als voorbeeld een zelfbedacht aantal datums (van 1 t/m 17 januari 2025) en rijen (20 rijen)." (einde citaat). In het door mij in dat bericht vermelde bereik geeft mijn antwoord van 29/10/24, 13.03 uur het juiste resultaat !

[...]

Nu de vw. opmaak moet gelden voor andere rijen en kolommen dan op grond van je gegevens in je 1e bericht verondersteld kon worden, kun je een correcte oplossing bereiken als volgt:
1. Selecteer het volledige blad 'Personeel' en verwijder alle vw. opmaak voor welk bereik dan ook.
2. Selecteer in het blad 'Personeel' het bereik i7:FB999.
3. Kies in het menu van Excel: "Voorw. opmaak"- 'Nieuwe regel", klik op "Een formule gebruiken om...." en voer in als formule:
code:
1
=en(Voorjaar!$E47=$D7;$D7<>"";i7="v")

4. Klik in hetzelfde venster op 'Opmaak', kies een opvulkleur, klik op 'Ok' en in het onderliggende venster nogmaals op 'Ok'.
Opm1: In vergelijking met de vorige formule heb ik een extra voorwaarde toegevoegd voor het geval in de kolom met namen lege cellen zouden voorkomen en je (al dan niet per ongeluk) in zo'n rij zonder naam toch ergens een v zou hebben ingevuld.
Opm2: Je beseft hoop ik wel dat voor een correcte werking van de vw. opmaak, de namen in de bladen 'Voorjaar' en 'Personeel' (vanaf resp. E47 en D7) in dezelfde volgorde moeten staan.
Succes ermee !
toon volledige bericht
Excuses, ik heb inderdaad de neiging om drie stappen vooruit te denken en handelen.
Ik zal mijn best doen om hier op te letten!

Hartelijk dank voor de heldere uitleg! Deze formule doet inderdaad exact waarvoor hij geschreven is!

Enige nadeel; de gegevens uit het tabblad "Personeel" zijn statisch en de gegevens uit het tabblad "Voorjaar" dynamisch. Oftewel, de naam van het geplande personeelslid kan per periode op een andere plaats in het werkblad "Voorjaar" komen te staan.
Ik zal zelf ondertussen proberen te kijken of een formule met bijvoorbeeld verticaal zoeken of een ALS functie de oplossing kan bieden.

Daarnaast wilde ik graag de markering juist op tabblad "Voorjaar" terug zien wanneer er een 'v' op tabblad "Personeel" voor komt. Dit heb ik opgelost door in vw. opmaak in werkblad "Voorjaar" de volgende formule in te gebruiken:
code:
1
=EN($E47=Personeel!$D7;$D7<>"";Personeel!I7="v")

Acties:
  • +1 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
Reptile209 schreef op donderdag 31 oktober 2024 @ 13:41:
[...]

Doe eens wat ik hier voorstelde: Reptile209 in "Cellen in blad A markeren gebaseerd op gegevens uit blad B" Dat helpt je te troubleshooten waar het mis gaat.
Bedankt, dit werkt inderdaad een stuk gemakkelijker! _/-\o_

Acties:
  • 0 Henk 'm!

  • mboonstra_jb
  • Registratie: Oktober 2024
  • Laatst online: 10-01 10:22
Als volgt opgelost:

Verwijzing onderaan werkblad "Voorjaar" gemaakt naar gehele tabel uit werkblad "Personeel".
Cellen met waarde "v" en cellen groter dan 1 (dus dubbel geplande items) in deze tabel laten markeren met een "1".
code:
1
=(ALS('Personeel + vrij dagen'!I7="v";1;ALS('Personeel + vrij dagen'!I7>1;1;ALS('Personeel + vrij dagen'!I7<>"v";0))))


Vervolgens heb ik op de kolommen waarin de medewerker gepland wordt via voorwaardelijke opmaak rood laten omranden wanneer er in de hierboven genoemde tabel een waarde >0 naar voren komt:
code:
1
=AANTALLEN.ALS($D$102:$D$825;$E47;F$102:F$825;1)


Resultaat is naar wens, tussenvoegen nieuwe personeelsleden vergt wel wat routine maar daarentegen neemt het wel weer mogelijk risico weg. Dubbel geplande dagen en dagen die als vrij zijn gemarkeerd worden nu rood omrand en zijn duidelijk zichtbaar.

Afbeeldingslocatie: https://tweakers.net/i/iXYvJpT3G5608Jv9CyAaL6YoZik=/fit-in/4000x4000/filters:no_upscale():strip_exif()/f/image/GeabDml8zUK4yEhUodnWNmA7.png?f=user_large
Pagina: 1