Overzicht maken van gegevens uit meerdere werkbladen

[BRUSC]

Ik zit met het volgende:

Ik wil een overzicht maken van gegevens die over meerdere werkbladen verspreid staan. De werkbladen hebben allemaal een unieke naam maar de gegevens in deze bladen zijn wel overal hetzelfde ingericht. Nu wil ik in een nieuwe sheet een overzicht van een aantal van deze gegevens maken. (zie bijgaand voorbeeld). Nu kan ik natuurlijk gewoon stuk voor stuk een verwijzing naar de betreffende cel in het betreffende werkblad maken (dat heb ik nu als voorbeeld voor de eerste kolom gedaan) maar ik heb meer dan 100 werkbladen met daarop tientallen onderdelen die ik wil samenvatten. Dan ben ik dus wel even bezig...

Kortom: er moet toch een makkelijke manier zijn om dit automatisch te laten doen. De plaats waar de gegevens gezocht moeten worden blijft per keer gelijk alleen het werkblad verandert.

Wie weet hoe je dit kan oplossen?

 

[snb]

Zet in werkblad Index Cel A2 enige tekst.
Verwijder in werkblad Index alle gegevens in kolom A vanaf rij 2.

Draai vervolgens deze macro:

Sub integratie()
  For Each sh In Sheets
    If sh.Name <> "Index" Then Sheets("Index").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 8) = Split(sh.Name & "|" & Join(WorksheetFunction.Transpose(sh.Range("B4:B10")), "|"), "|")
  Next
End Sub

De enige aanpassingen zitten in het aantal rijen dat per blad moet worden gelezen.
In het voorbeeld B4:B10 (7 rijen), daarmee correspondeert resize(,8)

Voor het aanpassen is deze code simpeler

Sub integratie()
  For Each sh In Sheets
    If sh.Name <> "Index" Then Sheets("Index").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, [COLOR="red"]8[/COLOR]) = Split(sh.Name & "|" & Join(WorksheetFunction.Transpose(sh.Range("B4").resize([COLOR="Red"]7[/COLOR])), "|"), "|")
  Next
End Sub

Nu hoef je in deze code alleen maar de rood gemarkeerde cijfers aan te passen. Het eerste getal is altijd 1 groter dan het 2e cijfer, omdat niet alleen de gegevens moeten worden ingelezen, maar ook de naam van het werkblad, waarin de gegevens staan.

 

[popipipo]

Of als je liever formules ipv vba gebruikt
De indirect formule kun je eenvoudig door kopieeren.

 

[BRUSC]

Bedankt

Zeer bedankt voor de tips.

De formule oplossing ben ik nu mee bezig maar ik blijf een #verw! fout houden.

Ik heb weer een voorbeeldje bijgevoegd. Wat doe ik hier nog niet goed?

Bij voorbaat dank voor jullie moeite.

 

[oeldere]

indirect

Beste Brusc,

ik heb het bestandje met de formule van popipipo aangepast. (zie bijlage).

In de tabbladen in cel a1 de naam van het tabblad vermeld (met een underscore tussen de tekst en getal).

vervolgens is bij invoegen naam de naam die gelijk is aan cel a1 ingevoerd (KMI_6003).

vervolgens is bij blad 1 de tekst in a3 en a4 aangepast aan dezelfde tekst in a1.

P.s. de formules zijn ongewijzigd gebleven.

heb je vragen, laat het gewoon even weten.

Met vriendelijke groeten,

Oeldere

 

[BRUSC]

Naam invoegen

Beste Oeldere,

Inderdaad als ik "Invoegen - naam" kies en dan het bereik op het betreffende werkblad aangeef dan werkt de formule wel. Alleen heb ik in de echte situatie een paar honderd werkbladen en ben ik zo nog altijd handmatig al mijn werkbladen aan het ingeven.

Waarom zoekt hij niet automatisch in het goede werkblad als ik de naam van het werkblad exact gelijk hou aan de naam in de index?

 

[oeldere]

snb (post #2) geeft een VBA oplossing.

popipipo (post #3) geeft een oplossing met formules.

jij geeft aan dat post #3 in jouw model fout gaat.

ik wijzig deze zodat deze goed gaan.

waarschijnlijk kan dit met de functie (indirect) ook wel.

maar anders kies voor de VBA-oplossing
(daar heb ik echter te weinig kaas van gegeten).

Met vriendelijke groeten,

Oeldere

 

[oeldere]

indirect en sheets

toch nog een poging (zie bijlage)

met de formule:

=VLOOKUP($B$2;INDIRECT("'"&$A3&"'!$A$3:$B$50");2;0)

deze heb ik van popipipo (met dank). :thumb:
het idee van de hyperlinks heb ik ook van popipipo. :thumb:

onder het motto => beter goed gejat, dan slecht verzonnnen!

heb je hier vragen over, laat het gewoon even weten.

met vriendelijke groeten,

Oeldere

 

[BRUSC]

Eruit

Bedankt voor de reacties, ik ben een heel stuk verder. Formules blijven mijn voorkeur houden omdat deze automatisch worden doorgerekend.

Ik ga de moeite nemen om alle werkbladen eenmalig te "namen" en dan moet het geod komen.

Nogmaals bedankt voor de hulp.

 

[oeldere]

Bedankt voor de reacties, ik ben een heel stuk verder. Formules blijven mijn voorkeur houden omdat deze automatisch worden doorgerekend.

Ik ga de moeite nemen om alle werkbladen eenmalig te "namen" en dan moet het geod komen.

Nogmaals bedankt voor de hulp.

zie post #8.

wat bedoel je met namen?

namen geven in de tabbladen is i.v.m. indirectformule niet meer nodig!

laat je nog even wat weten?

Met vriendelijke groeten,

Oeldere

 

[BRUSC]

Namen

Ik bedoel dat ik bij Invoegen --> Naam --> Definieren eerst het betreffende tabblad moet definieren met exact dezelfde naam als het tabblad zelf en dan de hele sheet als verwijzing aangeven.

Als ik dat doe werkt de formule (zie verschil bij KMI_6006 op screencapture 1 en 3) en anders blijf ik #VERW! houden zoals je ziet in de sheet.

Zie ook 3 screencaptures.

 

[oeldere]

volgens mij heb ik wel eens gezien, dat de naam op de tabbladen ook automatisch kunnen worden opgehaald. Zeker nu je veel tabbladen moet benoemen is het misschien verstandig hierover eerst nog een vraag over te stellen.

zelf heb ik deze gegevens niet paraat (anders gebruik de zoekfunctie eens).

Met vriendelijke groeten,

Oeldere

 

[snb]

Kun je toelichten waarom je afziet van het gebruik van deze suggestie ?

 

[Gerald Baalhuis]

kan dit ook voor meerdere cellen?

Beste snb,

Kan je dit stukje VBA zodanig aanpassen dat op het blad "Index"
in kolom A de inhoud van cel B2 uit alle andere werkbladen geplaatst wordt
in kolom B de inhoud van cel B12 uit alle andere werkbladen geplaatst wordt
In kolom C de inhoud van cel B14 uit alle andere werkbladen geplaatst wordt

Alvast bedankt voor je reactie

Gerald

 

[puppie]

Zeer belegen topic.
Daarom maar voorzien van een slot.
Maak even een eigen vraag voor jouw probleem.