In Excel de verwijzing naar een cel variabel maken.

Question

Hallo allemaal,

Ik heb een excel bestand gemaakt waarin ik per jaar mijn energieverbruik en de opbrengst van de zonnepanelen bijhoudt

Op het tabblad "data" staat de hoofdtabel waarop de meterstanden staan:

kolom A|: datum,

kolom B, meterstand verbruik laag tarief;

kolom C meterstand verbruik hoog tarief;

kolom D meterstand teruglevering laag tarief;

kolom E meterstand teruglevering hoog tarief

enz...

Voor elk jaar gebruik ik een nieuw tabblad waarop het daadwerkelijk verbruik en verschil t.o.v de vorige jaren wordt berekend en weergegeven in grafieken. De meterstanden worden van het tabblad "data" gehaald.

Nu komt het::elke keer als ik voor een jaar nieuw tabblad maak doe ik dit door een kopie te maken van een bestaand tabblad. Alleen moet dan alle verwijzingen handmatig veranderen. Is het mogelijk de verwijzingen variabel te maken door bijvoorbeeld gebruik te maken van het jaartal dat in cel P2 van de sheet staat.

ik heb hierover 2 vragen:

1: Op tabblad "meterstanden 2017" staat cel "Q17" de meterstand van december 2017 die wordt opgevraagd: door "=data!B134"

Op het tabblad "meterstanden 2018" wordt in cel Q5 nu ook de meterstand december 2017 opgevraagd door "=data!B134" alleen moet ik elke keer als ik een nu een nieuw tabblad maak deze verwijzing handmatig aanpassen. Is het mogelijk om de wijzing te doen door =data!E(dan een formule waarin gebruik wordt gemaakt van het jaartal in P2). Als gezegd staat P2 de waarde van het jaar: 2018.

2. in cel Q6 staat de meterstand van januari 2018 dit wordt nu opvraagt door "=data!B135" Dit volgt op het tabblad "data" op de meterstand van december
3. (maar verwijzing Q6 t/m Q17 moet ik nu elke keer handmatig aanpassen) Is het mogelijk door bv gebruik te maken van een index kolom met als inhoud de getallen 1 t/m 12 en het jaartal "=data!B134" per maand automatisch op te hogen? 

Hieronder een stukje van het blad 'meterstanden 2018'

De maanden staan in kolom P.

De meterstanden =data!... staan in kolom Q.

En het verbruik van de maand wordt in de kolom R uitgerekend door de het verschil van bv Q6-Q5 uit te rekenen.

--------kolom (P)--------- kolom (Q) ------------------------kolom (R)

rij-5--------------------------=data!B134      <<< (dit is december van het vorige jaar)

rij-6-----januari------------=data!B135------------------------=ALS(Q6="";NB();Q6-Q5)

rij-7-----februari-----------=data!B136------------------------=ALS(Q7="";NB();Q7-Q6)

rij-8-----maart--------------=data!B137------------------------=ALS(Q8="";NB();Q8-Q7)

rij-9-----april----------------=data!B138------------------------=ALS(Q9="";NB();Q9-Q8)

rij-10----mei----------------=data!B139------------------------=ALS(Q10="";NB();Q10-Q9)

rij-11----juni----------------=data!B140------------------------ =ALS(Q11="";NB();Q11-Q10)

rij-12----juli-----------------=data!B141------------------------=ALS(Q12="";NB();Q12-Q11)

rij-13----augustus---------=data!B142------------------------=ALS(Q13="";NB();Q13-Q12)

rij-14----september-------=data!B143------------------------=ALS(Q14="";NB();Q14-Q13)

rij-15----oktober------------=data!B144------------------------=ALS(Q15="";NB();Q15-Q14)

rij-16----november--------=data!B145------------------------=ALS(Q16="";NB();Q16-Q15)

rij-14----december--------=data!B146------------------------=ALS(Q17="";NB();Q17-Q16)

En ophet blad 'data' staan: 

in rij 134 de gegevens van december 2017;

in rij 135 de gegevens van jan 2018;

in rij 136 de gegevens van febr 2018

in rij 137 de gegevens van maart 2018

enz.....

Ik geprobeerd de vraag zo duidelijk mogelijk te beschrijven en hoop dat iemand me verder kan helpen

Willem

Answer 1

Hallo Willem,

Ik voel me helemaal niet beledigd. Het is beter als iemand een oplossing zoekt die hij begrijpt dat klakkeloos een formule over te nemen.

Stel in A1 heb je het jaartal (2018)

Dan kan je alles in 1 formule stoppen.

=VERT.ZOEKEN(DATUM(2018;RIJEN($5:5);0);data!$A:$K;5;ONWAAR)

Zoals ik al zei, dag 0 van de maand is de laatste dag van vorige maand. Hetzelfde als wat LAATSTE.DAG doet.

Elk argument van een functie mag op zichzelf een functie zijn. Je hoeft dus niet eerst de laatste dag te berekenen om VERT.ZOEKEN te gebruiken.

Als volgende formule zou je zelfs alles in 1 formule kunnen combineren.

=ALS(VERT.ZOEKEN(DATUM(2018;RIJEN($5:6);0);data!$A:$K;5;ONWAAR)=0;NB();VERT.ZOEKEN(DATUM(2018;RIJEN($5:6);0);data!$A:$K;5;ONWAAR)-D5)

Answer 2

Dag Willem,

Dat zou moeten lukken met de functie INDIRECT maar om je verder te helpen heb ik wel zicht op je 2 bladen nodig.

Answer 3

Hallo JP Ronse,

Bedankt voor je reactie. Het idee van INDIRECT heb ik zelf ook al bekeken, maar ik kon er niet uitkomen. Misschien heb jij een beter idee hoe het wel moet lukken met INDIRECT. Zou mooi zijn.

Nogmaals de gedachte is als ik een nieuw tabblad maak voor dat de formule in R5 gebruik gemaakt wordt van de waarde in cel Q2 (om in het geval bijlage (2018) te verwijzen naar "data!E134. Daar staat het jaar tal. En in het jaar 2019 naar "data!E146 enz.

En in de cellen Q6 t/m @17 moet een soort ophoog functie komen die de waarde elke maand ophoogt van E134 naar E135 naar E136 enz t/m december   

Hieronder screenshots van de tabbladen (met de formules zichtbaar) "data" en "meterstanden 2018"

Alvast bedankt voor het meedenken

Answer 4

de Screenshots ging niet helemaal goed dus dan maar in een Word document.

Answer 5

Gaat zo te zien ook niet goed, dan maar een keer als PDF