Matrix formule met resultaten uit meerdere werkbladen

[burley]

Hallo allemaal!

Ik worstel met een (voor mij) uitdagende matrix formule om gegevens uit meerdere werkbladen in één blad te krijgen. Wij gebruiken op dit moment een template met een matrix formule om de gegevens vanuit één blad dynamisch overzichtelijk in één werkblad te presenteren, icm wat opzoek functies zodat alleen de factuur/credit gegevens getoond worden die relevant zijn. Dit hebben wij nodig voor het berekenen van de commissie van onze verkoop agenten.

Het gaat om onderstaande formule;

{=(ALS.FOUT(INDEX(Invoices!M$2:M$57;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(Invoices!M$2:M$57;$A$1:A1;0));0));""))}

Deze formule zorgt ervoor dat wanneer er nieuwe gegevens (factuur en credit factuur waarden) in het data blad komen te staan, dit automatisch geupdate wordt in ons overzicht.

In ons nieuwe overzicht worden de gegevens van facturen en credits vanuit verschillende bronnen aangeleverd en ik wil dat dus graag combineren. Hier zit echter het probleem, want wat ik ook probeer ik krijg het niet voor elkaar om de gegevens uit het tweede blad weer te geven. In mijn boeren verstand leek het mij logisch dat wanneer het ene blad geen gegevens meer oplevert (de formule een blanco cel oplevert) ik de waarde_indien_fout dien te vervangen voor een nieuwe formule die verwijst naar het andere blad, maar helaas zit ik hier fout, want mijn gekunstel (zie onderstaande) levert niets op.

{=(ALS.FOUT(INDEX(Invoices!M$2:M$57;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(Invoices!M$2:M$57;$A$1:A1;0));0));ALS.FOUT(INDEX(Credit_notes!M$2:M$57;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(Credit_notes!M$2:M$57;$A$1:A1;0));0));"")))}

Mijn vraag is dus hoe ik de waarden uit twee werkbladen mbv bovenstaande formule (oid) op één blad kan laten tonen.

Alvast heel erg bedankt voor alle input! En uiteraard zijn alle suggesties welkom

Bijgevoegd een voorbeeld bestand.

 

[AlexCEL]

Welke versie van Excel gebruik je?
Welke kolommen gaat het om? Het zijn nogal uitgebreide tabellen. Heb een gokje gedaan...
In bijlage een eerste opzet met Power Query

 

[burley]

Dank voor je reactie! Je opzet is heel aardig en zeker logisch gedacht, echter heb je alles nu gegroepeerd op order nummer, terwijl dat niet de bedoeling is (begrijp de gedachte). Maar zowel de credits als de facturen moeten afzonderlijk worden weergegeven, zonder onderlinge relatie.

Ik werk met excel 2019. Het gaat mij in eerste instantie om de kolommen 'M' (op beide tabbladen). Dus "Invoice_number" op tabblad 'Invoices' en "Credit_note_number" op tabblad Credit notes.

Power query's zijn mij totaal niet bekend (zal me straks inlezen), maar kreeg inderdaad de melding dat de query gemaakt was met een nieuwere versie dan ik heb. Desalniettemin werkt hij wel, maar ik heb dus nog niet wat ik wil

 

[AlexCEL]

Het was dan ook een eerste aanzet...

Met PQ kun je ook queries aanvullen, dus wat je wilt kan vast wel.
Maar: wat wil je onder elkaar hebben? Welke kolommen specifiek, dat zou ik graag even weten...

En die melding komt omdat ik een Office Insider versie gebruik.

 

[burley]

Je inzet wordt al enorm gewaardeerd, ook de eerste aanzet en ook al snap ik nog niet heel veel van de pq (het is wel heul handig en biedt enorme voordelen!).

Wat ik in één kolom wil hebben zijn zowel de invoice nummers als de credit note nummers, vandaar is de rest heel eenvoudig want al die nummers zijn uniek, dus de rest van de data is zo aangevuld.

 

[JEC.]

De data in de 2 sheets staat ook niet in dezelfde kolommen

 

[AlexCEL]

Poging #2

 

[burley]

Ja dat is het! Super!!! Hier ga ik mee aan de slag!

Heel erg bedankt! Ik ga de PQ op veel meer plaatsen toepassen, dus een gouden tip voor mij.

 

[burley]

De data in de 2 sheets staat ook niet in dezelfde kolommen

De factuurnummers en de credit factuur nummers staan beide in kolom M op de verschillende sheets, de rest is van onder geschikt belang