Link naar bestandsnaam variabel maken

[verhoog]

In een map heb ik voor elke week een bestand en een totaalbestand. Dus 'rapport week 1', 'rapport week 2', 'rapport week 3' en 'rapport totaal'. In 'rapport totaal' wil ik gegevens ophalen uit het rapport van de huidige week. Dus als vandaag week 25 is, wil ik gegevens ophalen uit 'rapport week 25' en volgende week uit 'rapport week 26'. Dit wil ik doen door in 'rapport totaal' een cel te vullen met het huidige weeknummer, en de formule die de gegevens ophaalt te laten verwijzen naar die cel. De stanadaardformule is:

='[rapport week 25.xls]Blad1'!A1

Maar hoe zorg ik er nu voor dat de 25 in deze formule wordt aangepast aan het huidige weeknummer die in de eerdergenoemde cel, laten we zeggen B10, staat?

 

[danny147]

Beste verhoog

Probeer deze eens, zie bestandje.

Het bestandje waar je in moet zoeken, moet volgens mij wel open staan.

Groetjes Danny. :thumb:

 

[verhoog]

Hi Danny, dat werkt bijna helemaal goed! Zoals je zegt moet inderdaad het bestand waar je naar verwijst openstaan. In de praktijk wordt dit lastig, omdat de map 52 weekrapporten bevat en 1 totaaloverzicht. Ik zou dus graag willen dat je de weekrapporten gesloten kan houden, en dat hij zelf via de formule op zoek gaat naar het juiste weekrapport.

Is het mogelijk om het pad waar de bestanden in staan (zowel de weekrapporten als het totaaloverzicht staan in dezelfde map) automatisch te achterhalen en deze te gebruiken in de formule? Of lukt dat alleen handmatig?

 

[verhoog]

Ik heb het nu voor elkaar om ook het pad in de INDIRECT functie op te nemen:

=INDIRECT("'"&Q1&"["&S3&".xls]Weektotaal'!B5")

Waarbij in Q1 het pad staat, bijv C:\mijn documenten
En in S3 het weeknummer

Alleen als het bestand niet open is werkt het nog steeds niet. Is dat niet vreemd? Hij zou het bestand toch moeten kunnen vinden met het hele pad in de formule

Of is er misschien nog een andere oplossing?

 

[Warme bakkertje]

Om automatisch het pad op te vragen van een workbook kan je gebruik maken van XLM-macro Document.Lezen
Aangezien alle rapporten in dezelfde map staan kan je dit doen in je Totaaloverzicht
Voor XL2003 ga je als volgt tewerk Invoegen >> Naam >> Definiëren
Bij Naam vul je bv. Pad in en bij Verwijst naar =Document.Lezen(2;"hier zet je de naam van je werkblad waar de formule inkomt")
In je werkblad zet je in cel Q1 =Pad en zodoende verschijnt nu het volledige pad van je bestand.

 

[verhoog]

Ok, maar laat ik dan de INDIRECT functie in stand? Kun je laten zien hoe dan de formule er uit komt te zien die ik hierboven heb gegeven? Alvast bedankt

 

[Warme bakkertje]

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

 

[verhoog]

2 dingen snap ik niet:

1. document.lezen
"hier zet je de naam van je werkblad waar de formule inkomt". Wat bedoel je hiermee? Ik probeer het pad op te vragen van 'rapport week 25'. Hoe doe ik dat in het Totaaloverzicht?

2. In de blog die je noemt staat dat de INDIRECT functie niet gebruikt kan worden als het bestand waar het naar verwijst niet openstaat. Dit zou op te lossen zijn door de functie HYPERLINK erbij te gebruiken. Ik heb dit nu gedaan met de laatste functie die ik hier heb gepost, maar geen resultaat, nog steeds #REF. Enig idee hoe dat kan?

 

[Warme bakkertje]

In Post#3 stel je dat de weekrapporten en je totaaloverzicht in 1 map staan dus als je het pad van Totaaloverzicht opvraagt geldt dit toch ook voor je weekrapporten, nietwaar ?
Wat betreft je tweede vraag zullen de formule-experten alhier moeten inspringen want ik reken mij daar hoegenaamd niet bij.

 

[danny147]

Beste verhoog

Probeer het volgende bestandje eens uit.
Zorg ervoor dat het pad juist geschreven is in cel Q1.
In cel S3 staat een formule voor het weeknummer.

Druk op de kop ophalen.
Er zit een macro in, die het bestandje zal ophalen met de juiste weeknummer.
Na het openen van het bestandje, moet je het maar handmatig sluiten.

De formule in cel A1 zal dan ingevuld worden met de waarde van cel B5 van Weektotaal.

Groetjes Danny. :thumb:

 

[verhoog]

Dank je Danny, dat werkt inderdaad prima! (behalve dat de formule voor het weeknr niet in S3 maar S4 stond, en deze formule niet werkt omdat ik een engelse versie heb, maar daar is wel een oplossing voor met een andere formule!)

Ik ben dus voor een aardig deel uit de brand. En dan nu door voor de wasmachine: ik zou graag het bereik in een vert.zoeken formule op dezelfde manier voorzien van het variabele weeknummer, om zo alle 52 weekrapporten in één keer te doorzoeken op een bepaalde waarde. Dus bijv:

=vlookup(A1;INDIRECT("'"&Q1&"["&S3&".xls]Weektotaal'!B5");2;FALSE)

Het openen en sluiten van 52 weekrapporten is dan geen doen. Zoals ik al zei kwam Warme Bakkertje via zijn link met een mogelijke oplossing:

If the data you wish to reference/lookup is in cell A2 & the INDIRECT range/table reference is in cell J2, then the following formula will correctly return the data in the 2nd column of the lookup table.

=HYPERLINK(VLOOKUP($A2,INDIRECT($J$2),2,0))

alleen werkt deze bij mij op de een of andere manier niet. Krijgt iemand anders m wel aan de gang?

 

[danny147]

Beste verhoog

Volgens mij is de formule in cel A1 hetzelfde in het Engels.

=INDIRECT("'"&Q1&"["&S3&".xls]Blad1'!C4")

Enkeldat Blad1, Sheet1 is.

=HYPERLINK(VLOOKUP($A2,INDIRECT($J$2),2,0))
Het bereik is INDIRECT(J2) is dit juist ?

Groetjes Danny. :thumb:

 

[verhoog]

Wat ik tot nu toe getest heb is de reeds werkende formule:

INDIRECT("'"&Q1&"["&S3&".xls]Weektotaal'!B5")

te vervangen door:

HYPERLINK(INDIRECT("'"&Q1&"["&S3&".xls]Weektotaal'!B5"))

De formule werkt wel, alleen nog steeds niet als het bestand gesloten is. En dat zou die HYPERLINK moeten oplossen. Als ik dit werkend krijg kan ik daarna eens kijken of het binnen een VLOOKUP ook werkt

 

[Warme bakkertje]

Met de Pull-functie van Harlan Grove kan je binnen een VLookUp waarden zoeken in gesloten bestanden. Het enige nadeel is echter dat dit vertragend werkt bij het berekenen. Ik sta natuurlijk steeds tot je beschikking wil je hierover verdere informatie verkrijgen.
Gaat het over 1 waarde ophalen uit een gesloten bestand kan dit ook met een Excel4Macro.

 

[verhoog]

Ik vraag me sowieso af of het gaat lukken wat ik wil maken, qua snelheid. Ik heb een matrix van 120 rijen en 52 kolommen. In elk van de cellen komt een zoekformule die dus in de gesloten bestanden moet gaan zoeken om een waarde op te halen. En dat ook nog op een niet al te snel bedrijfsnetwerk. Maar ik dacht, proberen kan altijd.

Ik zou de Pull-functie daarom nog even willen laten rusten. Ik hoop dat er hier nog iemand langs komt die een andere oplossing heeft voor het zoeken in gesloten bestanden, bijv via die HYPERLINK