Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

[Excel365] Formule aanpassen advh tabnaam

Pagina: 1
Acties:

Onderwerpen

Vraag


  • gwystyl
  • Registratie: juni 2001
  • Laatst online: 16:04

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
  • Nu online

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
  • Nu online

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: 16:04

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
  • Nu online

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



Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True