Matrixformule aanpassen

[Tweety1]

Ik heb een Excel bestand met op sheet 2 een Matrixformule die gekoppeld is aan een tabel. Als ik de tabel filter dan blijven de zichtbare rijen over in Matrixformule (tabel).
Op tabblad 1 heb ik een formule geplaatst die zoekt in de Matrixformule (tabel).
Als ik de tabel niet filter krijg ik de goede uitslag maar als ik de tabel wel filter dan krijgen de lege cellen geen waarde mee. Hoe kan ik dit aanpassen.

Voorbeeld

Bekijk bijlage Matrixformule.xlsx

mvg

Kasper

 

[sylvester-ponte]

Tweety, je laat hem zoeken in de onderste tabel in sheet2.
Als je in die onderste tabel filtert blijft het goed gaan.
Maar als je in de bovenste tabel op sheet2 filtert verdwijnen er ook waarden in de onderste tabel sheet2
En dat zie je terug in je zoek actie op sheet1
Laat hem zoeken in de bovenste tabel sheet2

 

[Tweety1]

Ok. Maar ik ben opzoek na een formule waardoor ik de tabel kan filteren en alleen de zichtbare waardes in de tabel met verticaal zoeken worden getoond.

 

[Tweety1]

Ik heb ondertussen wat zitten proberen en kwam op het volgende formule

{=ALS.FOUT(VERT.ZOEKEN(R2;ALS(SUBTOTAAL(3;VERSCHUIVING(Opleiding;RIJ(Opleiding)-RIJ(Filter2);0;1));Filter1);2;ONWAAR);"FOUT")}

Nu zou ik graag de formule willen doortrekken via een macro alleen gaat dat niet goed.
Cel R2 blijft steeds hetzelfde alleen wil ik deze doortrekken naar R3,4,5 etc.

Hoe kan ik dit aanpassen

Sub FilterMaken()
Dim strName As String
Application.ScreenUpdating = False
Range("U1").FormulaR1C1 = "Filter"
Columns("U:U").NumberFormat = "General"
strName = ThisWorkbook.Name
Range("U2:U" & Range("R" & Rows.Count).End(xlUp).Row).FormulaArray = _
        "=IFERROR(VLOOKUP(RC[-3],IF(SUBTOTAL(3,OFFSET(Opleiding,ROW(Opleiding)-ROW(Filter2),0,1)),Filter1),2,FALSE),""Overige"")"
Columns("U:U").NumberFormat = "@"
Columns("U:U").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

 

[sylvester-ponte]

komt dit in de richting?in E kolom staat ook iets om de zichtbaarheid van de rij aantegeven

 

[Tweety1]

ziet er goed uit allen krijg ik wel een kringloop melding. Hoe kan ik die oplossen?
Na dat ik het 2x gefilterd heb op kolom A kan ik niks meer selecteren.

 

[Tweety1]

Ik heb het opgelost doormiddel van volgende stukje macro

Dim LastRow As Long
Sheets(1).Range("U2").FormulaArray =  _
        "=IFERROR(VLOOKUP(RC[-3],IF(SUBTOTAL(3,OFFSET(Opleiding,ROW(Opleiding)-ROW(Filter2),0,1)),Filter1),2,FALSE),""Overige"")"
Columns("U:U").NumberFormat = "@"

LastRow = Sheets(1).Range("A65000").End(xlUp).Row
Sheets(1).Range("U2:U" & LastRow).FillDown