Allocatie van kosten

[verhoog]

Zie bijlage, deze is als volgt opgebouwd:

- Input sheet
- Samenvatting per soort

Middels een vert.zoeken formule haal ik bedragen voor de samenvatting op uit de input sheet (bijv cel J7). Het nadeel hiervan is dat als ik een kostensoort wil invoegen tussen bijv A en B, ik de kolomnummers uit de formules van B, C en D handmatig moet aanpassen, omdat deze niet automatisch mee 'opschuiven'. Ik heb al wat gestoeid met de somproduct-formule (zoeken op kostensoort, 'Fees' en periode), maar ik kom hier niet uit. Iemand een idee hoe dit anders is op te lossen?
.
.

 

[Roncancio]

Je moet zorgen dat het kolomgetal flexibel is.

=ALS(ISFOUT(VERT.ZOEKEN($B7;Input!$E$7:$ES$3881;[B][U]8[/U][/B];ONWAAR));"";VERT.ZOEKEN($B7;Input!$E$7:$ES$3881;[B][U]8[/U][/B];ONWAAR))

In onderstaande formule wordt gekeken naar de letter in rij 4 (in dit geval B).

=ALS(ISFOUT(VERT.ZOEKEN($B7;Input!$E$7:$ES$3881;VERGELIJKEN(J4;Input!4:4;0)-4;ONWAAR));"";VERT.ZOEKEN($B7;Input!$E$7:$ES$3881;VERGELIJKEN(J4;Input!4:4;0)-4;ONWAAR))

Met vriendelijke groet,


Roncancio

 

[verhoog]

Ja, naar zoiets was ik op zoek, en deze werkt inderdaad. Thx!

 

[verhoog]

Hmm, toch werkt het niet helemaal naar behoren. Ik heb nu op het 'Per Soort' blad tussen kolom AP en AQ een kolom weggehaald (zie bijlage). In cel AQ8 staat nu:

=IF(ISERROR(VLOOKUP($B8;Input!$E$7:$EO$3901;MATCH($J$4;Input!$5:$5;0)+30;FALSE));"";VLOOKUP($B8;Input!$E$7:$EO$3901;MATCH($J$4;Input!$5:$5;0)+30;FALSE))

Deze haalt nu de verkeerde info op, het zou +29 moeten zijn voor het kolomgetal. Maar dan zou ik dus nog steeds iedere keer de formules moeten aanpassen als ik een kolom toevoeg/weghaal....
.
.

 

[dr rutno]

En als je het nou relateert aan het kolomnummer van de betreffende kolom?

=ALS(ISFOUT(VERT.ZOEKEN($B8;Input!$E$7:$EO$3901;VERGELIJKEN($J$4;Input!$5:$5;0)+[B]KOLOM(AQ$2)-13[/B];ONWAAR));"";VERT.ZOEKEN($B8;Input!$E$7:$EO$3901;VERGELIJKEN($J$4;Input!$5:$5;0)+[B]KOLOM(AQ$2)-13[/B];ONWAAR))

Ik kan niet goed inschatten of dit het gewenste resultaat levert, maar de formule helpt je wellicht iets verder...

 

[verhoog]

Ik weet inmiddels waar het mis is gegaan. In mijn eerste vraagstelling heb ik niet duidelijk gemaakt dat het ook voor kan komen dat er op het Input blad kolommen worden ingevoegd. Sterker nog, als er kolommen worden ingevoegd gebeurt dat op beide tabbladen op dezelfde plaats; er wordt dus een extra kostenplaats ingevoegd. Dus, in het voorbeeld betekent dit bijvoorbeeld dat er op beide tabbladen tussen kostenplaatsen A en B een hele nieuwe kostenplaats wordt ingevoegd, waardoor B t/m L opschuift naar rechts.

Iemand dan een idee hoe ik de verwijzingen kan behouden?

 

[Roncancio]

Ik heb de formule van cel AQ8 zo aangepast dat er gekeken wordt naar de waarde in rij 7.

=ALS(ISFOUT(VERSCHUIVING(Input!B7;VERGELIJKEN($C8;Input!$C$7:$C$3901;0)+8;VERGELIJKEN($AO$6;Input!$7:$7;0);1;1));"";VERSCHUIVING(Input!B7;VERGELIJKEN($C8;Input!$C$7:$C$3901;0)+8;VERGELIJKEN($AO$6;Input!$7:$7;0);1;1))

Het resultaat is de waarde in cel AS17 van Input.

Met vriendelijke groet,


Roncancio

 

[verhoog]

Ok, nu wordt ie voor mij een beetje te ingewikkeld. De formule werkt inderdaad, alleen als ik deze naar links kopieer om deze vanaf kolom E in alle tabellen te vullen, krijg ik een error. De =VERSCHUIVING(Input!B7.... ) kan ik niet verder naar links kopieren dan 1 kolom, omdat er voor A niks zit.... Resulteert in =VERSCHUIVING(Input!#REF.... )

Is het mogelijk de formule op te stellen voor cel E8? Dan kan ik m daarna wel door kopiëren naar rechts.

Thx!

 

[Roncancio]

De formule voor E8.

=ALS(ISFOUT(VERSCHUIVING(Input!$B$7;VERGELIJKEN($C8;Input!$C$7:$C$3901;0)+8;VERGELIJKEN(E$6;Input!$C$7:$IV$7;0);1;1));"";VERSCHUIVING(Input!$B$7;VERGELIJKEN($C8;Input!$C$7:$C$3901;0)+8;VERGELIJKEN(E$6;Input!$C$7:$IV$7;0);1;1))

Met vriendelijke groet,


Roncancio

 

[verhoog]

Ik ga ervan uit dat hij nou goed, alles lijkt in orde. Ik zal de komende dagen verder testen.

Wederom bedankt Roncancio :thumb:

 

[verhoog]

Jammer, hij werkt nog niet helemaal. Probleem zit m volgens mij in het feit dat er op het Input blad op rij 7 steeds hetzelfde rijtje voorkomt (fees, onk, bonus, etc). Aangezien hij daarop zoekt, zal hij steeds dezelfde getallen retourneren, die uit de eerste tabel.

Nog nieuwe ideeën?

 

[Roncancio]

Nog nieuwe ideeën?

Ja, de werkbladen gelijk laten lopen. Dwz groep A van het ene werkblad begint in dezelfde kolom als groep A van het andere werkblad.

De groepen A t/m L zijn even breed.
Dat heb je ook geen last meer van ingewikkelde formules.

Met vriendelijke groet,


Roncancio

 

[verhoog]

Het vervelende alleen is dat het aantal rijen per periode op het input-blad kan verschillen. Soms komen er mensen bij, soms gaan er mensen af. De rij die opgehaald moet worden is dus de ene keer lager of hoger dan de andere keer.

Een ander alternatief is natuurlijk eenmalig flink wat extra groepen aan te maken, en deze verbergen totdat ik ze nodig heb... Maar ik dacht dat er wellicht nog lumineuze ideeën zouden zijn om dit varkentje te wassen.

 

[Roncancio]

Het vervelende alleen is dat het aantal rijen per periode op het input-blad kan verschillen. Soms komen er mensen bij, soms gaan er mensen af. De rij die opgehaald moet worden is dus de ene keer lager of hoger dan de andere keer.

Een ander alternatief is natuurlijk eenmalig flink wat extra groepen aan te maken, en deze verbergen totdat ik ze nodig heb... Maar ik dacht dat er wellicht nog lumineuze ideeën zouden zijn om dit varkentje te wassen.

Ik bedoel de kolommen gelijk laten lopen niet zo zeer de regels.
De formule wordt een stuk overzichtelijker als er niet meer naar de kolommen moet worden gezocht. Zeker gezien het feit dat de kolommenindeling vanaf A t/m L identiek is.

Met vriendelijke groet,


Roncancio

 

[verhoog]

Werkt dat ook als de groepen niet even breed zijn? Groep is nl 4 kolommen breed, groep B 5. En welke formule had je dan in gedachten?

 

[Roncancio]

Als je bij kolom C van "Per Soort" 2 kolom invoert zijn de kolommen G t/m BN identiek in beide werkbladen.
Ook zie ik dat er bij Input voor elke dag steeds hetzelfde aantal regels wordt gebruikt.
Als dit zo hoort, maakt dat de formule een stuk eenvoudiger.
Je hoeft dan slechts een formule te gebruiken die de dag zoekt in Input en vervolgens een vast aan regels naar beneden gaat voor het totaalbedrag.

Waarna je de volgende formule kunt gebruiken.

=VERSCHUIVING(Input!$C$7;VERGELIJKEN('Per Soort'!$C8;Input!$C$8:$C$47;0)+9;KOLOM()-3)

Met vriendelijke groet,


Roncancio

 

[verhoog]

Ok, dat is waar. Met wat extra verborgen kolommen is het aantal kolommen gelijk te krijgen.
Echter, zoals ik eerder al zei kan het aantal regels wel verschillen. Iedere regel is namelijk een persoon, en er kunnen per periode personen bijkomen of afgaan.