(Probleem) Excel: functie voor toekomstige bestanden

[aRegularUser]

Beste Lezers,

Eerst en vooral even mijn excuses aanbieden aan J_F_K en andere gebruikers. Ik dacht niet dat mijn vorig topic al gepost was en heb het daarom nogmaals gepost. Ook had ik Excel nog niet geherinstalleerd en vandaar het late antwoord. Ik heb deze account al even, maar ben nog redelijk nieuw in de wereld der fora.

Ik zit met een probleem op excel dat ik niet kan oplossen. Ik ga het zo goed mogelijk proberen te beschrijven en dan hoop ik dat een van de experts hier zo vriendelijk is om me hier even mee uit de put kan helpen. Ik heb een tamelijke basiskennis over excel: daarmee bedoel ik dat ik weet hoe ik moet verwijzen en weet hoe ik formules moet opstellen en interpreteren.

Het probleem:

Elke dag wordt er op werk een nieuwe lijst gemaakt waarbij uren worden ingegeven en de vertraging die een wagen heeft opgelopen bij vertrek. Deze zouden in de juiste plaats moeten komen in een ander document.

Er bestaat dus een document van deze maand genaamd "November 2014" en elke dag wordt er een nieuw document aangemaakt met de naam: "01112014" , "02112014", "03112014",... . Deze documenten bestaan nog niet in het begin van de maand. In het dagelijks document staat elke dag het vertrek en werkelijke vertrek in van een aantal voertuigen met daarbij de berekende vertraging. Dus bv.:

	voorziene vertrektijd	werkelijke vertrektijd	vertraging
voertuig 1	6:12	6:13	00:01
voertuig 2	7:57	8:03	00:06
voertuig 3	8:00	8:00	00:00

Nu staan er in het document "November" 3 tabbladen (voor elk voertuig, in het echte document zijn dat er veel meer) en in elk tabblad de data van die maand ( van 1 tot 31 november).

Nu moet achter elke datum automatisch het uur van vertraging komen van het juiste document bij het juiste tabblad (bij het juiste voertuig dus) zodat dat automatisch wordt ingevuld.

Oplossing:

Ik dacht eraan dat een formule achter de datum, die verwijst naar het juiste document (ook al bestaat het nog niet) het juiste wel zou doorgeven als het document wordt gecreëerd, aangezien bv.: vertraging telkens op B4 staat bij voertuig 1, en dat doet het ook. De vertraging van voertuig 2 staat dan bv.: op B5 en dat zal ik dan op blad2 (waar voertuig 2 staat) moeten ingeven. Hierbij een voorbeeld van de formules:

datum bij voertuig 1	vertraging
01/11/2014	(formule)=[01112014]Blad1!$B$4
02/11/2014	(formule)=[02112014]Blad1!$B$4
03/11/2014	(formule)=[03112014]Blad1!$B$4

Zoals je kan zien moet dan elke formule anders zijn, aangezien de datum aangepast moet worden. Als ik deze forumes zelf allemaal moet ingeven, dan kan ik de uren beter gewoon overschrijven. Volgende maand is het weer anders (01122014) dus zal ik dat weer allemaal moeten aanpassen.

Kan een expert me hierbij helpen? Ik zou hiervoor een formule willen vinden zodat ik deze niet altijd hoef in te vullen (werd me ook gevraagd dit te regelen).
Alvast bedankt voor het lezen en als u verduidelijking wilt beantwoord ik graag je vragen!

[Belindo]

Check even de INDIRECT functie, daarmee kun je in je formule je datum mee genereren, dan hoef je alleen de formule nog naar beneden te slepen en zet ie overal de datum in je formule goed op basis van de datum die er links staat.

edit:
Let wel; INDIRECT werkt dan niet als het doelbestand gesloten is!

Overigens niet de beste methode waar je aan denkt, wat als het document verwijderd of hernoemd wordt?

Als je een beetje thuis bent in VBA kun je een macro schrijven die de data inlaadt en vervolgens in een centrale tabel gooit. Dan kun je met een simpele pivottable een hoop bereiken qua rapportages.

[ Voor 7% gewijzigd door Belindo op 11-11-2014 14:59 ]

[Witte]

Of je maakt 'vast' alle documenten aan (met lege gegevens).
Waarom trouwens per se Excel??? Dit kan toch heel gemakkelijk met (kleine) Acces-database?
Paar tabelletjes en twee formpjes. Kost je een middagje prutsen, maar dan heb je ten minste consistente data.
Excel-documenten hebben de neiging dat "ze niet van elkaars bestaan" weten.

[ Voor 70% gewijzigd door Witte op 11-11-2014 14:56 ]

[aRegularUser]

Belindo schreef op dinsdag 11 november 2014 @ 14:52:
Check even de INDIRECT functie, daarmee kun je in je formule je datum mee genereren, dan hoef je alleen de formule nog naar beneden te slepen en zet ie overal de datum in je formule goed op basis van de datum die er links staat.

edit:
Let wel; INDIRECT werkt dan niet als het doelbestand gesloten is!

Overigens niet de beste methode waar je aan denkt, wat als het document verwijderd of hernoemd wordt?

Als je een beetje thuis bent in VBA kun je een macro schrijven die de data inlaadt en vervolgens in een centrale tabel gooit. Dan kun je met een simpele pivottable een hoop bereiken qua rapportages.

Beste, Alvast hartelijk bedankt voor het antwoorden. De bestanden gaan niet verwijderd of hernoemd worden, daar kan je van op aan. Zou je dit eens in een formule kunnen gieten zodat ik het begrijp? Naar mijn weten kan je niet een verwijzing naar een ander document (tussen de twee vierkante haakjes) in een formule neerschrijven, of gaat dit wel?

Witte schreef op dinsdag 11 november 2014 @ 14:54:
Of je maakt 'vast' alle documenten aan (met lege gegevens).
Waarom trouwens per se Excel??? Dit kan toch heel gemakkelijk met (kleine) Acces-database?
Paar tabelletjes en twee formpjes. Kost je een middagje prutsen, maar dan heb je ten minste consistente data.
Excel-documenten hebben de neiging dat "ze niet van elkaars bestaan" weten.

Ik heb geen kennis van Acces, en deze Excel bestanden vinden elkaar wel geloof ik? Je kan othc verwijzen naar een excel bestand dat nog niet bestaat? Als deze dan wordt aangemaakt worden de gegevens daarvan automatisch ingevuld, toch? In ieder geval kan ik die documenten niet leeg aanmaken, dat gebeurt niet door mij.

[Belindo]

Aangezien de weg naar een oplossing leerzamer is dan een kant en klaar antwoord;

Naar mijn weten kan je niet een verwijzing naar een ander document (tussen de twee vierkante haakjes) in een formule neerschrijven, of gaat dit wel?

Heb je al gezocht op wat INDIRECT() doet? Binnen INDIRECT() kun je een formule schrijven waarin je weer een referentie of andere formule kunt stoppen.

Zoals vermeld werkt dit niet wanneer het externe bestand gesloten is, hiervoor heb je de INDIRECT.EXT() functie nodig die standaard niet in Excel zit. Hiervoor kun je de MoreFunc add-in installeren.

als je de boel werkend hebt gekregen in INDIRECT.EXT() is de volgende stap om de datum in de bestandsnaam variabel te krijgen, zodat ie op de regel van een andere datum ook in díe file gaat kijken.

Dit doe je door in je formule te verwijzen naar de cel die de datum bevat. Alleen wil je deze nog wel een format van mmddyyyy meegeven. Dit doe je met de TEXT() functie.

Stappenplan:
- Installeer MoreFunc en krijg de add-in aan de praat in Excel (voor 2010 en hoger zijn wat handmatige stappen nodig)
- Giet je bestaande formule in de INDIRECT.EXT() functie, je resultaat zal hetzelfde zijn
- Probeer je datum het format ddmmyyyy te geven met de TEXT() functie, wanneer je in Excel bijvoorbeeld een 11/1/2014 datum hebt staan, wil je deze met een formule veranderen naar 01112014
- Plaats de formule waarin je de datum wijzigt naar ddmmyyyy in de INDIRECT.EXT() formule op de plek van de bestandsnaam

Nogmaals, dit is niet de beste oplossing, ik weet niet om hoeveel bestanden het uiteindelijk gaat, maar élke formula zal dus in een bestand kijken om de waarde op te zoeken, ik weet niet of dit de boel erg gaat vertragen.

Een betere oplossing zou zijn om (semi)automatisch de dagelijkse bestanden te importeren in een centrale tabel dmv. een macro in VBA. Dan heb je één file met daarin de data die je hebben wilt, zo kun je veel makkelijker een rapport maken.

[aRegularUser]

Beste Belindo,

Bedankt voor je uitgebreide antwoord! Het heeft me zeker en vast al een pak vooruit geholpen! Ik ben er echter nog niet. Ik heb opgezocht wat de indirect-functie doet en waar hij voor dient. Ik heb echter nog niet gevonden hoe ik de formule werkende krijg. Ik wil hem namelijk laten werken zonder add-ins voor excel. Die hebben we op werk namelijk niet en ik weet niet of ik dat zomaar mag gaan installeren. In ieder geval ben ik eens aan het proberen geslagen en heb ik het volgende:

Ik wil duidelijk =[01112014]Blad1B4 bekomen, waarbij de bestandnaam variabel is. Daarmee kan ik dan best indirect() gebruiken en voor de bestandnaam de tekst() formule gaan toepassen. Dat lukt allemaal vrij goed afzonderlijk, maar samen gaat dit niet voor mij. Doe ik iets fout of is het effectief niet mogelijk?
Ik heb nu iets in deze lijn:

=indirect("[=TEKST(A4;"ddmmjjjj"]Blad1!B4";WAAR)

[Belindo]

Je sluit je TEKST( niet af met een ) ná de jjjj" Tevens moet je je indirect afsluiten met "& voordat je de text() gaat doen, en daarna weer verder met &"

Even uit m'n hoofd omdat ik geen NL Excel heb:
=indirect("["&TEKST(A4;"ddmmjjjj")&"]Blad1!B4")


Nogmaals, je moet dan wel het bestand open hebben waar je naar zoekt, is dit echt iets wat je wilt? Wat als je straks voor een jaar data/bestanden hebt. Dan moet je 365 bestanden open hebben staan zodat je rapport alle data kan inlezen.

[ Voor 16% gewijzigd door Belindo op 19-11-2014 11:15 ]

[Verwijderd]

Met een formule verwijzen naar een (nog) niet bestaand bestand of blad zal een foutmelding veroorzaken (#VERW) die je kunt opvangen met: =als.fout(jouwformule;"Hier wat er bij die fout moet gebeuren") .
Verder ben ik het met Belindo eens dat een macro hier meer aangewezen is dan het gebruik van formules. Om een goede macro te schrijven is inzage in de gebruikte bestanden noodzakelijk.

[aRegularUser]

Hij werkt eindelijk! Ik was de dubbele aanhalingstekens en het &-teken vergeten. Ik zou graag alle mensen die hebben gereageerd willen bedanken. Specifiek Belindo, die me met veel uitleg en geduld heeft bijgestaan, ben ik heel dankbaar. Met als.fout voor de formule kan je vervelende vakjes met #VERW indd gemakkelijk laten verdwijnen. Weer een leuke en handige tip!