Verticaal zoeken naar variabel werkblad

Peterwi · 24 jun 2017

Volgende formule doet het volgende en werkt :

Ik zoek in een variabele excel-file &$A2& (Waarde: Test) naar het veld $A$1 (Waarde : Kode) in tabblad &A2 (waarde Blad1)

=ALS.FOUT(VERT.ZOEKEN($A$1;INDIRECT("[" &$A2& ".xlsx]" &A2& "!$A:$B");2;ONWAAR);0)

Tot zover alles ok maar mijn werkbladen heten niet Blad1 maar Blad (1), Blad (2) enz. (Dat zijn de namen die automatisch gemaakt worden als je een werkblad copiëert)

In dit geval werkt de bovenstaande formule plots niet meer.
Heeft iemand hier een verklaring voor en eventueel ook een oplossing.
De naam van de werkbladen aanpassen is onmogelijk omdat er meer dan 1000 bestanden zijn met soms tot 20 werkbladen.

AlexCEL · 24 jun 2017

variabele excel-file &$A2& (Waarde: Test)
tabblad &A2 (waarde Blad1)

Zonder voorbeeldbestandje is het een beetje gokken, want bovenstaande is mij niet geheel duidelijk. Wellicht is dit een oplossing of zet het je in de goede richting.

Code:

=ALS.FOUT(VERT.ZOEKEN($A$1;INDIRECT("["&$A2&".xlsx]Blad ("&ALS.FOUT(--RECHTS(A2;2);RECHTS(A2;1))&")!$A:$B");2;0);0)

Ik neem dus aan dat in A2 de waarde "Blad1" staat en dat dit "Blad (1)" moet worden bij het zoeken.

Peterwi · 26 jun 2017

Hallo Alexcel,

Bedankt voor je reactie maar blijkbaar was mijn voorbeeld toch niet heel duidelijk.
Als ik in A2 de waarde "Blad1" zet (dat is de naam van het werkblad) dan werkt de formule correct.
Echter, de namen van de werkbladen zijn Blad (2), Blad (3), enz.
Dus als ik in A2 "Blad (2)" zet als waarde werkt dezelfde formule opeens niet meer. Ik vermoed omdat er een spatie in de naam van het werkblad staat.

Peterwi · 26 jun 2017

Hallo Alexcel,

Bedankt voor je reactie maar blijkbaar was mijn voorbeeld toch niet heel duidelijk.
Als ik in A2 de waarde "Blad1" zet (dat is de naam van het werkblad) dan werkt de formule correct.
Echter, de namen van de werkbladen zijn Blad (2), Blad (3), enz.
Dus als ik in A2 "Blad (2)" zet als waarde werkt dezelfde formule opeens niet meer. Ik vermoed omdat er een spatie in de naam van het werkblad staat.

Nu met bijlages

jos1982 · 26 jun 2017

Hoi Peterwi,

Ik weet ook niet 100% zeker of ik de vraag goed begrijp, maar misschien heb je gelijk wat betreft de spatie.

Misschien kun je dit testen: =ALS.FOUT(VERT.ZOEKEN($A$1;INDIRECT("[" &$TEKST(A2;"@")& ".xlsx]" &TEKST(A2;"@")& "!$A:$B");2;ONWAAR);0)

gr Jos

roeljongman · 27 jun 2017

als er een spatie in de bladnamen staat dan moet je tussen enkele quotes of apostrofs toevoegen om de bladnaam >> '

blad1 of

'blad 1'

jos1982 · 27 jun 2017

Wat Roel aangeeft klopt.

Alleen moet jij het toepassen op de gehele bestandsnaam inclusief tabblad.

=ALS.FOUT(VERT.ZOEKEN($A$2;INDIRECT("'[" &$A4& ".xlsx]" &A4& "'!$A:$B");2;ONWAAR);0)

De nuance is dus de apostrof voor het ([) haakje voor het (!) uitroepteken.

Ik heb het getest, dus het zou moeten werken.

Cobbe · 27 jun 2017

Ik vrees dat Indirect en gesloten bestanden een slecht huwelijk is.

jos1982 · 27 jun 2017

Cobbe zei:
Ik vrees dat Indirect en gesloten bestanden een slecht huwelijk is.

Daar heb je gelijk in Cobbe.

roeljongman · 27 jun 2017

@jos, dank voor de nuance, zeg maar correctie, ik wist dat die apostrofs de truc was alleen op het late uur niet lang genoeg nagedacht over de exacte plek ervan

en ik vrees inderdaad dat het met 800 excel bestandjes vrij weinig oplost want die waarden gaan dan alleen zichtbaar zijn als al die 800 bestanden open staan..

@peterwi
in je test tabblad van je voorbeeld staat al dat met indirect je bestanden geopend moet zijn.
ben je excel aan het ontdekken met de uitwerking van verschillende formules? of moet je een echte oplossing hebben voor een bestaand probleem?

Als je echt 800 bestanden moet uitlezen op bepaalde cellen van diverse tabbladen dan kom je waarschijnlijk op VBA code uit. Met vba kun je de bestanden voor de gebruiker ongezien openen en de waarden eruit halen en weer sluiten. Als dat 800 keer moet kan dat even duren natuurlijk dus dan wordt het even goed denken wanneer data opnieuw moet worden ingelezen.

In je testtablad zeg je bij bepaalde formules ook dat de bestanden niet geopend hoeven te zijn klopt maar dat niet helemaal. enige verschil met de indirect formule is dat bij een directe koppeling naar een cel de laatste waarde wordt vastgehouden terwijl bij indirect de waarde verdwijnt zodra je het bronbestand sluit, maar de laatst gelezen waarde kan inmiddels zijn vervangen door een andere waarde en daar kom je alleen achter als je de bestanden opent en de koppeling bijwerkt.

Kortom de bal ligt bij jou om aan te geven hoe nu verder.

Peterwi · 27 jun 2017

Hallo allemaal,

Fijn dat jullie reageren want ik had al eerder een vraag gesteld waar niemand op antwoordde.

@ roeljongman : het is inderdaad een bestaand probleem en niet zo maar iets om excel te leren kennen.

Eigenlijk moet ik wel steeds enkel het laatste werkblad van elke excel-file hebben maar het zijn inderdaad meer dan 800 bestanden (met een variabel aantal excel-bladen).
In het verleden heb ik wel al gewerkt met verticaal zoeken in externe bestanden en soms moeten ze geopend zijn en soms niet (heb niet ontdekt wanneer wel en wanneer niet)
Ik werkte dan wel niet met "indirect".
Het is een probleem dat we al langer hebben en ik zou de gegevens 1 keer per maand moeten verwerken (en controleren).
Ik heb vroeger altijd geprogrammeerd in RPG maar met VBA heb ik geen ervaring.

Alle hulp en suggesties zijn dus meer dan welkom.
- de excelbestanden hebben telkens dezelfde structuur
- de naam van het excel-bestand is telkens het personeelsnummer (steeds 4 posities, dus 1000.xlsx, 2000.xlsx, ...)
- de naam van de werkbladen is dezelfde (dus ook 4 posities). In excel-file 1000.xlsx heten de werkbladen 1000, 1000 (2), 1000 (3), ...

Alvast bedankt voor eventuele suggesties...

roeljongman · 28 jun 2017

Bij zoeken in externe bestanden geldt maar 1 regel. Alleen als het bestand waarnaar je verwijst geopend is tegelijk met je verzamelsheet ben je verzekert van de meest actuele (correcte) waarde.
Het enige verschil tussen een directe koppeling naar en bepaalde cel en het gebruik van de indirect formule is dat bij een directe koppeling de laatst opgehaalde waarde onthouden wordt en bij de indirect functie een foutwaarde verschijnt als het bestand niet geopend is.

Wat ik zou doen is stoppen met werken met koppelingen en met vba een import maken voor de maandelijkse wijzigen, met visuele tussenstap zodat je data kunt controleren voordat je het toevoegt aan de (Bijgewerkte) personeelslijst. ik had nog ergens een bestand die misschien handig kan zijn voor jou.

Bekijk bijlage Directory uitlezen.xlsm

wat het doet is alle bestanden of alle bestanden die na een bepaalde datum gewijzigd zijn opzoeken in een specifieke directory. en vervolgens uit de bestanden in die lijst bepaalde gegevens ophalen.

Volgens mij past dat aardig bij wat jij wilt doen.
je moet elke maand controleren of er nieuwe danwel gewijzigde personeelsfiles zijn. zo ja dan moeten die bijgewerkt worden.
Dit bestand geeft een overzicht van de gewijzigde bestanden en van de benodigde cellen uit die bestanden.
dat lijkt me een goed begin. om die gegevens ook in de personeelslijst te krijgen zit er nog niet in maar dat lijkt me niet al te moeilijk om te maken.
maar dit leek me een goed startpunt.

Een sheet met koppelingen naar 800 verschillende bestanden is niet beheersbaar. 1x per ongeluk op koppelingen bijwerken drukken en opslaan en al je links zijn foutmeldingen.
De weg om te gaan is dus om vba te gebruiken om gemakkelijk en op uniforme wijze infomatie uit de losse bestanden in te lezen in de verzamellijst.

Peterwi · 28 jun 2017

Hallo Roeljongman,

Super bedankt.
Dit is inderdaad waar ik naar op zoek was.
Er moeten wel nog een aantal kolommen bijgevoegd worden maar ik denk wel dat het zal lukken.
Op welke manier kan ik de VBA kodering te zien krijgen en is deze makkelijk aanpasbaar (extra kolommen met extra gegevens).

Wat betreft het verticaal zoeken in niet geopende bestanden : dit lukt wel in sommige toepassingen zonder dat de bestanden geopend zijn.
Als de zoekwaarde niet gevonden is geeft het programma wel een foutmelding #.
In de meer ingewikkelde excel-files gaat het echter niet.

Ik heb bijvoorbeeld een excel-file met de personeelsgegevens (1 regel per persoon) met naam, adres, dienst, geboortedatum enz.
Als ik naar deze file verwijs kan ik steeds zonder problemen de gegevens binnenhalen met verticaal zoeken (Mijn sleutel is dan steeds de personeelsnummer.

HSV · 28 jun 2017

Hard gecodeerd werkt het uiteraard, ..........zoals in onderstaande vorm bv.
Heeft niets met ingewikkeldheid van het bestand te maken.

Code:

=VERT.ZOEKEN(D1;'C:\Users\Peterwi\Documents\[blablabestand.xlsx]Blad1'!$A$1:$G$12;3;0)

Maar met de 'Indirect' functie werkt dat niet helaas zonder dat het bestand open staat.

roeljongman · 28 jun 2017

de vba codering vind je door ALT+F11 te klikken.
of naar het tablad ontwikkelaars te gaan en dan de knop "Visual Basic"

Edit: als je nog niet met macro's hebt gewerkt bestaat de kans dat je ontwikkelaars tabblad / menu nog niet zichtbaar is dat moet je dan nog even activeren onderstaande link geeft aan hoe.
https://support.office.com/nl-nl/ar...eergeven-e1192344-5e56-4d45-931b-e5fd9bea2d45

Extra kolommen zijn niet heel lastig.
Dit is de code die de gegevens ophaalt uit de personeelskaarten

Code:

Sub GetDataFromFiles()
Dim wb As Workbook
    
    Application.ScreenUpdating = False ' turn off the screen updating
[s][COLOR="#808080"]'    Sheet1.Range("tbl_filelist[Filename]").Select
'    Range(Selection, Selection.End(xlDown)).Select[/COLOR][/s]
    cRow = 12 + Sheet1.Range("tbl_filelist[Filename]").Rows.Count
        For i = 13 To cRow
            ' open the source workbook, read only
            Set wb = Workbooks.Open(Filename:=Range("C3") & "\" & Cells(i, 2), ReadOnly:=True)
            ' vind de laatste sheet
            With Sheet1
            [COLOR="#FF0000"]    ' read data from the source workbook
                .Range("D" & i) = wb.Sheets(Sheets.Count).Name
                .Range("E" & i) = wb.Sheets(Sheets.Count).Range("B1")
                .Range("F" & i) = wb.Sheets(Sheets.Count).Range("B2")
                .Range("G" & i) = wb.Sheets(Sheets.Count).Range("B3")
                .Range("H" & i) = wb.Sheets(Sheets.Count).Range("B4")[/COLOR]
            End With
            wb.Close False ' close the source workbook without saving any changes
            Set wb = Nothing ' free memory
        
        Next i
    Application.ScreenUpdating = True ' turn on the screen updating
 End Sub

Het rode gedeelte is waar de cellen worden uitgelezen omdat ik niet weet welke velden jij allemaal nodig hebt en of deze aaneengesloten staan heb ik alle cellen apart in laten lezen.
het zou wat efficiënter (kortere code) kunnen als je altijd een aaneengesloten bereik wil kopiëren.

Peterwi · 29 jun 2017

Merci, dat heb ik ondertussen verder getest en dat werkt prima.

Ik had nog (voorlopig) nog één bijkomend vraagje.

In de bestandjes staan een aantal gegevens, zoals naam en geboortedatum steeds op dezelfde plaats (rij en kolom) en dat werkt dus al zeer goed.

Echter, er zijn ook twee kolommen met de jaren en maanden ancienniteit.
Deze staan wel altijd in dezelfde kolom maar naargelang de periode dat een personeelslid in dienst is en verschillende contracten of afwezigheden heeft gehad kan dat in een verdere rij staan.

Dus in kolom G staat Jaar ancienniteit en in kolom H van dezelfde regel staat het aantal jaren ancienniteit.
Elk bestand heeft dus een variabel aantal regels en die ancienniteit is dus cruciaal.
Ik wilde met verticaal zoeken in kolom G naar Jaar Ancienniteit zoeken en dan dus zo de gegevens met het aantal jaren ancienniteit inlezen.
Met VBA weet ik dus niet hoe dat moet.

roeljongman · 29 jun 2017

kun je die Ancienniteit in een excelvoorbeeldje zetten want op basis van de beschrijving lukt het niet te bedenken wat de code moet zijn

Peterwi · 29 jun 2017

In bijlage twee voorbeeldbestandjes.
Het is telkens het laatste werkblad dat van tel is.

Oorspronkelijk wilde ik dan via verticaal zoeken de gegevens opzoeken :

=VERT.ZOEKEN("Totaal ancienniteit";A:E;4;ONWAAR) (voor de jaren ancienniteit)
=VERT.ZOEKEN("Totaal ancienniteit";A:E;5;ONWAAR) (voor de maanden ancienniteit)

De omschrijving "Totaal ancienniteit" komt maar één keer voor (niet steeds op dezelfde lijn) en zo zou ik de gegevens met verticaal zoeken kunnen vinden.

Peterwi · 4 jul 2017

Verticaal zoeken met VBA in het laatste tabblad van elke file

De onderstaande VBA werkt uitstekend op één regel na.
Het programma zoekt in het laatste werkblad van elke file van een map naar bepaalde gegevens.
Range D tot Range P zijn vaste velden in de werkbladen en die worden correct ingevuld.
Echter, in Range Q wil ik de waarde van een veld in kolom M zetten waar in kolom A het woord "TOTAAL" staat.
Omdat er in elk werkblad een variabel aantal regels zijn wil ik de waarde van het veld zoeken met verticaal zoeken.

In de onderstaande VBA is er echter een fout in de syntax.
Weet er iemand hoe dit exact moet geprogrammeerd worden?

Sub GetDataFromFiles()
Dim wb As Workbook

Application.ScreenUpdating = False ' turn off the screen updating
' Sheet1.Range("tbl_filelist[Filename]").Select
' Range(Selection, Selection.End(xlDown)).Select
cRow = 12 + Sheet1.Range("tbl_filelist[Filename]").Rows.Count
For i = 13 To cRow
' open the source workbook, read only
Set wb = Workbooks.Open(Filename:=Range("C3") & "" & Cells(i, 2), ReadOnly:=True)
' vind de laatste sheet
With Sheet1
' read data from the source workbook
.Range("D" & i) = wb.Sheets(Sheets.Count).Name
.Range("E" & i) = wb.Sheets(Sheets.Count).Range("B4")
.Range("F" & i) = wb.Sheets(Sheets.Count).Range("F4")
.Range("G" & i) = wb.Sheets(Sheets.Count).Range("M4")
.Range("H" & i) = wb.Sheets(Sheets.Count).Range("D5")
.Range("I" & i) = wb.Sheets(Sheets.Count).Range("G5")
.Range("J" & i) = wb.Sheets(Sheets.Count).Range("P1")
.Range("K" & i) = wb.Sheets(Sheets.Count).Range("B6")
.Range("L" & i) = wb.Sheets(Sheets.Count).Range("H6")
.Range("M" & i) = wb.Sheets(Sheets.Count).Range("M6")
.Range("N" & i) = wb.Sheets(Sheets.Count).Range("B7")
.Range("O" & i) = wb.Sheets(Sheets.Count).Range("G7")
.Range("P" & i) = wb.Sheets(Sheets.Count).Range("C8")
.Range("Q" & i) = wb.Sheets(Sheets.Count).ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""TOTAAL"",'[Sheets]Sheets.Count'!A:Q,13,FALSE)"
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory

Next i
Application.ScreenUpdating = True ' turn on the screen updating

End Sub
Sub UpdatePersoneelslijst()

End Sub

roeljongman · 5 jul 2017

Als ik me goed herinner wilde je zowel het jaar als de maanden van totaal Ancienitiet.
boven de .Range("Q" ..) heb ik een regel code toegevoegd die met zoeken het regelnummer vind en dan kolom M en N kopieert. het woord totaal wordt alleen gezocht in het bereik A1:A1000
en de volgende regel code plakt die waarden van de formules in kolom Q en R

loop de kolomletters van de nieuwe code nog even goed na, je voorbeeld wijkt af van het echte bestand dus kan het niet 100% testen, altijd kleine kans dat er toch een lettertje verkeerd staat.

Code:

Sub GetDataFromFiles()
Dim wb As Workbook
    
    Application.ScreenUpdating = False ' turn off the screen updating
    cRow = 12 + Sheet1.Range("tbl_filelist[Filename]").Rows.Count
        For i = 13 To cRow
            ' open the source workbook, read only
            Set wb = Workbooks.Open(Filename:=Range("C3") & "\" & Cells(i, 2), ReadOnly:=True)
            ' determine newest sheet
        
             With Sheet1
                .Range("D" & i) = wb.Sheets(Sheets.Count).Name
                .Range("E" & i) = wb.Sheets(Sheets.Count).Range("B4")
                .Range("F" & i) = wb.Sheets(Sheets.Count).Range("F4")
                .Range("G" & i) = wb.Sheets(Sheets.Count).Range("M4")
                .Range("H" & i) = wb.Sheets(Sheets.Count).Range("D5")
                .Range("I" & i) = wb.Sheets(Sheets.Count).Range("G5")
                .Range("J" & i) = wb.Sheets(Sheets.Count).Range("P1")
                .Range("K" & i) = wb.Sheets(Sheets.Count).Range("B6")
                .Range("L" & i) = wb.Sheets(Sheets.Count).Range("H6")
                .Range("M" & i) = wb.Sheets(Sheets.Count).Range("M6")
                .Range("N" & i) = wb.Sheets(Sheets.Count).Range("B7")
                .Range("O" & i) = wb.Sheets(Sheets.Count).Range("G7")
                .Range("P" & i) = wb.Sheets(Sheets.Count).Range("C8")
                    wb.Sheets(Sheets.Count).Range("M" & wb.Sheets(Sheets.Count).Range("A1", "A1000").Find(what:="Totaal").Row).Resize(, 2).Copy '
                .Range("Q" & i).PasteSpecial Paste:=xlPasteValues
            End With
            wb.Close False ' close the source workbook without saving any changes
            Set wb = Nothing ' make variable empty
        Next i
    Application.ScreenUpdating = True ' turn on the screen updating

Verticaal zoeken naar variabel werkblad

Gebruiker

Meubilair

Gebruiker

Gebruiker

Bijlagen

Gebruiker

Terugkerende gebruiker

Gebruiker

Giga Honourable Senior Member †

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Inventaris

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Bijlagen

Gebruiker

Terugkerende gebruiker

Wij waarderen jouw privacy