ALs geselecteerde mnd bsb in Tabel x DAN SOM die kolom ANDERS SOM kolom Tabel Y

[SteveSchouten]

Goeiedag,

Ik ben al een uren bezig om de juiste excel formule te schrijven maar kom er echt niet uit.
Hoop dat iemand kan helpen?

Wat ik zou willen is het volgende:
ALS ik in Cel B3, bv maand januari-20 selecteer, wil ik dat ALS er in de Tabel Actuals voor die maand gegevens beschikbaar zijn, deze worden opgeteld. Voor januari zijn er actuals beschikbaar, dus zou het resultaat van de formule zou €81.511 moeten zijn. Zou ik in B3 februari kiezen, dan zou het resultaat dus €74.469 moeten zijn. Etc, etc.

ALS er voor de geselecteerde maand (bv augustus-20), géén gegevens beschikbaar zijn in de Tabel Actuals, dan wil ik dat hij naar de Tabel Forecast gaat, en dan voor die maand, de SOM teruggeeft; in dit voorbeeld dus €87.548.

Voetnoot: Het aantal rijen in Tabellen Actuals en Forecast staat niet vast.

Iets met ALS, ISNB, VIND.SPEC, SOM, VERSCHUIVEN?

Please help out?

 

[AlexCEL]

Niet echt simpel, maar werkt volgens mij wel met gegeven condities:

=ALS.FOUT(1/(1/SOMPRODUCT((INDIRECT("C8:N"&VERGELIJKEN("Forecast";B:B;0)-2))*(C7:N7=B3)));SOMPRODUCT(VERSCHUIVING(B8;2+VERGELIJKEN("Forecast";B8:B1000;0);VERGELIJKEN(B3;C7:N7;0);1000)))

Als je Excel 365 gebruikt kun je SOMPRODUCT ook vervangen door de functie SOM.

 

[SteveSchouten]

Thanks @AlexCEL voor je snelle reactie! Eens kijken of het werkt!

 

[SteveSchouten]

@AlexCEL. GeWeldig! Hij werkt bíjna helemaal!:thumb:
Als ik rijen toevoeg aan de kolom Forecast, neemt de formule de data in die nieuwe rijen keurig over.
Maas als ik rijen toevoeg aan de kolom Actuals niet.
Hoe zou het deel van de code dat betrekking heeft op de Actuals ook dynamisch moeten worden gemaakt?

 

[AlexCEL]

Had ik ook reeds geconstateerd en de formule al aangepast in post #2. Hieronder nogmaals, deze formule is "dynamisch" in alle opzichten (maar daardoor ook complex):

=ALS.FOUT(1/(1/SOMPRODUCT((INDIRECT("C8:N"&VERGELIJKEN("Forecast";B:B;0)-2))*(C7:N7=B3)));SOMPRODUCT(VERSCHUIVING(B8;2+VERGELIJKEN("Forecast";B8:B1000;0);VERGELIJKEN(B3;C7:N7;0);1000)))

Hoe komt die lijst tot stand? Waarschijnlijk kan met benoemde tabellen bovenstaande flink versimpeld worden.

En: mag een hulpkolom ook? Dan geldt ook dat het veel simpeler kan... (zie bijlage)

 

[SteveSchouten]

Wauw AlexCel, dit is het!:thumb:

Die lijst met mijn testbestand was een voorbeeld met fictieve data.
In de praktijk worden actuals aangeleverd door een boekhouder, die werkt met een programma waar geen extra (hulp)kolommen aan kunnen worden toegevoegd (uit dat programma wordt een excel export gedraaid).
Die forecast wordt door de lijnorganisatie bijgewerkt in een afzonderlijke excel.

Weet niet precies wat je bedoeld met 'benoemde tabellen'? Dat je die tabellen Actuals en Forecast ook echt als tabellen opmaakt en benoemt en dat je dan met gestructureerde (ipv specifieke cel)referenties kunt werken?

Deze formule gaat mijn excel pet helaas nog wel te boven
Ik ga proberen 'm te snappen mbv de formule evaluatie functie.

Veel dank man, dit is super!

 

[AlexCEL]

Prima.

met behulp van evaluatie zou je een eind moeten komen om 'm te snappen. Succes ermee. Mochten er nog vragen zijn hoor ik het wel.