Grootte bereik verwijzing afhankelijk van oorsprong

[RAFCRIK]

Hallo,

Ik maak een simpel werkmapje om mijn kosten bij te houden. Ik heb me voor het voorbeeld beperkt tot jan-maa. De bedoeling is om op ieder tabblad de info in te voeren (per maand dus), en op het eerste tabblad een overzicht te hebben.

1) Ik heb de bereiken op de maandtabbladen namen gegeven (kost-omschrijving + prijs, een bereik van 2 kolommen breed dus), maar ik krijg dat al niet geplakt in een willekeurige cel. (ik heb het getest op de afzonderlijke tabbladen, maar: #WAARDE!)

2) De verwijzing vanuit het eerste tabblad naar de bereiken, had ik graag gedaan vanuit een titel. Ik gebruik hiervoor INDIRECT, een toepassing die rechtstreeks beschreven staat in de help-file. Het levert echter een andere fout op: #VERW!

3) Het aantal rijen dat iedere maand in het overzicht (1ste tabblad dus) moet innemen, hangt natuurlijk af van de invoer op de afzonderlijke tabbladen. Zo heeft januari 1 uitgavepost, maar februari 3.
Is het mogelijk om Excel bij het overzichtautomatisch 1 rij te laten gebruiken bij januari en 3 bij februari?

Een hele boterham, maar voor jullie heeft Excel al lang geen geheimen meer.

Dank bij voorbaat!

 

[Roncancio]

Ik heb het bestand aangepast.
Het punt is dat je de formule in de cel heb gezet ipv bij Datavalidatie.
Verder zat er een foutje in de formule waardoor je niet het gewenste resultaat kon krijgen.
Tevens heb ik de verwijzingen aangepast.
Tot slot nog de formule voor de bedragen toegevoegd zodat je bij het maken van een keuze je bijbehorende bedrag ernaast kunt vinden.
Deze formule is verbonden met de tekst in de A-kolom. Dus de naam van de maand wordt in de gelijknamige werkblad gezocht.

Met vriendelijke groet,


Roncancio

 

[RAFCRIK]

Bedankt, maar dat was niet helemaal wat ik bedoelde. Ik wil namelijk alle uitgaven in het overzicht zetten. De keuzelijst is dus niet nodig, het gaat er mij vooral om, om automatisch alle uitgaven te zien te krijgen, met één witregel eronder en het subtotaal. Er moet dus verschoven worden, of rijen ingevoegd, en dat automatisch. Snap je?

P.S.: Kan je me even uitleggen hoe ["'"&A2&"'!B1"] samengesteld is? (komt uit de INDIRECT)

 

[Roncancio]

Ik heb een macro geschreven die de gegevens van de maanden uit de werkbladen haalt en deze gegevens in het kwartaaloverzicht plaatst.

Sub Kwartaal()
Dim lsRij As Long
Dim lZRij As Long
Dim dTel As Double
    lZRij = 2
    lsRij = 2
    Worksheets("Kwartaaloverzicht").Range("A2:C10000").Font.Bold = False
    Worksheets("Kwartaaloverzicht").Range("A2:C10000").Font.Italic = False
    For Sh = 2 To ActiveWorkbook.Worksheets.Count
        Worksheets("Kwartaaloverzicht").Range("A" & lsRij).Value = Worksheets(Sh).Name
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = "Omschrijving"
        Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = "Kost"
        Worksheets("Kwartaaloverzicht").Range("A" & lsRij & ":C" & lsRij).Font.Bold = True
        lsRij = lsRij + 1
        While Worksheets(Sh).Range("A" & lZRij).Value <> ""
            Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = Worksheets(Sh).Range("B" & lZRij).Value
            Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = Worksheets(Sh).Range("C" & lZRij).Value
            dTel = dTel + Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value
            lsRij = lsRij + 1
            lZRij = lZRij + 1
        Wend
        lsRij = lsRij + 1
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = "Subtotaal"
        Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = dTel
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij & ":C" & lsRij).Font.Italic = True
        lZRij = 2
        dTel = 0
        lsRij = lsRij + 2
    Next
End Sub

P.S.: Kan je me even uitleggen hoe ["'"&A2&"'!B1"] samengesteld is? (komt uit de INDIRECT)

Met INDIRECT kan je indirect verwijzen naar een bepaalde cel.
Dus in bovenstaand geval wordt de waarde weergegeven van cel B1 in het werkblad met de naam die overeenkomt met cel A2.
Stel in cel A2 staat januari, dan is het dus de cel B1 van het werkblad januari.

Met vriendelijke groet,


Roncancio

 

[RAFCRIK]

Ik heb een macro geschreven die de gegevens van de maanden uit de werkbladen haalt en deze gegevens in het kwartaaloverzicht plaatst.

Sub Kwartaal()
Dim lsRij As Long
Dim lZRij As Long
Dim dTel As Double
    lZRij = 2
    lsRij = 2
    Worksheets("Kwartaaloverzicht").Range("A2:C10000").Font.Bold = False
    Worksheets("Kwartaaloverzicht").Range("A2:C10000").Font.Italic = False
    For Sh = 2 To ActiveWorkbook.Worksheets.Count
        Worksheets("Kwartaaloverzicht").Range("A" & lsRij).Value = Worksheets(Sh).Name
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = "Omschrijving"
        Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = "Kost"
        Worksheets("Kwartaaloverzicht").Range("A" & lsRij & ":C" & lsRij).Font.Bold = True
        lsRij = lsRij + 1
        While Worksheets(Sh).Range("A" & lZRij).Value <> ""
            Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = Worksheets(Sh).Range("B" & lZRij).Value
            Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = Worksheets(Sh).Range("C" & lZRij).Value
            dTel = dTel + Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value
            lsRij = lsRij + 1
            lZRij = lZRij + 1
        Wend
        lsRij = lsRij + 1
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij).Value = "Subtotaal"
        Worksheets("Kwartaaloverzicht").Range("C" & lsRij).Value = dTel
        Worksheets("Kwartaaloverzicht").Range("B" & lsRij & ":C" & lsRij).Font.Italic = True
        lZRij = 2
        dTel = 0
        lsRij = lsRij + 2
    Next
End Sub

Zomaar uit de losse pols? Sjiek. Moet ik die in "ThisWorkbook" plaatsten, of in "Blad1"?

Met INDIRECT kan je indirect verwijzen naar een bepaalde cel.
Dus in bovenstaand geval wordt de waarde weergegeven van cel B1 in het werkblad met de naam die overeenkomt met cel A2.
Stel in cel A2 staat januari, dan is het dus de cel B1 van het werkblad januari.

Het ging mij vooral om de aanhalingstekens en apostrofes. De apostrofes en het uitroepteken begrijp ik (van het werkblad), maar de aanhalingstekens en de ampersanten niet? ["'"&A2&"'!B1"]

 

[Roncancio]

Zomaar uit de losse pols? Sjiek. Moet ik die in "ThisWorkbook" plaatsten, of in "Blad1"?

Geen van beiden.
De macro moet in een module geplaatst worden. Bovenaan in het Visual Basic Editor menu zie je Invoegen en daarin Module.
Als je zelf een macro maakt via bijv. macro opnemen dan wordt deze ook in een module geplaatst.

Het ging mij vooral om de aanhalingstekens en apostrofes. De apostrofes en het uitroepteken begrijp ik (van het werkblad), maar de aanhalingstekens en de ampersanten niet? ["'"&A2&"'!B1"]

De apostrofes zijn tekst dus tussen "".
De ampersanten dienen om Excel duidelijk te maken dat het apart is en wordt toegevoegd.
Het is hetzelfde principe als:
A1 = "zaterdag"
B1= "Het is vandaag " & A1
Uitkomst: Het is vandaag zaterdag

Met vriendelijke groet,


Roncancio

 

[RAFCRIK]

Ik begrijp het.

Over het bestandje: Ik had gehoopt hier een oplossing te vinden die ik later naar believen zou kunnen aanpassen. Ik heb echter geen inzicht in macro's, dus ik zal weer beroep op jullie (jou) moeten doen.

In het bijgevoegd bestand heb ik alles zo georchestreerd zoals het moet zijn. De macro moet werken vanaf rij 14 naar beneden. Zou je hem even kunnen aanpassen, als je wil? Bedankt!

(Het bestand is te groot: ik heb het naar mijn webruimte geüpload: http://users.telenet.be/RAFCRIK/Boekhouding.xlsm)

 

[Roncancio]

Kan je het bestand naar mijn email adres sturen, zodat ik het bij kan werken?

Met vriendelijke groet,


Roncancio