Waarden op basis van code en data koppelen

[AnkieForum]

Goedemiddag,

Bijgaand een voorbeeldbestandje. Bekijk bijlage Voorbeeldje.xlsx

Ik wil graag in Blad 1 in de drie rode kolommen (F, G en H) de waarden hebben die in Blad 2 bij dezelfde werkgever staan in de juiste periode.
In kolom I tot en met K heb ik de juiste antwoorden gezet. Het doel is dus dat de werkgever bepalend is voor de waarde die er staat, met als voorwaarde dat de regel bij de werkgever geldig is in de periode van regels bij de medewerker.

Ik heb hierbij een aantal randvoorwaarden:
- De hoeveelheid rijen en regels per medewerker en werkgever kan telkens verschillen (niet te doen in een voorbeeldbestand maar wel belangrijk om rekening mee te houden)
- Bij voorkeur niet in een Macro maar gewoon in een Excelformule
- Het systeem waarin deze excel gedraaid wordt laat geen formule toe waarbij in Rij 2 naar rij 3 verwezen wordt (omdat niet altijd zeker is dat er een rij 3 bestaat). Vanuit rij 2 naar rij 1 mag wel.

Mocht mijn omschrijving onvoldoende duidelijk zijn, hoor ik het graag, dan kan ik altijd meer informatie aanleveren.

Alvast dank,

Ankie

 

[MarcelBeug]

Bekijk bijlage Voorbeeldje MB.xlsx

Zie bijlage.
Het zijn matrixformules geworden, d.w.z. formules die je moet invoeren met Ctrl+Shift+Enter. Excel zet er dan zelf accolades {} omheen.

Aandachtspunten / vragen / details:

1. Van de gegevens in Blad2 heb ik een tabel gemaakt.
De formules in Blad1 werken alleen als gerefereerd wordt aan gevulde gegevens in Blad2.
Door een tabel te gebruiken, gaat het nog steeds goed als er gegevens aan de tabel worden toegevoegd of er uit worden verwijderd.

2. Voor een juiste werking MOET de tabel in Blad2 oplopend gesorteerd zijn op leveranciernummer en dan op begindatum (zoals al het geval was).

3. De eerste regel levert een verschil op omdat er geen gegevens van leverancier 999 zijn per 1-1-2012 (Begindatum op Blad2 bij leverancier 999 is 1-1-2015).
Daardoor pakt de formule de laatste regel van leverancier 111.
Ik neem aan dat dat een foutje in de tabel op blad2 is?

4. Er wordt alleen gezocht op basis van begindatum:
a. Ik neem aan dat de gegevens in Blad1 afgestemd zijn op de datums in Blad2?
b. Ik neem aan dat regels in Blad1 niet gesplitst hoeven te worden (als op 1 regel in Blad1 meerdere regels in Blad2 van toepassing zijn)?

5. De formules op Blad1 verschillen per kolom en zijn elk vanaf regel 2 naar beneden gekopieerd (d.w.z. F2 is naar beneden gekopieerd t/m F12, G2 naar G12 en H2 t/m H12).

Wellicht is het (nog) niet helemaal wat je zoekt, maar dan hebben we tenminste een basis voor verdere verfijning.

 

[AnkieForum]

Hi Marcel,

Dank je wel voor je hulp zo ver. Het doet inderdaad wat ik nodig heb, door te proberen om het door te voeren ben ik echter achter een volgende beperking gekomen: ik kan niet standaard de gegevens omzetten naar een tabel. Is er een mogelijkheid om dit op een manier in te richten waarbij je geen tabellen nodig hebt? Iets met vergelijken bijvoorbeeld? Ik kan wel de titels van de kolommen gelijk houden bijvoorbeeld.

Om in te gaan op je aandachtspunten / vragen / details:

1. Van de gegevens in Blad2 heb ik een tabel gemaakt. Zie bovenstaand, ik hoop dat je nog meer ideeën hebt. Sorry dat ik dit niet eerder aan kon geven...


2. Voor een juiste werking MOET de tabel in Blad2 oplopend gesorteerd zijn op leveranciernummer en dan op begindatum (zoals al het geval was). Geen probleem, dit kan ik standaard instellen.

3. De eerste regel levert een verschil op omdat er geen gegevens van leverancier 999 zijn per 1-1-2012 (Begindatum op Blad2 bij leverancier 999 is 1-1-2015).
Daardoor pakt de formule de laatste regel van leverancier 111.
Ik neem aan dat dat een foutje in de tabel op blad2 is? Ja klopt, dit zal in 'het echt' niet voorkomen. Dank je.

4. Er wordt alleen gezocht op basis van begindatum Dat is prima
b. Ik neem aan dat regels in Blad1 niet gesplitst hoeven te worden (als op 1 regel in Blad1 meerdere regels in Blad2 van toepassing zijn)? Eigenlijk wel maar dat kunnen we hier buiten beschouwing laten. Dit ga ik op een andere manier oplossen.


Alvast heel erg bedankt.

Ankie

 

[MarcelBeug]

Bekijk bijlage Voorbeeldje MB2.xlsx

In de bijlage heb ik de tabel vervangen door een gedefinieerde naam WerkgeverTabel.
Deze is zodanig gedefinieerd (via tab Formules - Namen beheren) dat het nog steeds goed gaat als er gegevens worden toegevoegd of verwijderd:

=Blad2!$A$2:INDEX(Blad2!$A:$H;1+AANTAL(Blad2!$A:$A);KOLOM(Blad2!$H1))

wat zoveel betekent als: het gebied Blad2!A2:Hn (n = 1 + het aantal getallen in kolom A). Die 1 is voor de kopregel.

De formules op blad1 zijn nog steeds matrixformules en verschillend per kolom (F2, G2 en H2, elk naar beneden gekopieerd).