[Excel365] Formule aanpassen advh tabnaam

[gwystyl]

Mijn vraag
Ik heb ooit een excel sheet gemaakt waarop onze office manager gemakkelijk de vakantiedagen bij kan houden.
Op het tabblad data staat net zo'n kalender, en verschillende formules om de weekend- en feestdagen te arceren als je het jaartal verandert.
So far, so good.

Vanwege corona wil het bedrijf flexibeler omspringen met vergoedingen, en moet zij gaan bijhouden wanneer iemand thuis werkt, op de zaak werkt en of diegene lunch gebruikt of niet.
Als laatste wil ze voor de boekhouding graag een overzicht maken waarin e.e.a. ook inzichtelijk is.

Ik heb nu de sheet omgebouwd naar onderstaand plaatje:



Op elk tabblad staat de tabbladnaam in cel C1 door de volgende formule:

code:

=DEEL(@CEL("bestandsnaam";C1);VIND.ALLES("]";@CEL("bestandsnaam";C1))+1;31)

zoals gevonden in [EXCEL] Tabbladnaam als waarde in een cel

Op het datablad heb ik per persoon ook de woon-werk afstand neergezet, zodat ik hiernaar kan verwijzen/zoeken bij de totalen onderaan. Dat werkt met de volgende formule:

code:

=(C45+C46)*(X.ZOEKEN($C$1;Tabel1[Achternaam];Tabel1[km w-w]))*vergoeding_per_km

Elk tabblad van de verschillende personeelsleden is op deze manier opgebouwd. In het maandoverzicht wat naar de boekhouding moet, wil ik dus de totalen van elke persoon die maand weergeven.
Ik heb hiervoor het volgende tabblad gemaakt:



Bovenin zie je het huidige jaar (verandert mee als je het jaartal op het datablad verandert) en een dropdown list met de maanden.

De formule in de adresbalk werkt, maar met zo'n 30 personen en 3 kolommen is het een flinke klus om die formules aan te passen voor alle personen. Daarnaast werken ze natuurlijk niet meer als de naam verandert.

Ik vermoed dat ik met Indirect aan de slag moet, maar ik krijg het niet voor elkaar. Voor mijn idee lijkt het op de vraag zoals hier gesteld op helpmij forum of hier op Tweakers: formule met verwijzing naar werkblad, opgehaald uit cel1


Relevante software en hardware die ik gebruik
Excel 365

Wat ik al gevonden of geprobeerd heb
Verschillende interpretaties van mijn formule met de functie indirect, met " ' & op verschillende plaatsen, zoals

code:

=X.ZOEKEN($C$2;Data!$B$5:$M$5;indirect("'C7""'!""$C$44:$N$44")*Thuiswerk

Hierbij staan ', verwijzing naar C7 waar de naam staat, '! en het celbereik allemaal tussen dubbele quotes "
Dit werkt niet.
Ook als ik & voor C7 zet werkt het niet
Ook als ik Tabel1[Naam] gebruik werkt het ook niet.

Een verwijzing naar =@INDIRECT("C7") levert "Hendriksen" op, maar =indirect("'"Tabel13[@Achternaam]"'!"C45) levert niet de waarde van cel C45 op tabblad "Hendriksen" op.

wie kan me in de juiste richting helpen? Indien gewenst kan ik mijn werkblad ergens uploaden

Jan, Piet, Joris en Corneel werken uiteraard niet voor ons bedrijf maar zijn voorbeeldnamen. Als extra hindernis heb ik de Vries als "Vries, de" geschreven. Als de oplossing alleen werkt als daar Vries staat vind ik het ook prima..

[Voor 3% gewijzigd door gwystyl op 05-03-2021 17:27]

[g0tanks]

gwystyl schreef op vrijdag 5 maart 2021 @ 17:23:
Een verwijzing naar =@INDIRECT("C7") levert "Hendriksen" op, maar =indirect("'"Tabel13[@Achternaam]"'!"C45) levert niet de waarde van cel C45 op tabblad "Hendriksen" op.

C45 is tekst en moet dus ook tussen aanhalingstekens. En je mist een boel &-tekens. Nu wordt alles op een hoop gegooid en dat werkt sowieso niet.

code:

=INDIRECT("'"&Tabel13[@Achternaam]&"'!"&"C45")

[gwystyl]

@g0tanks ik begrijp hieruit dat de verschillende stukjes door een & aan elkaar gekoppeld moeten worden, en het stuk na INDIRECT vertaald achter elkaar geplakt wordt.

Als ik jouw formule pak:

code:

=INDIRECT("'"&Tabel13[@Achternaam]&"'!"&"$C$44")

krijg ik inderdaad de waarde van cel C44 op tabblad "Hendriksen" als ik hem op die hoogte invul.


Ik heb nu het volgende gemaakt van de formule om de thuiswerkvergoeding uit te rekenen

code:

=X.ZOEKEN($C$2;Data!$B$5:$M$5;INDIRECT("'"&Tabel13[@Achternaam]&"'!")&"$C$44:$N$44")*Thuiswerk

Hierbij is
$C$2 de maand
Data!$B$5:$M$5 het bereik van maanden op het datablad
INDIRECT("'"&Tabel13[@Achternaam]&"'!""$C$44:$N$44") zou het corresponderende bereik van het aantal thuiswerkdagen van die maand op moeten leveren
Thuiswerk = naam voor de vergoeding per dag.

Dat lijkt te werken, dus bedankt!

Ik had al het idee dat ik in de buurt zat, maar met de uitleg van de functie door Microsoft zelf kwam ik er niet uit.

[g0tanks]

gwystyl schreef op vrijdag 5 maart 2021 @ 20:11:
@g0tanks ik begrijp hieruit dat de verschillende stukjes door een & aan elkaar gekoppeld moeten worden, en het stuk na INDIRECT vertaald achter elkaar geplakt wordt.

Als ik jouw formule pak:

code:

=INDIRECT("'"&Tabel13[@Achternaam]&"'!"&"$C$44")

krijg ik inderdaad de waarde van cel C44 op tabblad "Hendriksen" als ik hem op die hoogte invul.


Ik heb nu het volgende gemaakt van de formule om de thuiswerkvergoeding uit te rekenen

code:

=X.ZOEKEN($C$2;Data!$B$5:$M$5;INDIRECT("'"&Tabel13[@Achternaam]&"'!")&"$C$44:$N$44")*Thuiswerk

Hierbij is
$C$2 de maand
Data!$B$5:$M$5 het bereik van maanden op het datablad
INDIRECT("'"&Tabel13[@Achternaam]&"'!""$C$44:$N$44") zou het corresponderende bereik van het aantal thuiswerkdagen van die maand op moeten leveren
Thuiswerk = naam voor de vergoeding per dag.

Dat lijkt te werken, dus bedankt!

Ik had al het idee dat ik in de buurt zat, maar met de uitleg van de functie door Microsoft zelf kwam ik er niet uit.

INDIRECT kan best verwarrend zijn. Je moet scherp zijn op wat een variabele is en wat niet. De achternaam varieert dus om Tabel13[@Achternaam] moeten geen aanhalingstekens. De cel of cellen waar je naar refereert zijn juist weer niet variabel en daar moeten wel aanhalingstekens omheen.