• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Externe verwijzingen naar bronbestanden met veranderende bestandsnamen

Status
Niet open voor verdere reacties.
T

Tcgielo

Gebruiker
Lid geworden
20 dec 2016
Berichten
24
Hoi Excelkoningen,

Ik ben bezig om een dashboard te maken waarin gegevens van 40 excelbestanden worden samengevoegd. Deze bestanden hebben allemaal hetzelfde format. Wat ik nu wil doen is externe verwijzingen maken in mijn dashboard naar telkens dezelfde range cellen in alle 40 bronwerkmappen. Nu wil het echter zo zijn dat de bestandsnaam van deze 40 werkmappen tussentijds wordt gewijzigd. Als dit gebeurd in het geval van een 'normale externe verwijzing' dan kan excel de data niet meer lezen. Zie voorbeeldbestand van hoe ik het in gedachte had met een 'normale' externe verwijzing.

Mijn vraag:
Hoe kan ik dit oplossen zonder dat ik telkens een de externe verwijzing moet aanpassen op het moment dat ik mijn dashboard open? Ik hoop dat dit mogelijk is zonder Macro aangezien ik hier nog niet erg bekend mee ben. Mocht het alleen kunnen met macro hoe zou deze er dan uit moeten zien?

Ik lees het een en ander over indirecte verwijzingen, alleen dit blijkt niet te werken wanneer de bronbestanden gesloten zijn.

Alvast bedankt!
 

Bijlagen

Laatst bewerkt:
@jeanpaul28 Excuus, ik bedoelde uiteraard koningen én koninginnen.
 
Hoi,

Is er wellicht iemand die mij hiermee nog kan helpen? ik ben namelijk nog steeds op zoek naar een oplossing.
Alvast bedankt!
 
Tcgielo,

Ik moet zeggen dat je een 'gevaarlijk' dashboard aan het maken bent om gegevens van 40 Excelbestanden samen te willen voegen. Is er niet een beter bronbestand/systeem beschikbaar?

Als antwoord op jouw vraag:
Wil je dit met Excel willen oplossen dan zal er een logica in moeten zitten in het wijzigen van de bestandsnamen. Dan zou je dit mogelijk met formules op kunnen vangen. Maar persoonlijk zou ik hier niet aan beginnen, de kans op fouten is met 40 bestanden wel erg groot. Dus dan is de vraag of jouw dashboard nog wel werkbaar is.

Een ander mogelijkheid is om een opzoektabel te maken, met de koppeling van klant/leverancier en de pad van het bestand. Dan moet je nog steeds handmatig de wijzigingen doorvoeren trouwens, maar wel op 1 plek. Dan kan je met INDIRECT en VERT.ZOEKEN het dashboard afhankelijk maken van deze opzoektabel.
 
Ik ben het helemaal eens met KristiaanL, dit is vragen om moeilijkheden. Ik denk dat je probeert een probleem op te lossen dat wordt veroorzaakt door een verkeerde opzet.
Ik weet niet hoe je aan 40 verschillende bronbestanden komt. Kan je de opzet van de bestanden eens proberen uit te leggen?
 
Hoi KristiaanL, jkpieterse,

Ik ben het met jullie eens dat de opzet niet ideaal is. Het is alleen bedoeld is tussenoplossing om een bepaalde tijd te overbruggen. De achtergrond: ik werk voor een jong bedrijf die machines produceert en levert. Op het moment dat er een order voor een machine binnenkomt wordt er een projectbestand aangemaakt, met hierin de BOM (bill of material) voor de desbetreffende machine. Dit resulteert in een mapje met ongeveer 40 bestanden (40 lopende projecten).Voor ongeveer 10% van de onderdelen moet R&D nog bepaalde acties ondernemen. Het dashboard is bedoeld om deze artikelen te filteren en deze onder elkaar weer te geven, zodat R&D niet 40 afzonderlijke bestanden moet openen, maar in het dashboard in één oogopslag kan zien wat er gebeuren moet.

Het extern verwijzen lukt allemaal wel, het zit hem er alleen in dat er iedere keer projecten bij komen, die dus handmatig weer in het dashboard moeten worden gezet, waarbij iedere keer de bestandsnaam in de formule moet worden aangepast. Dit kan ik eventueel oplossen door alvast wat nieuwe bestanden ' klaar te zetten' en hiernaar te verwijzen. Maar ieder projectbestand heeft als naam de naam van de klant. De 'klaargezette' bestanden hebben deze naam uiteraard nog niet dus hier vindt weer een wijziging plaats...

Kortom, het blijft veel handwerk om telkens de externe verwijzing in de formule te moeten aanpassen. Hoe kan ik dit dynamischer maken? is het bijvoorbeeld mogelijk om een tabblad te maken met bestandspaden en naar deze cellen te verwijzen in de formule? Of is er een macro die het mapje met projectbestanden automatisch scant en deze vervolgens meeneemt in het dashboard?
 
Even nog advocaat van de :evil: spelen:
Op het moment dat er een order voor een machine binnenkomt wordt er een projectbestand aangemaakt, met hierin de BOM (bill of material) voor de desbetreffende machine.
Klik om te vergroten...
Hoe wordt dit gedaan? Handmatig of via een systeem?
 
Dit wordt handmatig in het Excelbestand gezet (standaard BOM wordt handmatig gekopieerd uit een ander excelbestand en waar nodig handmatig aangevuld).
 
Ik heb hier wat voorbeeldbestanden. Het dashboard en twee projectbestanden als voorbeeld. Het gaat er dus om dat op het moment dat er een nieuwe order binnen komt, de klantnaam moet worden ingevoerd in Kolom A, en dat vervolgens de formules in kolom B en C handmatig aangepast moet worden.

Een oplossing zoals een tabblad met bestandspaden/namen en vervolgens een verwijzing naar die cellen in de formule zou voor mij ook al top zijn. Dan hoef ik maar op 1 plek de wijziging door te voeren.
 

Bijlagen

Wie is op het slimme idee gekomen voor iedere klant een apart Excel Werkboek te maken ?

Zijn meer dan 1 miljoen regels per Excel werkblad onvoldoende om een genormaliseerde tabel met gegevens van 40 klanten te vullen ?
 
Je kan dan een zo een tabel maken:
Bekijk bijlage Doelbestand (KL).xlsx
Waarbij de klantnaam automatisch als bestandsnaam wordt gebruikt. Als dit pad in een formule wilt gebruiken moet dit met een INDIRECT formule.

Toch blijf ik erbij dat je moet nagaan of dit betrouwbare informatie zal opleveren.
 
@snb, de reden hiervoor is onder andere dat er anders meerdere mensen tegelijk in één excelwerkmap moeten werken, waardoor versies overschreven zullen worden etc. Nogmaals, het is inderdaad een suboptimale oplossing, maar we moeten het er even mee doen totdat we het kunnen vervangen voor een goed systeem.

@KristiaanL, zou je het principe wat je noemt van de indirecte formule kunnen toepassen in het voorbeelddashboard die ik hierboven heb geplaatst? Dan kan ik dat bekijken en het principe leren.
 
Code: 
=VERT.ZOEKEN(A2;INDIRECT("'P:\downloads\Test\["&A2&".xlsx]Blad1'!$A$1:$C$16");3;ONWAAR)

zoiets in B2.

Al zie ik dat ook de range niet hetzelfde is bij de klanten en leverancier...
 
@KristiaanL

Excuus, ik bedoelde in het voorbeeldbestand die ik als laatste had toegevoegd (dashboard voorbeeldbestand.xlsm). Zie B9 en C9 als uitgangspunt. In deze formules moet ik allebei 3x de bestandslocatie aanpassen als ik de formule kopieer voor een nieuwe klant. Hier gaat het wel over dezelfde ranges aangezien bestand klant1 en bestand klant2 exact dezelfde opzet hebben.
 
Kijk eens naar de tab Gegevens, Nieuwe Query, Uit Bestand, Uit Map. Daarmee kan je data uit alle bestanden in 1 map importeren en masseren, waarna je een tabel overhoudt met de gegevens uit die 40 bestanden. Een wijziging in een bestand (naam wijziging, nieuw bestand, wat dan ook) is opgelost met 1 druk op de knop vernieuwen. Voorwaare is wel dat de indeling van die bestanden identiek is.
 
@jkpieterse, bedankt voor je tip. Ik ben aan de slag gegaan met een power query met de voorbeeldbestanden als oefening. Dit gaat goed, behalve dat hij de 1e kolom (klantnaam) alleen van het eerste bestand leest. De 2e en 3e kolom worden echter wel van beide bestanden goed uitgelezen. Wat doe ik fout? Heb de bestanden bijgevoegd.
 

Bijlagen

Het probleem is dat in een van beide klant bestanden een spatie staat achter "Customer" in cel A1.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan