Variabele gebruiken in formule

[Jemp]

Ik gebruik volgende formule in de sheet "Resultaat/Rechtstreeks"
=SOMPRODUCT((Acties2024.xlsx!Activiteit="v")*(Acties2024.xlsx!Namen=A$1))

Aangezien ik elk jaar het jaartal (2024 in het voorbeeld) moet aanpassen wilde ik hier een variabele van maken (Jaartal)

Met de nieuwe uitdrukking werkt de formule echter niet (zie sheet "Resultaat/ViaJaartal"
=SOMPRODUCT((INDIRECT("Acties"&Jaartal&".xlsx!Activiteit")="v")*(INDIRECT("Acties"&Jaartal&".xlsx!Namen")=A$1))

Ik heb twee bestanden toegevoegd die ik enkel gemaakt heb om mijn probleem duidelijk te maken.
Ik maak gebruik van Excel van Microsoft 365

JPR

 

[AlexCEL]

In je voorbeeld kan zoiets voor resultaat.xlsx:

=SOMPRODUCT((INDIRECT("[Acties"&Jaartal&".xlsx]Blad1!A2:C4")="v")*(INDIRECT("[Acties"&Jaartal&".xlsx]Blad1!A1:C1")=A$1))

Ik zou echter naar andere oplossingen zoeken. Bijvoorbeeld Power Query of de gegevens in 1 werkblad krijgen.

 

[Jemp]

Alvast bedankt voor het snelle antwoord, maar ...
ik zou graag het voordeel van een gedefinieerde namen te gebruiken ("Activiteit" en "Namen" ) willen behouden. Het voordeel is dat ik geen domein moet aangeven en dat ik de formule gewoon naar de volgende lijn kan kopiëren in "Resultaat" zonder het domein te moeten aanpassen (zie Tab "Rechtstreeks").

Kan ik geen formule gebruiken die hierbij aanleunt? Ik zou dus zelfde formule willen gebruiken waarbij het jaartal als variabele gebruikt wordt.

 

[Jemp]

Het probleem wordt goed in beeld gebracht wanneer de formules in de verschillende cellen geëvalueerd worden in het werkboek "Resultaten2".

In het tabblad "Rechtstreeks" geeft de evaluatie van "Acties2024.xlsm!Activiteit"

• in cel B2 : {"v"\0\ "x"} wat perfect overeenkomt met de gegevens in Acties2024 lijn 2
• in cel B3 : {0\"v"\ "v"} wat perfect overeenkomt met de gegevens in Acties2024 lijn 3

In het tabblad "ViaJaartal" geeft de evaluatie van INDIRECT("Acties"&Jaartal&".xlsm!Activiteit") echter

• in cel B2 : {"naam1"\"naam2"\ "naam3"} wat overeenkomt met de gegevens in Acties2024 lijn 1
• in cel B3 : {"naam1"\"naam2"\ "naam3"} wat ook overeenkomt met de gegevens in Acties2024 lijn 1

De vraag is dus : vanwaar komt dit verschil (tabblad "Rechtstreeks" is correct) en kan er aan verholpen worden?

 

[emields]

Plaats alles in 1 blad en met een draaitabel haal je eruit wat je wil. Waarom 2 bestanden en bladen gebruiken als je dan toch alles op 1 blad wil?

 

[Jemp]

@ emields
Er zijn verschillende redenen waarom er 2 bestanden zijn.
De voorbeeld bestanden zijn uiteraard eenvoudig maar heb ik enkel gemaakt om het optredende probleem duidelijk te maken.

 

[AlexCEL]

Ok.
- Je wilt dus eigenlijk ook nog per dag het resultaat hebben? Dan komt er nog een INDIRECT bij.
- Ik heb het wel zo aangepast dat je gedefinieerde namen kunt gebruiken.
- Let er dan wel op dat deze GOED gedefinieerd zijn.
- In je voorbeeld(en) klopte het een en ander niet qua ingestelde bereiken.
- Verder kwam ik geen cel tegen met de naam Jaartal, dan werkt het ook niet. Die heb ik dus toegevoegd.
- Bestand acties2024.xlsm moet open staan uiteraard.

De formule wordt dan:

=SOMPRODUCT((INDIRECT("[Acties"&Jaartal&".xlsm]Blad1!Activiteit")="v")*(INDIRECT("[Acties"&Jaartal&".xlsm]Blad1!Namen")=B$1)*(INDIRECT("[Acties"&Jaartal&".xlsm]Blad1!Dagen")=$A2))

In onderstaande voorbeelden krijg ik volgens mij de correcte resultaten. Geef anders eens aan wat de resultaten moeten worden.

 

[Jemp]

Nogmaals bedankt voor het antwoord en de inspanning.
Het laatste ontwerp werkt (uiteraard) perfect als we een derde argument (dagen) toevoegen aan SOMPRODUCT.

Ik tracht enkel nog een antwoord te vinden op mijn vraag van gisteren (02:39).
Om een of andere reden valt het "dynamische" aspect van de formule weg als we via INDIRECT gaan.
Laat mij stellen dat het probleem op zich wel opgelost is en dat ik enkel nog nieuwsgierig ben waarom we via INDIRECT een derde argument moeten toevoegen (Dagen) wat, zonder de variabele "Jaartal", niet nodig is.