Excel: vlookup in ander bestand

[erikkallenberg]

Mijn vraag

Hoe zorg ik in onderstaande formule dat het jaar en weeknummer(jaar in 4, week in 2 digits) elke week veranderd ?

=VLOOKUP($F$3;'K:\Registratie\# Operations\2020\Productiviteit[Productiviteit HSC AL 202003.xlsx]PickenGr1'!$A$21:$BJ$852;7;FALSE)

In week 3 heet het bestand Productiviteit HSC AL 202003.xls
In week 4 heet het bestand Productiviteit HSC AL 202004.xls
In week 5 heet het bestand Productiviteit HSC AL 202005.xls
etc.

Wat ik wil is dat de vlookup wanneer het NU week 5 is, de productiviteit in het bestand 202005 opzoekt.

Wat ik al gevonden of geprobeerd heb
Dan zou de formule er ongeveer zo uit zien:
=VLOOKUP($F$3;'K:\Registratie\# Operations\2020\Productiviteit[Productiviteit HSC AL &TEXT(YEAR(TODAY());WEEKNUM(TODAY()))&.xlsx]PickenGr1'!$A$21:$BJ$852;7;FALSE)

Ik krijg de formule niet kloppend.
Kan iemand mij helpen :-)?

edit:

bedrijsnaam weggehaald

...

[ Voor 3% gewijzigd door erikkallenberg op 20-01-2020 20:04 ]

[tritimee]

Probleem is dat je voor jouw formule, Indirect nodig hebt. Dat werkt echter alleen als de target workbooks allemaal open zijn.

Ik denk dat je beter met een VBA code kunt kijken of je met 1 druk op de knop, of bij het openen van het bestand, een referentie kan laten aanpassen

[erikkallenberg]

tritimee schreef op zondag 19 januari 2020 @ 10:20:
Probleem is dat je voor jouw formule, Indirect nodig hebt. Dat werkt echter alleen als de target workbooks allemaal open zijn.

Ik denk dat je beter met een VBA code kunt kijken of je met 1 druk op de knop, of bij het openen van het bestand, een referentie kan laten aanpassen

Bedankt voor je reactie. Kan je misschien toelichten hoe ik de referentie automatisch kan laten aanpassen met een voorbeeld :-)?

[DappereDodo]

Probeer eens handmatig de wijziging door te voeren terwijl je een macro opneemt. Dan heb je je eigen voorbeeld gemaakt

Hoeft alleen nog getweaked te worden, maar ja, dit is tenslotte tweakers.net.

[erikkallenberg]

Ben nog een beginnend tweaker....

Ik probeer dit, maar dit is ook niet de oplossing.

Range("M6").Select
ActiveCell.Replace What:="202002", Replacement:="202003", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("M6").Select

Hoe zorg ik er dan voor dat in week 4 202002 automatisch 202004 wordt?

[F_J_K]

Je past nu de inhoud van een cel aan, dat is niet je vraag en dus niet wat je in de code wilt oplossen.
@tritimee gaf aan dat je probleem is dat het bestand niet is geopend en er dan geen vlookup mogelijk is. Je wilt dus dat bestand openen bij openen van je hoofd-bestand. Dat kan met Workbooks.Open Filename:= "bladiebla.xlsx" wat je dan in Workbook_Open() wilt zetten. Je kunt ook de rest met VBA doen, maar als je daar nog geen ervaring mee hebt dan zou ik dat niet doen zonder eerst inlezen en oefenen.

offtopic:
Ik heb de topictitle wat aangepast. "Formule in Excel" zegt zo weinig, dat geldt voor de meeste topics die over Excel gaan. Ook: misschien wil je de bedrijfs-/locatienaam weghalen uit je topicstart, @erikkallenberg

[ Voor 18% gewijzigd door F_J_K op 20-01-2020 06:47 ]

[tritimee]

erikkallenberg schreef op zondag 19 januari 2020 @ 16:46:
Ben nog een beginnend tweaker....

Ik probeer dit, maar dit is ook niet de oplossing.

Range("M6").Select
ActiveCell.Replace What:="202002", Replacement:="202003", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("M6").Select

Hoe zorg ik er dan voor dat in week 4 202002 automatisch 202004 wordt?

ik denk dat je hier al best een eind mee op weg bent!

bedenk eens waaruit jouw 202004 bestaat, en kijk eens of er formules zijn die standaard een deel van dat getal kunnen genereren.

Wat je ook zou kunnen doen is een hulpsheet, waar je zelf het getal invult.
waardoor je iets krijgt als worksheet("Hoofdsheet").Range("M6").Value = "deel 1 van link naar bestand"&"ws(Hulpsheet), range A1.value&"rest van de koppeling"

en dan iets maken waardoor dit update (wellicht meenemen in je hulpsheet on change? of een vba button om je macro aan te koppelen)

ik heb zo 123 geen panklaar antwoord, want ik ken niet alle functies uit mijn hoofd. maar dit zijn wat mogelijkheden die je hebt ( en zo zijn er nog veel meer opties)

[Belindo]

Zoals @tritimee al zei; als je het jaar en weeknummer dynamisch wilt gebruiken heb je een formule nodig die vereist dat je werkboek waarin je zoekt open is.

Verder probeer je nu in VBA simpel 202002 te vervangen met 202003. Wat je echter wilt is ergens een hulp formule gebruiken die dynamisch het jaar en weeknummer genereert. Hiervoor kun je de TODAY() gebruiken om het jaar en het weeknummer op basis van vandaag te genereren.

Psuedocode, niet getest:

code:

=YEAR(TODAY())&TEXT(WEEKNUM(TODAY(),21),"00")

Voor het weeknummer gebruik ik de return type 21, deze gebruikt dan de ISO week nummers zoals wij in Europa gewend zijn, weken starten op maandag. Tevens wrap ik de WEEKNUM() functie in een FORMAT() functie om het weer te geven als twee cijfers. Standaard geeft de WEEKNUM() formule één getal terug voor week 1 t/m 9.

Wat je echter ook kunt overwegen is de data binnenhalen vanuit je file waarin je wilt zoeken. Je maakt dan een verbinding tussen twee bestanden en 'download' de data uit het ene bestand in je andere. Deze data connectie is met VBA makkelijk aan te passen zodat hij met een druk op de knop de data uit een andere week-file binnenhaalt.

[erikkallenberg]

Het werkt nu met een indirect formule.

Dit werkt echter alleen als het bestand open staat.

Ik kan dit laten openen bij het opstarten van het bestand, maar dit is niet wenselijk.

Is er een manier om dit te omzeilen? Dus dat de indirect functie nog wel werkt, maar het bestand niet geopend hoeft te worden?

edit:

Ben nu tot de volgende oplossing gekomen:

Vind het nog niet ideaal, maar met een update knop wordt het bestand geopend & dichtgemaakt.

Dan werkt het op zich

[ Voor 24% gewijzigd door erikkallenberg op 20-01-2020 19:53 ]

[tritimee]

erikkallenberg schreef op maandag 20 januari 2020 @ 13:15:
Het werkt nu met een indirect formule.

Dit werkt echter alleen als het bestand open staat.

Ik kan dit laten openen bij het opstarten van het bestand, maar dit is niet wenselijk.

Is er een manier om dit te omzeilen? Dus dat de indirect functie nog wel werkt, maar het bestand niet geopend hoeft te worden?

edit:

Ben nu tot de volgende oplossing gekomen:

Vind het nog niet ideaal, maar met een update knop wordt het bestand geopend & dichtgemaakt.

Dan werkt het op zich

Nee,

Indirect kan alleen naar open sheets.
Je imo beste opties zijn om met een VBA formule de koppeling aan te laten passen, of handmatig elke week de koppeling aan te passen via Data/Edit Links in de ribbon.