Hoe kan ik mijn formule behouden na het plakken speciaal…

[tobe1966]

Hoe kan ik mijn formule behouden na het plakken speciaal shift cels down?
...
Ik heb een spreadsheet met mijn ING transacties, alle transacties zijn in een extra kolom toegewezen aan een categorie.

Ik heb op een 2e werkblad een overzicht met een aantal verschillende formules waar gegevens uit de transacties gehaald worden met bijvoorbeeld sumifs.

Werkt allemaal perfect, echter als ik mijn nieuwe transacties invoer dmv plakken speciaal, shift down cells. Dan veranderen ook de formules.
De formules hebben als criteria om te zoeken vanaf bijvoorbeeld A2 en als ik dan 6 transacties toevoeg veranderen de formules automatisch naar A8 en dat is natuurlijk niet de bedoeling.

Ik heb in de formule naar mijn idee al gewijzigd naar absoluut dmv $A$2 maar dat helpt niet.

Weet iemand hier een oplossing voor?

[dixet]

Dit is wel typisch een dingetje voor het correct gebruik van absolute en relatieve verwijzingen.
Een verwijzing naar A2 zal meeveranderen als er rijen of kolommen worden toegevoegd.
Een verwijzing naar $A$2 zal helemaal niet meeveranderen. Je moet dus goed bedenken welk deel van je verwijzing mee moet veranderen.

Als je categoriën in kolom A staan en de waarden die je wilt sommeren in kolom B krijg je zoiets:

code:

=SUMIF(Sheet1!A$2:A6;"appelmoes";Sheet1!C$2:C6)

Door het dollarteken alleen te gebruiken bij de 1e cel in je zoekreeks en optelreeks zet je die vast. De laatste cel in je reeks moet wel meeveranderen omdat het aantal rijen zal toenenemen.

Na het toevoegen van 10 nieuwe rijen zal je formule automatisch veranderen in

code:

=SUMIF(Sheet1!A$2:A16;"appelmoes";Sheet1!C$2:C16)

NB ik doe de aanname dat je het over Excel hebt, maar in bijvoorbeeld Google Sheet werkt het net zo. In mijn versie van Excel (365) bestaat geen plakken speciaal, shift down cells.

[Lustucru]

dixet schreef op dinsdag 9 januari 2024 @ 13:06:
Dit is wel typisch een dingetje voor het correct gebruik van absolute en relatieve verwijzingen.
Een verwijzing naar A2 zal meeveranderen als er rijen of kolommen worden toegevoegd.
Een verwijzing naar $A$2 zal helemaal niet meeveranderen. Je moet dus goed bedenken welk deel van je verwijzing mee moet veranderen.

Dat is dus niet correct. Zowel =A5 als =$A$5 wordt aangepast als je ergens tussen rij 1 en 5 een of meer regels invoegt. Anders gezegd:
Als de cel met de formule wordt verplaatst of gekopieerd verandert de verwijzing wel bij een relatieve verwijzing en niet bij een absolute verwijzing; als de cel waarnaar verwezen wordt van plek verandert worden beide formules aangepast.

De oplossing voor het probleem in de topicstart kan twee kanten op:
- voeg geen rijen of cellen in boven of links van A2 (voorkeur)
- fixeer de verwijzing door gebruik te maken van =indirect("A2")

[dixet]

Lustucru schreef op dinsdag 9 januari 2024 @ 13:55:
[...]


Dat is dus niet correct. Zowel =A5 als =$A$5 wordt aangepast als je ergens tussen rij 1 en 5 een of meer regels invoegt. Anders gezegd:
Als de cel met de formule wordt verplaatst of gekopieerd verandert de verwijzing wel bij een relatieve verwijzing en niet bij een absolute verwijzing; als de cel waarnaar verwezen wordt van plek verandert worden beide formules aangepast.

De oplossing voor het probleem in de topicstart kan twee kanten op:
- voeg geen rijen of cellen in boven of links van A2 (voorkeur)
- fixeer de verwijzing door gebruik te maken van =indirect("A2")

Je hebt helemaal gelijk! Ik had dit getest, maar blijkbaar toch iets anders gedaan want bij opnieuw proberen gedraagt het zich zoals jij beschrijft.

Een andere oplossing is om de transacties in een tabel op te nemen. Dan kan je in de formules verwijzen naar de naam van de kolom in plaats van celverwijzingen te gebruiken, bijvoorbeeld

code:

=SUMIF(Table1[Categorie];"appelmoes";Table1[Bedrag])

Dit blijft werken, waar je binnen de tabel ook rijen of kolommen toevoegt.

[ Voor 17% gewijzigd door dixet op 09-01-2024 15:12 ]

[Lustucru]

dixet schreef op dinsdag 9 januari 2024 @ 15:08:
[...]
Een andere oplossing is om de transacties in een tabel op te nemen. Dan kan je in de formules verwijzen naar de naam van de kolom in plaats van celverwijzingen te gebruiken, bijvoorbeeld

code:

=SUMIF(Table1[Categorie];"appelmoes";Table1[Bedrag])

Dit blijft werken, waar je binnen de tabel ook rijen of kolommen toevoegt.

Dat is idd de meest elegante oplossing.

[tobe1966]

Dit is mijn formule:
=SUMIFS($Transacties.$H$2:$Transacties.$H$1048576;$Transacties.$A$2:$Transacties.$A$1048576;A5;$Transacties.$B$2:$Transacties.$B$1048576;">=01-01-2023";$Transacties.$B$2:$Transacties.$B$1048576;"<=31-01-2023")

Moet de, =indirect, dan voor elke verwijzing bij elke criteria en met () ?

[Lustucru]

In dit geval: gewoon "H:H" etc. Anders de oplossing van @dixet

[ Voor 34% gewijzigd door Lustucru op 10-01-2024 12:28 ]

[tobe1966]

Deze "H:H" lukt niet, is dat wellicht om dat in H1 geen getal staat, maar de tekst "bedrag" ?

[dixet]

Wat lukt daar niet aan? We kunnen hier vandaan niet op je scherm kijken hè 🤔

[tobe1966]

Als ik het wijzig zoals onderstaand:

=SUMIFS($Transacties.$H:$H;$Transacties.$A$2:$Transacties.$A$1048576;A5;$Transacties.$B$2:$Transacties.$B$1048576;">=01-01-2023";$Transacties.$B$2:$Transacties.$B$1048576;"<=31-01-2023")

krijg ik een Err:502

Ook als ik de $ tekens weglaat.

[dixet]

Probeer het eens zonder dollarteken voor je sheetnaam en met een uitroeptekenin plaats van punt tussen de sheetnaam en celadres.

code:

=SUMIFS(Transacties!$H:$H;Transacties!$A:$A;A5;Transacties!$B:$B;">=01-01-2023";Transacties!$B:$B;"<=31-01-2023")

[tobe1966]

yep werkt nu, moest nog wel de ! vervangen door . maar het werkt perfect.

Bedankt allemaal voor de medewerking !!

[dixet]

Dan ben ik toch wel nieuwsgierig welk spreadsheetpakket je gebruikt.

In Excel verwijs je naar een andere sheet zonder dollarteken voor de sheetnaam en een uitroepteken er achter,

In LibreOffice Calc juist met dollarteken en een punt.
Geen dollarteken en een punt is een notatie die ik niet ken...

voor de volgende keer is het handig dat in je startpost te vermelden, anders krijg je heel veel antwoorden waar je niks aan hebt

[tobe1966]

Libre office Calc.

Dollar teken en punt dus.

Was inderdaad beter geweest om het in de opening post te vermelden.