Toon posts:

[Excel365] Formule aanpassen advh tabnaam

Pagina: 1
Acties:

Onderwerpen

Vraag


  • gwystyl
  • Registratie: Juni 2001
  • Laatst online: 13:40

gwystyl

Beugeltje dan maar?

Topicstarter
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:
1
=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:
1
=(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:
1
=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]

Beste antwoord (via gwystyl op 05-03-2021 20:11)


  • g0tanks
  • Registratie: Oktober 2008
  • Laatst online: 07:49

g0tanks

Moderator CSA
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:
1
=INDIRECT("'"&Tabel13[@Achternaam]&"'!"&"C45")

Ultrawide gaming setup: AMD Ryzen 7 2700X | NVIDIA GeForce RTX 2080 | Dell Alienware AW3418DW

Alle reacties


Acties:
  • Beste antwoord
  • 0Henk 'm!

  • g0tanks
  • Registratie: Oktober 2008
  • Laatst online: 07:49

g0tanks

Moderator CSA
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:
1
=INDIRECT("'"&Tabel13[@Achternaam]&"'!"&"C45")

Ultrawide gaming setup: AMD Ryzen 7 2700X | NVIDIA GeForce RTX 2080 | Dell Alienware AW3418DW


  • gwystyl
  • Registratie: Juni 2001
  • Laatst online: 13:40

gwystyl

Beugeltje dan maar?

Topicstarter
@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:
1
=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:
1
=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.

Acties:
  • +1Henk 'm!

  • g0tanks
  • Registratie: Oktober 2008
  • Laatst online: 07:49

g0tanks

Moderator CSA
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:
1
=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:
1
=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.

Ultrawide gaming setup: AMD Ryzen 7 2700X | NVIDIA GeForce RTX 2080 | Dell Alienware AW3418DW



Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee