Opgelost vstack

[popipipo]

=VSTACK('Groep A'!A2:.I20;'Groep B'!A2:.I20;'Groep C'!A2:.I20)

Deze formule met trimrange werkt wel maar waarom werkt het niet op deze manier:

=VSTACK('Groep A:Groep C'!A2:.I25)

 

[peter59]

Ik heb dat Claude eens gevraagd, want die kan het beter verwoorden dan ikke.

De 3D-verwijzing werkt niet in VSTACK.
'Groep A:Groep C'!A2:I25 is een zogenaamde 3D-referentie, waarmee je dezelfde range op meerdere tabbladen tegelijk aanspreekt (handig bij functies als SOM, GEMIDDELDE, enz.). Het probleem is dat VSTACK, net als de meeste nieuwe dynamische matrixfuncties, geen 3D-verwijzingen ondersteunt.
Excel geeft dan een foutmelding, meestal #REF! of #WAARDE!

Als je met heel veel bladen werkt, is het onhandig om alles steeds handmatig te typen.
Gelukkig zijn er alternatieven, zoals Power Query om alle bladen samen te voegen of een kleine VBA-macro/LAMBDA-functie om de bladnamen dynamisch op te halen.

 

[popipipo]

Ik denk dat een filterfunctie toevoegen ook gaat. Power Query vind ik een vereenvoudigde versie van VBA en dat zijn niet mijn beste kwaliteiten. Kun je een voorbeeld geven van die LAMBDA functie?

 

[JEC.]

Zie hier de uitleg

Onderwerp 'Specifieke data ophalen van specifieke sheets (O365)'

4 sep 2023

Handige tip om data van verschillende tabbladen op te halen.
Uiteraard heb je het liefst alles op één tabblad om vanuit daar te filteren, maar de vraag om data juist terug te brengen naar één tabblad, komt vaak voor.

Ik zal gelijk beginnen met het mooiste deel; GET.WORKBOOK(1) --> In het Nederlands: WERKMAP.LEZEN(1)
Zet onderstaande formule in de name manager en noem deze bijvoorbeeld "all_sheets" (werkt alleen daar en niet in je sheet, vergelijkbaar met de EVALUATE functie). GET.WORKBOOK(1) is ook beschikbaar in oudere versies.

=TOCOL(TEXTAFTER(GET.WORKBOOK(1);"]"))

...

• JEC.

 

[peter59]

Oké @jec, doe mij dan toch maar Power Query.

@popipipo
Het is toch een stukje VBA

 

[JEC.]

Power query werkt hier beter als je de data niet live hoeft te zien veranderen. Via formules zie je de wijzigingen direct terug in de output. Het hangt dus van de situatie af.

Via VBA zou het ook een UDF worden.

 

[peter59]

als je de data niet live hoeft te zien veranderen.

Dat klopt. Maar met één klik op "Alles verversen" is het geregeld.
Een klein nadeel is ook wel dat het het beste werkt als alle tabbladen benoemde tabellen bevatten.

 

[JEC.]

Met alles verversen ben je er maar dat is niet wenselijk na iedere wijziging natuurlijk

 

[peter59]

Gedeeltelijk mee eens, maar als er in de betreffende tabbladen wijzigingen worden aangebracht of nieuwe tabbladen worden toegevoegd, moet je altijd naar het tabblad gaan waar alle wijzigingen samenkomen en gewoon op "Alles verversen" klikken. Klaar.

 

[popipipo]

Op een of andere manier werkt de functie GET.WORKBOOK niet bij mij
Ik gebruik nu deze formule

=FILTER(VSTACK('Groep A:Groep C'!A2:I100);VSTACK('Groep A:Groep C'!A2:A100)<>0)

Iedereen bedankt voor de bijdrage.

 

[peter59]

Ja, oké. Dat is ook een mogelijkheid.
Of als je zowel lege waarden als nullen wilt uitsluiten in kolom A dan kan je misschien deze formule gebruiken.

=LET(Data;VSTACK('Groep A:Groep C'!A2:I100);FILTER(Data;(INDEX(Data;;1)<>"")*(INDEX(Data;;1)<>0))
)

 

[popipipo]

Voor zover ik het nu kan zien zal er nooit een nul in kolom A komen te staan. Kolom A kan wel leeg zijn.
Maar bedankt voor de aanvulling misschien kan ik de methode nog wel eens gebruiken.

 

[JEC.]

@popipipo , ik denk dat je “enable Excel4.0 macros …” moet aanvinken onder trust settings —> macro settings.

En file opslaan als xlsm