Behoud van verwijzing naar cellen op andere tabbladen

[Hendriekus]

Beste Excelspecialisten,

Ik zit met een uitdaging waar ik tot heden nog geen werkbare formules kan bedenken of vinden.
Het is een een werkblad om voor meerdere collega's facturatie bij te houden per week. (in onze branche moeten we eerst een accordering uitvragen die bij de uiteindelijke factuur moet..)
Elke week kopieer ik het laatste blad en maak de inhoud leeg. Dit gaat gemiddeld over een lijst met 60 projecten (regels)

Elk tabblad is een week met daarin projecten en een kolom met een (zelfde)formule die het volgende doet:
De som van alle uitgevraagde en ontvangen bedragen bij elkaar optellen. Dit over alle bladen middels een formules tussen voorblad en eindblad.
Dus: Voor project PR01 vraagt collega PIETJE €500 in week 19 (tabblad 19)
In week 20 (volgende tabblad) ontvangt PIETJE €450 en vult dit in onder de kolom ontvangst
Het is nu de bedoeling dat de kolom met de formule dus laat zien dat er nog €50 resterend is.

Dit was functioneel totdat een collega vorige week de bladen met tabellen "aflopend sorteerde" waardoor later toegevoegde projecten roet in het eten gooide.
Nieuwe projecten (PRO11 zoals in het voorbeeld) worden onderaan bijgevoegd of tussen gevoegd. Bij sortering verwijst de formule dan nog naar de "oude locatie" van de waardes die in voorgaande weken zijn ingevoegd.
Dit geldt ook voor verwijderde werken uiteraard..
Ik ben dus op zoek naar een methode waarbij de formule rekening houdt met het projectnummer als voorwaarde voor de som over alle bladen. (Functie combo met ALS of VERT.ZOEKEN in combinatie met projectnummer..??)

Zie bijlage voor het voorbeeld zoals ik deze nu heb. 5 tabbladen.

Hopelijk kan 1 van jullie me verder helpen. Dank alvast!

 

[AlexCEL]

Voor naar de exacte vraag te kijken: mag ik een versimpeling voorstellen? 1 "platte" tabel met alle gegevens, en een draaitabel voor analyse van de data (op hetzelfde blad of op een separaat blad).

Minder foutgevoelig, veel minder sheets, geen verwijzingen, veel flexibeler. Je kan de draaitabel naar wens inrichten door met de velden te slepen.

 

[Hendriekus]

Beste Alex,

Dank voor je snelle antwoord en tip.
De versimpeling ziet er goed uit en wil zeker bezig gaan met deze manier.
Helaas ben ik tot heden nog te weinig bezig geweest met draaitabellen.
Het probleem is echter dat ik te maken heb met collega's die het in moeten vullen en daarom graag nog even de boel wil "herstellen" zodat het eruit ziet zoals ze gewend zijn.
De sommen welke in voorgaande weken zijn ingevoerd in mijn eigen (groter en uitgebreidere) bestand kloppen nu niet meer door de sortering, en ik weet niet hoe dit te herstellen.
Vandaar dat ik op zoek ben naar een formule die de waardes van meerdere werkbladen meeneemt, mits ze voldoen aan het criteria "met projectnummer"

Dan kan ik met wat meer tijd en aandacht een betere oplossing gaan gebruiken zoals jij voorstelt. Hierbij is het van belang dat deze lijst niet te lang en onoverzichtelijk wordt.

 

[Haije]

Hendriekus,

wellicht werkt deze work around:

vervang in de kolom projectnr PRO door "", m.b.v. zoek en vervang
wijzig de opmaak van het veld projectnr in Aangepast: "PRO"#

Effectief staat er dan in het veld projectnr een nr

Als je dit op alle tabbladen doet kun je op projectnr sorteren

zie bijlage

 

[Hendriekus]

Beste Haije, Dank voor je terugkoppeling.

Voor zover ik het kan bepalen echter geen oplossing voor mijn probleem.
Het probleem is dat ik een werkmap heb met 52 bladen, met hierin tabellen.
In deze tabellen zit een formule met SOM van een meerdere cellen in een regel, over alle tabbladen. (in het geval van mijn voorbeeld =SOM(Voorblad:Eindblad!C11)-SOM(Voorblad:Eindblad!D11)
Zolang er geen sortering is, en dus op elk blad de het zelfde project op dezelfde regel staat werkt de formule goed.
Maar nu is er vorige week gesorteerd (van A naar Z) en dit kan niet ongedaan worden gemaakt.
Aangezien de lijst wekelijks wordt aangevuld met nieuwe werken die niet perse opvolgend zijn, verwijzen de formules nu niet meer naar dezelfde regel.
Dus op een ander tabblad, staat het projectnummer nu op een andere regel.
Wat ik dus denk nodig te hebben om dit te herstellen, is een formule of methode die alle cellen, over alle bladen optelt. Met als criteria het projectnummer. (in ons bedrijf is dat een volgserie OP#####)
De standaard SOM.ALS werkt niet over meerdere bladen c.q. tabellen voor zover ik weet.

 

[VenA]

Dat wordt dan 52 x som.als. Dus kan je beter overstappen op het idee in #2

 

[Haije]

Maar nu is er vorige week gesorteerd (van A naar Z) en dit kan niet ongedaan worden gemaakt.

In alle bladen? Of in (slechts) 1 of 2?

In dat geval zou ik het als volgt doen:
zet in de eerste vrije kolom op elke regel een volgnr, zodanig dat als je daarop sorteert je de gewenste volgorde weer hebt.
Evt daarna mbv beveiligingsopties in Excel sorteren onmogelijk proberen te maken

 

[AlexCEL]

Als je dan toch echt een formule wilt gebruiken... voor E4:

=SOMPRODUCT(ALS.FOUT(SOM.ALS(INDIRECT("week"&RIJ($5:$100)&"!A5:A100");$A5;INDIRECT("week"&RIJ($5:$100)&"!C5:C100"))-SOM.ALS(INDIRECT("week"&RIJ($5:$100)&"!A5:A100");$A5;INDIRECT("week"&RIJ($5:$100)&"!D5:D100"));0))

Sheet Voor- en Eindblad niet nodig. Bij 52 tabbladen wordt deze wel traag..... overweeg #2 nog eens.

 

[Hendriekus]

Als je dan toch echt een formule wilt gebruiken... voor E4:

=SOMPRODUCT(ALS.FOUT(SOM.ALS(INDIRECT("week"&RIJ($5:$100)&"!A5:A100");$A5;INDIRECT("week"&RIJ($5:$100)&"!C5:C100"))-SOM.ALS(INDIRECT("week"&RIJ($5:$100)&"!A5:A100");$A5;INDIRECT("week"&RIJ($5:$100)&"!D5:D100"));0))

Sheet Voor- en Eindblad niet nodig. Bij 52 tabbladen wordt deze wel traag..... overweeg #2 nog eens.

Goedemorgen Alex,

Op basis van wat meer zoekwerk op internet kreeg ik al het vermoeden dat het een somproduct i.c.m. som.als moest gaan worden.
Ik kan me van heel lang geleden herinneren dat ik die ook in een werkblad met nog meer bladen en had. Hier liep de boel toen bijna in vast elke keer.
Dus behalve dat ik nog niet exact weet hoe deze toe te passen in mijn eigen bestand (het vb bestandje is wat versimpeld) zal het waarschijnlijk te traag worden.

Ik zal dus zeker #2 gaan toepassen in de toekomst zodra ik hier wat meer in thuis ben.
Tot die tijd nog maar even kijken hoe ik mijn huidige lijsten uit de knoop krijg (de data moet hergebruikt worden)

@Haije, jij ook nog bedankt voor de tip met volgnummering. Het zijn echter helaas 14 bladen dus dan ben ik nog wel even zoet. En het sorteren is juist een wenselijke functionaliteit.
Misschien wel even bruikbaar voor het ontknopen. :thumb:

 

[AlexCEL]

Een andere optie die ik nog bedenken is het maken van een soort verzamelblad. Hieruit kan je dan de gegevens per week opzoeken. Dit beperkt het aantal INDIRECT/SOM.ALS/SOMPRODUCT functies nogal, en is daarmee een stuk sneller. Je kan de tabellen naar wens sorteren, zoekresultaat gaat mee.

Edit: (gewijzigd) voorbeeld toegevoegd.

 

[Hendriekus]

Beste Alex,

Super dank voor de moeite. Het is behoorlijk crisis met thuiswerken en collega's die steeds hulp nodig hebben. Dus elke tijdwinst is meegenomen.
Kan het zijn dat je bijgevoegde bestand per abuis dezelfde is als bij #8? Ik zie geen verzamelblad in je voorbeeld

 

[AlexCEL]

Oeps.

Bestand gewijzigd in #10.

 

[Hendriekus]

Beste Alex,

Super bedankt!
Zoals je in de gaten had is mijn Excel niet van hoog niveau. Ik gebruik vaak functies binnen de behoefte die ik heb vanuit prijsbladen of calculatiebladen etc. (bouw en onderhoud)
Ik ga hiermee aan de slag en weet zeker dat het een goed tussenstation is totdat ik aan de variant met draaitabellen begin.

Mocht ik daar mee vastlopen, zal ik zeker weer even in de lucht komen.

Thanks :thumb:

 

[Hendriekus]

Oeps.

Bestand gewijzigd in #10.

Beste Alex,

Wilde je even een PM sturen omdat ik de vraag als "opgelost" heb gemarkeerd. (er kan geen excel bijlage in een PM volgens mij..)

Ben nog steeds aan het rommelen met het implementeren van jouw oplossingen in mijn bestand.
Ik ben bezig met de variant van een verzamelblad. (Simpelweg omdat ik niet handig genoeg ben met draaitabellen en ik niet weet hoe meerdere werkbladen als brondata te gebruiken).

De uitdaging zit hem in het feit dat ik het voorbeeld bestand vorige keer heb versimpeld.
Maar nu krijg ik jouw formules niet toegepast.
In combinatie met andere post's op dit forum, denk ik dat ik een heel eind ben maar blijf een foutcode krijgen.

Het is de wens om collega's wekelijks zelf de invoer te laten doen voor hun projecten.
Voor mijn overzicht moet ik een totaal hebben per week van uitstaande uitvragen maar ook een totaal van openstaande uitvragen.

Misschien een nieuwe post openen?

 

[AlexCEL]

Nieuwe vraag is niet nodig, kunnen hier wel voortborduren.

Gaat niet goed in je bestand inderdaad. De INDIRECT klopt niet. In voorbeeld in #10 zie je dat er een "teller" loopt voor het tabbladnummer, en dat binnen elk tabblad verwezen wordt naar een vast aantal rijen. Beide flexibel ingebouwd (parameters in de eerste 3 rijen). Dit zul je ook in je eigen sheet moeten inbouwen. Zie bijlage.

Let wel: het verzamelblad is een "hulpblad". Kun je verbergen o.i.d. Het is niet de bedoeling dat een gebruiker hier iets mee doet. In het jaaroverzicht kun je dan sommen maken uit het verzamelblad (of er een draaitabel van maken).

 

[Hendriekus]

Beste Alex,

Allereerst weer super bedankt dat je er nog even naar wil kijken.
Ik denk dat je voorbeeld al voor een groot deel functioneel is.
Als ik het goed begrijp, gebruik jij dus geen INDIRECT i.c.m. SOMPRODUCT (deze combi zag ik in veel andere topics)

Als ik naar je vb bestand kijk valt me een paar dingen op.
- In cel A6 is de formule anders dan de opvolgende in de kolom.
- Vanaf cel A10 naar beneden is er een verwijzing die (verkeerd?) meeloopt en opeens naar de kolom "klanten" verwijst. Moet deze cel in de formule worden vastgezet ($) op A1?
- Als ik deze variant gebruik (i.p.v. de combinatie met SOMPRODUCT) dan moet ik de verwijzing in de tabbladen (wk12 t/m wk14) in kolom G aanpassen naar een SOM.ALS? Daar heb ik nu verticaal zoeken maar dan telt hij niks op.

Verder zal ik de parameters in de eerste 3 rijen nog even goed onderzoeken.
"Start week" snap ik.
"Aantal projecten/week" nog niet helemaal. Dit is de bandbreedte van het aantal rijden dat hij onder meeneemt denk ik.
"Start rij projectoverzicht" is volgens mij het startpunt vanaf waarnaar verwezen wordt over alle tabbladen (dit zal ik dan ook niet aanpassen per blad)Bekijk bijlage 347416

 

[AlexCEL]

1. "een INDIRECT i.c.m. SOMPRODUCT"
Dat kan wel, maar dan moet je een actuele projectenlijst bijhouden in de verzamelstaat, en op de weekbladen naar de verzamelstaat verwijzen om de totalen te bepalen. Het is maar wat je wilt.

2. "In cel A6 is de formule anders dan de opvolgende in de kolom."
Klopt, omdat het de eerste rij is.

3. "Vanaf cel A10 naar beneden is er een verwijzing die (verkeerd?) meeloopt en opeens naar de kolom "klanten" verwijst."
Dat is goed. Dat is het RIJ(C20) gedeelte van de functie, die fungeert als teller en loopt mee als je de functie naar beneden door trekt. Mag ook X20 zijn, of BB20. Als het daar maar rij 20 is... kolom maakt niet.

4. "dan moet ik de verwijzing in de tabbladen (wk12 t/m wk14) in kolom G aanpassen naar een SOM.ALS?"
Niet per se. Als je een totaalkolom maakt in de verzamelstaat zou het ook met een VERT.ZOEKEN moeten kunnen.

5. "Dit is de bandbreedte van het aantal rijden dat hij onder meeneemt denk ik."
Precies. Oftewel: hoeveel projecten heb je maximaal per week. Dat aantal rijen neemt hij mee van elk weekoverzicht.

6. "Start rij projectoverzicht" is volgens mij het startpunt vanaf waarnaar verwezen wordt over alle tabbladen"
Correct.

 

[Hendriekus]

De voorkeur is eigenlijk wel een verzamelblad met de actuele projecten zoals omschreven bij punt 1.
Deze exporteer ik namelijk wekelijks uit ons ERP programma.
Daarbij is dan het uiteindelijke doel ook makkelijker in te zien: Het totaalbedrag aan openstaande bedragen per project.
Ik denk dat jou opzet evengoed werkt, maar dan moet ik de nieuwe projecten dus toevoegen per week op het betreffende blad?

 

[AlexCEL]

Als je een lijst met projectnummers kan produceren en geïnteresseerd bent in totalen per projectnummer over alle weekbladen dan kun je deze gebruiken voor G4 (zelf aanpassen voor de overige kolommen):

=SOMPRODUCT(ALS.FOUT(SOM.ALS(INDIRECT("'wk"&RIJ($1:$53)&"'!B4:B500");B4;INDIRECT("'wk"&RIJ($1:$53)&"'!H4:H500"));0))

Let op: dit is een matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter. (tenzij je Office 365 gebruikt).

Kun je zelf aanpassen voor de overige kolommen. Kolom M is niet nodig. Dit is wel de mooiste oplossing vind ik

Wat je met de totalen op de weekbladen wilt is me nog even onduidelijk. Ook al kijk je bij week 12, moet je de totaalsom tot heden zien? Waar dan precies?

 

[Hendriekus]

Super, getest en volgens mij pakt hij alle bedragen op over alle werkbladen.
Alleen denk ik dat de server het niet helemaal aan kan wat betreft rekenvermogen
Bij opslaan en verwerken loopt de boel een beetje vast en kan ik een kop koffie halen.

ik zal kijken of het scheel als ik het bereik van verklein..
Dan voor de optie in ieder geval. Als dit te zwaar blijkt heb ik genoeg om mee verder te kunnen in ieder geval