[Excel] Index van werkbladgegevens in kolom per werkblad

[killerflappy]

Ik heb een flink aantal werkbladen in mijn bestand. Op ieder werkblad staan gegevens van een bepaalde persoon. Ik zou graag op het werkblad Blad1 alle informatie verzamelen in een soort index. Hiervoor wil ik de waarden uit cel B2 uit alle werkbladen onder elkaar krijgen zonder dat ik alle verwijzingen zelf moet maken. Een verwijzing maken met =Blad2!B2 lukt wel, maar ik wil deze verwijzing dus niet voor ieder blad zelf in moeten voeren. Ik heb op allerlei manieren geprobeerd om de formule te kopiëren, door te voeren, ik heb het met $ tekens geprobeerd maar niks lukt.

Ik heb met =indirect gewerkt. Niets opgeleved

[WillyF]

met VBA zou ik het wel weten te doen:

code:

Option Explicit

Dim lngAantalSheets As Long
Dim rnKopcel As Range

Dim wsGegeven As Worksheet
Dim celGegeven As Range
Dim strGegeven As String
Dim i As Integer

Dim celLijst As Range


Sub ZoekGegevens()
    Set rnKopcel = Blad1.Range("A2")
    lngAantalSheets = ThisWorkbook.Worksheets.Count 'Aantal Werkbladen wordt geteld
    
    'Ik ga ervan uit, dat op Werkblad 1 = Blad1 de gegevens moeten komen te staan
    'van Blad 2 t/m zoveel
    
    For i = 2 To lngAantalSheets
        Set wsGegeven = ThisWorkbook.Worksheets(i)  'werkblad met gegevens wordt vastgesteld
        Set celGegeven = wsGegeven.Range("B2")  'cel met gegeven wordt vastgesteld
        
        strGegeven = celGegeven         'gegeven wordt in een string gestopt
        
        Set celLijst = rnKopcel.Offset(i, 0)    'cel waar dat dingetje moet komen op blad 1 wordt vastgesteld
        celLijst = strGegeven                   'gegeven wordt in die cel gestopt.
    Next i
    
End Sub

Via menubalk Extra --> Macro --> Macro's zie je dan Zoekgegevens in de lijst staan, en die kan worden uitgevoerd.

[ Voor 9% gewijzigd door WillyF op 06-10-2005 17:37 ]

[Verwijderd]

zonder vba kan je aan het werk gaan met indirect, cel("bestandsnaam") en info("aantal_bladen").

[Verwijderd]

Probeer dit eens in Blad 1 en sleep de inhoud van de cel dan naar beneden:

=INDIRECT(ADRES(2,2,1,1,"Blad"&RIJ()),WAAR)

Dit voorbeeld gaat er vanuit dat het eerste item van de index in rij 2 staat. Als dat anders moet zijn moet je de waarde van RIJ() aanpassen (bijv. RIJ()+1).

Het lijk erop dat de INDIRECT functie niet direct met verwijzingen naar andere werkbladen kan omgaan (of we maken allebei een fout met de syntax). De ADRES functie maakt de verwijzing in een text string die INDIRECT wel kan verwerken.

[Lustucru]

Verwijderd schreef op donderdag 06 oktober 2005 @ 17:46:
[...]

Het lijk erop dat de INDIRECT functie niet direct met verwijzingen naar andere werkbladen kan omgaan (of we maken allebei een fout met de syntax). [...]

code:

=INDIRECT("Blad" & RIJ() &"!A1")

werkt hier iig wel. Tenminste, als alle bladen een naam blad1,blad2,...,bladn hebben.
Is dat niet het geval dan kun je met een paar regels VBA vrij eenvoudig de bladnaam ophalen:

Visual Basic:

Function BladNaam(index As Long) as string
    On Error GoTo nop
    BladNaam = Sheets(index).Name
    Exit Function
nop:
    BladNaam = "#err"
End Function

Op het moment dat je dit in een module hebt gestopt kun je in het werkblad met

code:

=indirect(bladnaam(rij()) & "!A1")

verwijzen naar alle cellen A1 op de opeenvolgende bladen.

[WillyF]

Misschien een stomme vraag, maar hoe verwijzen jullie naar een reeks werkbladen,
als die niet meer Blad2, Blad3, Blad4 etc meer heten, maar bijv.
Donald_Duck, Mickey_Mouse, Boris_Boef etc...

edit:
Ok, Niesje was me net voor... Ik dacht even dat er een functie was, die dat deed, waar ik al die tijd overheen heb gekeken.

[ Voor 27% gewijzigd door WillyF op 06-10-2005 18:20 ]

[Verwijderd]

WillyF schreef op donderdag 06 oktober 2005 @ 18:19:
Misschien een stomme vraag, maar hoe verwijzen jullie naar een reeks werkbladen,
als die niet meer Blad2, Blad3, Blad4 etc meer heten, maar bijv.
Donald_Duck, Mickey_Mouse, Boris_Boef etc...

edit:
Ok, Niesje was me net voor... Ik dacht even dat er een functie was, die dat deed, waar ik al die tijd overheen heb gekeken.

die functie is cel("bestandsnaam"), evenwel moet je andere functies gebruiken om hieruit enkel de bladnaam te puren.

[WillyF]

kijk kijk, weer wat geleerd...
'k Zal er eens naar kijken (als er tijd is ... )

[Lustucru]

Verwijderd schreef op donderdag 06 oktober 2005 @ 18:57:
[...]

die functie is cel("bestandsnaam"), evenwel moet je andere functies gebruiken om hieruit enkel de bladnaam te puren.

Nofi, maar dat is niet correct. Cel("bestandsnaam") levert de naam van de werkmap, maar niet de naam van een werkblad. Afaik kent Excel (nog) geen werkbladfunctie om naar werkbladen te verwijzen anders dan per naam; de enige manier om dat toch te doen is met een (eigen)gemaakte functie.

[killerflappy]

De formule INDIRECT kijkt naar 1 vaste cel, als ik daarna rijen wil invoegen kijkt de formule naar de verkeerde cel. Ik wil een formule die ik kan doortrekken zodat de werkbladen verzameld in 1 bestand en ik wil in de verschillende werkbladen een rij kunnen invoegen.

[onkl]

killerflappy schreef op vrijdag 07 oktober 2005 @ 12:20:
De formule INDIRECT kijkt naar 1 vaste cel, als ik daarna rijen wil invoegen kijkt de formule naar de verkeerde cel. Ik wil een formule die ik kan doortrekken zodat de werkbladen verzameld in 1 bestand en ik wil in de verschillende werkbladen een rij kunnen invoegen.

Standaardtrucje hiervoor:
In plaats van

code:

=indirect(bladnaam(rij()) & "!A1")

gebruik je, stel dat deze formule in cel A3 staat:

code:

=indirect(bladnaam(rij()) & "!A" & row(A3)-2)

De verwijzing bij Row moet altijd naar de cel waarin je de formule zet gaan. Met die twee kan je verder instellen.

[Verwijderd]

Niesje schreef op donderdag 06 oktober 2005 @ 20:01:Nofi, maar dat is niet correct. Cel("bestandsnaam") levert de naam van de werkmap, maar niet de naam van een werkblad. Afaik kent Excel (nog) geen werkbladfunctie om naar werkbladen te verwijzen anders dan per naam; de enige manier om dat toch te doen is met een (eigen)gemaakte functie.

excel, worksheet name ophalen met functie
maar wat wel zou kunnen is dat deze niet bruikbaar is in de context van de TS.

[ Voor 9% gewijzigd door Verwijderd op 07-10-2005 12:34 ]

[Lustucru]

Ik krijg met cel("bestandsnaam") noway de naam van een werkblad ( tabblad)
Aaargh, cel("bestandsnaam") geeft enkel werkmapnaam, cel("bestandsnaam";[verw]) levert wel een tabbladnaam op. Maar idd, in deze context weinig bruikbaar.

[ Voor 52% gewijzigd door Lustucru op 07-10-2005 15:16 ]

[Verwijderd]

probeer het eens.