Variabel jaartal in formule

[spaarie]

Beste helpers,

Zit met een vraag. Ik heb zelf al verschillende dingen geprobeerd, maar krijg het niet voor elkaar.

Hoe krijg ik een variabel jaartal in een formule? Ik gebruik de volgende formule in bestand "Verlofkaart 2012":

=SOMPRODUCT(('K:\Verlofkaart\[Opgenomen verlof 2012.xlsx]SYNTESS'!$C$2:$C$20000=$E$1)*('K:\Verlofkaart\[Opgenomen verlof 2012.xlsx]SYNTESS'!$B$2:$B$20000=C$13)*('K:\Verlofkaart\[Opgenomen verlof 2012.xlsx]SYNTESS'!$F$2:$F$20000))

Het één en ander is al aangepast naar variabel aan de hand van de bestandsnaam, maar dit wil ik nu ook in bovenstaande formule. Dus als het bestand 2013 is moet de formule ook automatisch aanpassen naar "opgenomen verlof 2013.xlsx".

Wordt nu gedaan d.m.v. =CEL("bestandsnaam") en dan 1x formule LINKS en formule RECHTS dat 2012 overblijft.

Iemand een idee?

 

[Thoralf]

Wat gebeurt er als je in de bestandsnaam zoekt op een punt (je krijgt dan de positie waar die staat) en als je daar 4 van aftrekt heb je de eerste positie van het jaartal 2013. Dat haal je met de functie DEEL in een keer uit die bestandsnaam (je hebt de startpositie al en de lengte is 4). Je zou het eventueel ook alleen met het laatste cijfer van het jaartal kunnen doen, maar dan ga je natuurlijk in 2020 de mist in

 

[spaarie]

Beste Thoralf,

Oke, dat snap ik maar hoe krijg ik dit nou in me SOMPRODUCT formule?

Niet zo in ieder geval

=SOMPRODUCT((INDIRECT(Gegevens!P9&Gegevens!P10&Gegevens!P11))$C$2:$C$20000=$E$1)*(INDIRECT(Gegevens!P9&Gegevens!P10&Gegevens!P11;$B$2:$B$20000=$D$13)))) etc. etc.

 

[Thoralf]

=SOMPRODUCT((INDIRECT("'K:\Verlofkaart\[Opgenomen verlof "&DEEL(CEL("bestandsnaam");VIND.SPEC(".";CEL("bestandsnaam")-4);4)&".xlsx]SYNTESS'!$C$2:$C$20000")=$E$1)*.....

Ik heb een stukje van je oorspronkelijke somproduct-formule gepakt en dat aangepast.

 

[spaarie]

Beste Thoralf,

Ik krijg het nog niet voor elkaar, want de path van moet ook variabel zijn. Ik heb op met tabblad gegevens P2 deze formule:

=DEEL(CEL("bestandsnaam");VIND.SPEC("[";CEL("bestandsnaam"))-15;15)&TEKST.SAMENVOEGEN("[Opgenomen verlof ";DEEL(CEL("bestandsnaam");VIND.SPEC(".";CEL("bestandsnaam"))-8;4);".xlsx]")

Dus helemaal variabel en dit resulteert in: K:\Verlofkaart\[Opgenomen verlof 2012.xlsx]

Nu moet op me basis tabblad de SOMPRODUCT formule komen met INDIRECT Gegevens'!P2 en SYNTESS'! etc, etc.

Ik zit echt al een tijd te k*tten hiermee, maar of hij geeft een fout of het resultaat is een fout... :evil:

Doet het niet:

=SOMPRODUCT((INDIRECT("'"&Gegevens!P2&"SYNTESS'!$C$2:$C$20000")=$E$1)*(INDIRECT("'"&Gegevens!P2&"SYNTESS'!$B$2:$B$20000")=$D$13)*(INDIRECT("'"&Gegevens!P2&"SYNTESS'!$F$2:$F$20000")))

 

[spaarie]

Iemand anders misschien????

 

[Thoralf]

Hallo Spaarie,

Als je de eerste 15 tekens of beter nog alle tekens tot [ wilt hebben, dan doe je dat toch via LINKS(CEL("Bestandsnaam");VIND.SPEC("[";CEL("bestandsnaam"))-1)
Je weet dan zeker wat je hebt: namelijk alles tot de eerste [.
Zoals je het nu lijkt te doen ben je er niet zeker van dat je het volledige pad krijgt, omdat je slechts 15 tekens pakt voor de eerste [.

 

[spaarie]

Beste Thoralf,

Dit is inderdaad correct, daarvoor ook mijn dank. Maar dit had een toekomstig probleem geworden zodra het bestand van locatie had veranderd en me SOMPRODUCT formule heb staat maar op #VERW!

 

[Thoralf]

Wil je met de indirect-functie weinig problemen hebben, dan kun je bijv het volgende doen.
Maak je somproduct-formule met de harde verwijzingen (dus je ziet in de formule de namen, zoals ze gegeven zijn aan bijv de bladen). Als je vervolgens die verwijzingen tussen "" zet en voor die " " de tekst INDIRECT( en na " " de tekst ) dan kun je eens kijken wat er gebeurt. Er zou geen wijziging in werking moeten zijn.
Vervolgens kun je op de daarvoor noodzakelijke plaatsen "&" invoegen en dan eventueel een gedeelte "..." vervangen door een celverwijzing.
Probeer dit eens uit met een voorbeeld bestandje om even het gevoel te krijgen en ga daarna naar je definitieve bestand.

 

[spaarie]

Het is me grotendeels gelukt nu. In principe werkt het, maar nog niet over de gehele gehele path.

Ik ga er denk verder wel mee uitkomen. Bedankt Thoralf.