Invoegen Rij waarbij verwijzing naar cel op ander blad niet wil doen wat ik verwacht

[Noudthy]

Ik ben een relatief eenvoudige Macro/VBA aan het maken, ik loop nu echter tegen een onverwacht probleempje aan.

Kort:
Formules en verwijzingen naar een cel op het zelfde werkblad, wijzigen wel als regel tussengevoegd word.
Verwijzingen in een cel naar de inhoud op een ander werkblad wijzigt niet in de regel die naar beneden schuift na het tussenvoegen.
(als of er een $ voor staat)

Lang:
De volgende stappen doet de macro:

Selecteer alle Werkbladen
Stelt vraag boven welk rij nummer een regel toegevoegd moet worden (Inputbox)
Selecteert betreffende regel en voegt nieuwe regel in.

Stel:
Ik geef aan regel 2 waarboven nieuwe rij ingevoegd moet worden. Regel 2 wordt dus regel 3 en regel 2 is alle cellen leeg
Gevolg:
Formule in A1 is Som(B1:F1)
Cal A2 en alle cellen op de regel 2 is/zijn leeg
Formule in A3 is Som(B3:F3)
Dit is dus helemaal goed MAAR bij verwijzingen naar celinhouden op andere werkbladen doet hij dit niet.

G1 is Blad2!A1
G2 is leeg
G3 is Blad2!A2

In G3 had ik verwacht Blad2!A3 Net als bij formules of verwijzigen binnen eigen werkblad

Waar moet ik een Spreekwoordelijk (of echt?) vinkje zetten of hoe moet ik cel of verwijzing aanpassen opdat in in G3 " Blad2!A3 " en niet Blad2!A2 automatisch na invoegen van een regel komt?



Bedankt al vast

Noudthy

 

[Peter B]

Je kunt de volgende formule gebruiken in kolom G:

=INDIRECT(ADDRESS(ROW();1;1;0;"Blad2");FALSE)

Hiermee leg je een referentie tussen de rij op de tab waar de verwijzing staat en Blad2.

 

[Noudthy]

Je kunt de volgende formule gebruiken in kolom G:

=INDIRECT(ADDRESS(ROW();1;1;0;"Blad2");FALSE)

Hiermee leg je een referentie tussen de rij op de tab waar de verwijzing staat en Blad2.

Dank je voor je reactie het kolom G verhaal was slechts als het voorbeeld.
Was slechts om uit te leggen.

Ik ken het INDIRECT gebeuren nog niet, dus korte uitleg wat de formule doet etc zou welkom zijn.

De werkelijke inhoud van betreffende velden is:

=SOM(Blad2!V120+Blad3!V120+Blad4!V120+Blad5!V121)

Edit:
Het is een bestaand programma, niet door mij gemaakt. Ik zou het dan ook heel anders opzetten.
Wijzigen of een nieuwe bouwen is niet wenselijk (omvang) en ik hoop dat te voorkomen

 

[Peter B]

Geeft weer aan dat een voorbeeld bijvoegen altijd verstandig is ...

Indirect is een functie om de inhoud van een cel weer te geven. Dus indirect($A$2) geeft de inhoud van cel $A$2. Met de adresfunctie zet je gegevens om naar een celreferentie, waar indirect dan weer gebruik van maakt.

Nu terug naar je formule:

=SOM(Blad2!V120+Blad3!V120+Blad4!V120+Blad5!V121)

Je zult voor iedere verwijzing het stukje indirect(adres) toe moeten passen. Kortom, in de cel waar deze formule vandaan komt moet je het volgende plaatsen (als deze temninste op regel 120 staat, anders moet je het verschil in aantal rijen toevoegen, bv. "ROW()-100":

=SUM(INDIRECT(ADDRESS(ROW();22;1;0;"Blad2");FALSE);INDIRECT(ADDRESS(ROW();22;1;0;"Blad3");FALSE);INDIRECT(ADDRESS(ROW();22;1;0;"Blad4");FALSE);INDIRECT(ADDRESS(ROW();22;1;0;"Blad5");FALSE))

Ik ben er overigens vanuit gegaan dat de regel 121 op Blad5 een foutje is en dat dit ook 120 moet zijn? Anders wordt de formule:

=SUM(INDIRECT(ADDRESS(ROW();22;1;0;"Blad2");FALSE);INDIRECT(ADDRESS(ROW();22;1;0;"Blad3");FALSE);INDIRECT(ADDRESS(ROW();22;1;0;"Blad4");FALSE);INDIRECT(ADDRESS(ROW()+1;22;1;0;"Blad5");FALSE))

 

[Noudthy]

Dank je! duidelijk voor als nog.
Ik ga er maandag even mee stoeien.