[Excel] eenvoudig kolomverwijzing updaten

[Angeloonie]

Ik zit te klooien met iets dat doodsimpel lijkt maar op de een of andere manier niet wil lukken..

Situatie:

Ik heb een tabel met verwijzingen:

Sheet X!A1

Sheet X!B1

Sheet X!C1

Vervolgens wil ik dezelfde tabel kopiëren maar dan bij de verwijzing de kolommen verschuiven met een X-aantal, zodat hij deze verwijzing heeft:

Sheet X!D1

Sheet X!E1

Sheet X!F1

In feite wil ik dus gewoon bij A-C een aantal kolommen opschuiven, in dit voorbeeld dus +3 op de kolomverwijzingen (A>D, B>E, C>F). Dit leek mij doodsimpel maar lukt niet of kan ik simpelweg niet vinden.
Ja ik kan per kolom een Ctrl+H doen en Sheet X!A vervangen met Sheet X!D, maar dan moet ik dat dus voor alle kolommen apart doen. (achteraf gezien was dat alsnog sneller geweest dan klooien/googlen )

Ik kan ook niet de juiste term vinden om het antwoord via Google te vinden (zowel NL als EN).

Iemand een idee hoe ik dit simpel doe?

[ Voor 7% gewijzigd door Angeloonie op 18-01-2018 12:49 ]

[breew]

VERSCHUIVING()

=VERSCHUIVING(A1;0;3)

Schuift drie kolommen op naar rechts (je met bovenstaande formule dan de inhoud uit D3)

Engels: OFFSET()

[Angeloonie]

Dat is hem dus niet echt, wil niet aan de formules komen maar gewoon de bestaande formules bijwerken met de verschuiving.

[StecaGrid]

3 kolommen invoegen, formules doorslepen en de onjuiste kolommen weer verwijderen. Das het meest simpel.
Heb je ook weer verwijzingen naar deze cellen dan sleep je de kolom naar de 3e kolom en trek je de formules vanuit kolom 2 naar kolom 3. Beetje omslachtig uitgelegd maar wel doeltreffend

[breew]

Angeloonie schreef op donderdag 18 januari 2018 @ 14:29:
Dat is hem dus niet echt, wil niet aan de formules komen maar gewoon de bestaande formules bijwerken met de verschuiving.

Je wilt niet aan de formules komen, maan ze wel bijwerken


Wellicht is gewoon find/replace (crtl+h) in de formuletekst optie?
Of, iets transparanter, de formules tonen, en dan find/replace?

1. 
2. 
3. 
4. 
5. 

[ Voor 33% gewijzigd door breew op 18-01-2018 15:18 ]

[Angeloonie]

StecaGrid schreef op donderdag 18 januari 2018 @ 14:37:
3 kolommen invoegen, formules doorslepen en de onjuiste kolommen weer verwijderen. Das het meest simpel.
Heb je ook weer verwijzingen naar deze cellen dan sleep je de kolom naar de 3e kolom en trek je de formules vanuit kolom 2 naar kolom 3. Beetje omslachtig uitgelegd maar wel doeltreffend

Ik snap wat je bedoeld en normaliter doe ik dit ook gewoon zo, maar dit gaat hier niet ivm enorm veel samengevoegde cellen

breew schreef op donderdag 18 januari 2018 @ 15:10:
[...]

Je wilt niet aan de formules komen, maan ze wel bijwerken

Nee ik wil de formule niet bijwerken maar de verwijzing. De formule blijft identiek maar enkel de kolomverwijzing moet opschuiven.

Wellicht is gewoon find/replace (crtl+h) in de formuletekst optie?
Of, iets transparanter, de formules tonen, en dan find/replace?

1. [afbeelding]
2. [afbeelding]
3. [afbeelding]
4. [afbeelding]
5. [afbeelding]

Uiteindelijk heb ik inderdaad gewoon de kolommen geselecteerd en een !A > !D zoeken/vervangen gedaan.

(PS. formules weergeven kan sneller dan je laat zien door Ctrl+T te gebruiken )


Omwegen werken allemaal wel maar ik vind het apart dat je niet simpel ergens een verwijzing makkelijk kan updaten.

[ Voor 3% gewijzigd door Angeloonie op 18-01-2018 15:57 ]

[breew]

Angeloonie schreef op donderdag 18 januari 2018 @ 15:55:
[...]
Ik snap wat je bedoeld maar dit gaat niet makkelijk ivm enorm veel samengevoegde cellen

Aaargh!! Degene die samengevoegde cellen gebruikt in data-bestanden moeten ze bij de knieën afzagen.. Het zorgt GEHEID voor problemen.
Cellen samenvoegen doe je, behoudens enkele zeer specifieke usecases, alleen in tabellen waarmee je data presenteert. Maar eigenlijk in Excel liever nooit, zeker niet in bron-data bestanden! Je krijgt nagenoeg altijd gezeik met de samengevoegde cellen. Excel is een spreadsheet programma, geen tabel-opmaak-tool. Dat doe je beter in Word

Uiteindelijk heb ik inderdaad gewoon de kolommen geselecteerd en een !A > !D zoeken/vervangen gedaan.
(PS. formules weergeven kan sneller dan je laat zien door Ctrl+T te gebruiken )
Omwegen werken allemaal wel maar ik vind het apart dat je niet simpel ergens een verwijzing makkelijk kan updaten.

Maar dat zoeken-vervangen is toch een heel simpele manier .
Je kunt evt ook op een cel met formule gaan staan, en dan op F2 drukken. Alle cellen waarnaar wordt verwezen worden nu met kleurtjes gemarkeerd. Je kunt deze cellen nu met de muis 'oppakken' en verslepen; de formule wijzigt dan automagisch.

[Angeloonie]

breew schreef op donderdag 18 januari 2018 @ 16:02:
[...]

Aaargh!! Degene die samengevoegde cellen gebruikt in data-bestanden moeten ze bij de knieën afzagen.. Het zorgt GEHEID voor problemen.
Cellen samenvoegen doe je, behoudens enkele zeer specifieke usecases, alleen in tabellen waarmee je data presenteert. Maar eigenlijk in Excel liever nooit, zeker niet in bron-data bestanden! Je krijgt nagenoeg altijd gezeik met de samengevoegde cellen. Excel is een spreadsheet programma, geen tabel-opmaak-tool. Dat doe je beter in Word

I know.. Het gaat hier om tabellen in Excel die vervolgens in een PowerPoint gezet moeten worden als Excel-tabel, maar uiteraard weer met een nét iets andere weergave dan de brontabel. Echt een kutklus en iets waar ik graag afscheid van neem Ergste is inderdaad die samengevoegde cellen, wat een gekut is dat.

[...]

Maar dat zoeken-vervangen is toch een heel simpele manier .
Je kunt evt ook op een cel met formule gaan staan, en dan op F2 drukken. Alle cellen waarnaar wordt verwezen worden nu met kleurtjes gemarkeerd. Je kunt deze cellen nu met de muis 'oppakken' en verslepen; de formule wijzigt dan automagisch.

Ja de manier van uitvoeren is inderdaad doodsimpel, maar als je dit voor veel kolommen moet doen is het niet echt handig.
Snap ook wel hoe je die verwijzingen kunt slepen enzo (ben echt geen beginner ofzo ), maar ik had echt verwacht dat er wel een handigheidje in Excel zou ditten om en masse een update te kunnen doen

Anyway ik heb nu gewoon zoeken/vervangen gedaan, maar was gewoon benieuwd of dit echt niet sneller/makkelijker kon.

[breew]

Angeloonie schreef op donderdag 18 januari 2018 @ 16:12:
Ja de manier van uitvoeren is inderdaad doodsimpel, maar als je dit voor veel kolommen moet doen is het niet echt handig.
Snap ook wel hoe je die verwijzingen kunt slepen enzo (ben echt geen beginner ofzo ), maar ik had echt verwacht dat er wel een handigheidje in Excel zou ditten om en masse een update te kunnen doen

Die is er; zo kun je met een VBA-tje snel de boel regelen, waarmee je de referentiecellen van formules in geselecteerde cellen in je werkblad met 3 opschuift naar rechts.

Visual Basic:

Option Explicit

Sub DrieKolommenOpschuiven()

  Dim c As Range, strFormule As String, strFormuleNieuw As String
  Dim lonKolom As Long
  
  'voor alle geselecteerde cellen
  For Each c In Selection
    'pak de huidige formule (R1C1-stijl!!)
    strFormule = c.FormulaR1C1
    'haal de kolomreferentie uit de formule
    lonKolom = CLng(Mid(strFormule, InStrRev(strFormule, "[") + 1, InStrRev(strFormule, "]") - 1 - InStrRev(strFormule, "[")))
    'maak de nieuwe formule-tekst (de [3] offset de C (=kolom) met drie naar rechts)
    strFormuleNieuw = Left(strFormule, InStrRev(strFormule, "[")) & CStr(lonKolom + 3) & "]"
    'plak de nieuwe formuletekst weer in de cel
    c.FormulaR1C1 = strFormuleNieuw
  Next c

'KOFFIE!!!

End Sub

[ Voor 26% gewijzigd door breew op 18-01-2018 17:00 . Reden: code update ]

[Raznov]

Als ik alle 3 kopieer in een keer, en dan de set weer verderop plak is het verloop 3.