Gegevens van ene blad verwerkt in ander blad binnen zelfde werkmap

[ekine]

Beste forumers,

Ik heb een excel bestand waarvan ik aan de hand van een waarde wil dat er op een andere tabblad komt te staan welke relatie er betreft tussen deze waarde. Als voorbeeld heb ik een excelbestand in de bijlage.
Blad1 is het 'invoerblad'. Daar kan je per datum bij een persoon een v neerzetten als deze aanwezig was. Op blad2 vind je hoe de regel er uit moet komen te zien. Dus je ziet per datum en per persoon op welke datum hij/zij aanwezig was. Ik ben het eens dat blad2 niet de meest logische vorm heeft maar op deze manier is het makkelijker om deze waarden naar Access te importeren.

Mijn vraag is hoe kan ik er voor zorgen dat als er een vinkje bij een persoon/datum wordt gezet dat ik dat op blad2 terug kan zien zoals het er staat (met inachtneming dat een persoon ook één keer aanwezig kan zijn in de gehele reeks en dus 1x voorkomt in dat lijstje i.p.v. meerdere malen).

Ik zat te denken aan een macro maar heb daar niet echt verstand van.
Ik heb een Bekijk bijlage test.xls

 

[JeanPaul28]

in cel c1

=INDEX(Blad1!$B$2:$E$6;VERGELIJKEN(A3;Blad1!$A$2:$A$6;0);VERGELIJKEN(B3;Blad1!$B$1:$E$1;0))

 

[ekine]

in cel c1

=INDEX(Blad1!$B$2:$E$6;VERGELIJKEN(A3;Blad1!$A$2:$A$6;0);VERGELIJKEN(B3;Blad1!$B$1:$E$1;0))

Ik heb er naar gekeken maar het is toch niet helemaal wat ik bedoel. Stel het test.xls bestand dat ik op blad1 in cel D6 een "v" toevoeg, dan hoor ik van blad2 in de kolom A16 "Persoon, E" en in kolom B16 "15-12-2016" te zien. Dus als je in het invulblad een "v" toevoegd, dan komt er in blad2 automatisch een regel bij. In het verlengde daarvan: stel je doet bij C2 van blad1 een "v" dan in blad2 dit moeten terug zien als "Persoon, A" in kolom A5 en "8-12-2016" in B5. De vorige waarde van A5 en B5 gaan dan één rij naar beneden (of de nieuwe waarde komt onderin een lege regel te staan en A5 en B5 behouden hun oude waarde)

 

[HSV]

Ik heb van de gegevens in Blad1 een tabel gemaakt zodat het onderhouden van deze code eigenlijk niet nodig is mocht je de tabel uitbreiden.
Bij het plaatsen van een 'v' of het verwijderen ervan wordt het bijgewerkt en gesorteerd in Blad2 d.m.v. een macro.

Test het maar eens.

 

[JeanPaul28]

bij mij doe ie het

 

[MarcelBeug]

En nog een formule-alternatief in Blad3, eveneens met een tabel voor de gegevens in Blad1, zodat bij uitbreiding van rijen/kolommen de formules nog steeds werken.

Kolom A is een hulpkolom:
A1 bevat het aantal v-tjes (oftewel het aantal benodigde regels).
A2 is een matrixformule (invoer afgesloten met Ctrl+Shift+Enter) die de rijen (*1000) en kolommen teruggeeft van de posities van de v-tjes.
Bijvoorbeeld 1002 betekent dat in de eerste (data)regel van de tabel in kolom 2 een "v" staat.
B2 geeft de naam van de persoon op de rij die aangegeven wordt door de duizendtallen in A2
C2 geeft de datum in de kolom die aangegeven wordt door het kolomnummer in A2 (oftewel de rest als je A2 deelt door 1000)
In het voorbeeld zijn de formules op regel 2 gekopieerd naar beneden tot en met regel 31.
De formules zijn zodanig opgebouwd dat ze nog steeds werken, ook al zou je de hele tabel verplaatsen (c.q. kolommen vóór of rijen boven de tabel invoegen).

A1 =AANTAL.ALS(Aanwezigheid;"v")
A2 =ALS(RIJEN(A$2:A2)>$A$1;"";KLEINSTE(ALS(Aanwezigheid="v";1000*(RIJ(Aanwezigheid)-RIJ(INDEX(Aanwezigheid;1;1))+1)+KOLOM(Aanwezigheid)-KOLOM(INDEX(Aanwezigheid;1;1))+1);RIJEN(A$2:A2)))
B2 =ALS($A2="";"";INDEX(Aanwezigheid;INTEGER(A2/1000);1))
C2 =ALS($A2="";"";DATUMWAARDE(INDEX(Aanwezigheid[#Kopteksten];REST($A2;1000))))

Edit: bestand toegevoegd. Let: de formules zijn niet compatibel met Excel 97/2003 zoals de extensie xls suggereert.

 

[ekine]

Ik heb van de gegevens in Blad1 een tabel gemaakt zodat het onderhouden van deze code eigenlijk niet nodig is mocht je de tabel uitbreiden.
Bij het plaatsen van een 'v' of het verwijderen ervan wordt het bijgewerkt en gesorteerd in Blad2 d.m.v. een macro.

Test het maar eens.

Helemaal goed. En stel dat ik op de horizontale as de datum wil vervangen door tekst, dan pas ik de celtype aan, maar welke functie in VSB moet ik dan gebruiken?

 

[ekine]

En nog een formule-alternatief in Blad3, eveneens met een tabel voor de gegevens in Blad1, zodat bij uitbreiding van rijen/kolommen de formules nog steeds werken.

Kolom A is een hulpkolom:
A1 bevat het aantal v-tjes (oftewel het aantal benodigde regels).
A2 is een matrixformule (invoer afgesloten met Ctrl+Shift+Enter) die de rijen (*1000) en kolommen teruggeeft van de posities van de v-tjes.
Bijvoorbeeld 1002 betekent dat in de eerste (data)regel van de tabel in kolom 2 een "v" staat.
B2 geeft de naam van de persoon op de rij die aangegeven wordt door de duizendtallen in A2
C2 geeft de datum in de kolom die aangegeven wordt door het kolomnummer in A2 (oftewel de rest als je A2 deelt door 1000)
In het voorbeeld zijn de formules op regel 2 gekopieerd naar beneden tot en met regel 31.
De formules zijn zodanig opgebouwd dat ze nog steeds werken, ook al zou je de hele tabel verplaatsen (c.q. kolommen vóór of rijen boven de tabel invoegen).

A1 =AANTAL.ALS(Aanwezigheid;"v")
A2 =ALS(RIJEN(A$2:A2)>$A$1;"";KLEINSTE(ALS(Aanwezigheid="v";1000*(RIJ(Aanwezigheid)-RIJ(INDEX(Aanwezigheid;1;1))+1)+KOLOM(Aanwezigheid)-KOLOM(INDEX(Aanwezigheid;1;1))+1);RIJEN(A$2:A2)))
B2 =ALS($A2="";"";INDEX(Aanwezigheid;INTEGER(A2/1000);1))
C2 =ALS($A2="";"";DATUMWAARDE(INDEX(Aanwezigheid[#Kopteksten];REST($A2;1000))))

Edit: bestand toegevoegd. Let: de formules zijn niet compatibel met Excel 97/2003 zoals de extensie xls suggereert.

Interessant, maar ook hier stel dat de horizontale as tekst heeft, hoe moet je dat in jouw formules aanpassen?

 

[MarcelBeug]

Dan kun je de DATUMWAARDE uit de formule in C2 (en gekopieerd naar beneden) verwijderen:

C2 =ALS($A2="";"";INDEX(Aanwezigheid[#Kopteksten];REST($A2;1000)))

 

[HSV]

Geheel nieuwe code als alles tekst of een mix is.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sn, sq, i As Long, j As Long, n As Long
If Not Intersect(Target, Me.ListObjects(1).Range) Is Nothing And Target.Count = 1 Then
sn = Me.ListObjects(1).Range
ReDim sq(1, 0)
With Sheets("Blad2")
 .Cells(1).CurrentRegion.ClearContents
        For i = 1 To UBound(sn)
         For j = 1 To UBound(sn, 2)
            If LCase(sn(i, j)) = "v" Then
              sq(0, n) = sn(i, 1)
               If IsDate(sn(1, j)) Then
                 sq(1, n) = CLng(CDate(sn(1, j)))
               Else
                 sq(1, n) = sn(1, j)
               End If
                        n = n + 1
               ReDim Preserve sq(1, n)
            End If
          Next j
        Next i
   .Cells(2, 1).Resize(UBound(sq, 2), 2) = Application.Transpose(sq)
 End With
 End If
End Sub

 

[ekine]

Hoi HSV, bedankt voor je oplossing. Wat moet ik in de code aanpassen als ik een bereik heb dat tot en met kolom EX gaat en tot en met rij 200? Want ik krijg een melding dat deze range buiten het bereik ligt.

 

[HSV]

Als het een Listobject is hoef je niets te veranderen aan de code.

 

[ekine]

Gelukt. Nu nog twee dingen waar ik mee loop te stoeien:
1. Het is goed dat een nieuw 'record' op blad2 elke keer onder de eerstvolgende lege regel komt te staan. Maar stel dat ik een 'v' heb geplaatst in cel B4, dan staat er bijvoorbeeld op regel 4 van blad2 de bijbehorende waarde. Maar stel dat ik de 'v' in cel B4 weghaal, hoe zorg ik er dan voor dat regel4 van blad2 dan ook wordt gewist en je dus een lege regel overhoudt (want de nieuwe 'v' tjes die je invoerd worden altijd onder de eerstvolgende lege regel gezet)
2. Ik wil een lijst maken, de categorie selecteren en dan de v'tjes invullen bij de personen die in die lijst zitten (iets met drop down). Dus persoon A en B horen tot categorie Appel dus als ik in blad1 bij dropdown 'Appel' kan selecteren dat ik alleen deze personen met bijbehorende 'v'tjes zie en deze kan toevoegen of verwijderen.