Doornummeren binnen ALS.FOUT functie

[VNBart]

Goedemiddag,

Ik zit met een probleem dat mijn pet te boven gaat. Ik twijfel of het überhaupt mogelijk is wat ik wil. Ik probeer het te omschrijven:

1. Wij houden onze offertes (die we maken in Excel) bij via een overzichtslijst. Deze laatste is als bijlage toegevoegd.
2. In de overzichtslijst worden enkele gegevens die in de offerte staan ingeladen. Dit betreft deze 4 variabelen: klantnaam, prijs, afhaalpunt en e-mailadres.
3. Dit werkt prima met deze functie: =ALS.FOUT('G:\Marketing\Vensterbank-Natuursteen.nl\Offertes\[Offerte VNT190001.xlsx]Offerte maatwerk'!$E$14;"")
4. Per variabele wijzigt natuurlijk de cel (achteraan in de functie). Daarnaast wijzigt per regel het offertenummer (dus op 4 plekken).

Probleem:

- Ik moet nu pér regel op 4 plekken het offertenummer wijzigen in de functie, zodat de gegevens uit die betreffende offerte worden ingeladen. Is dit op een andere manier mogelijk, zodat de hoeveelheid handwerk beperkt wordt?
- Het offertenummer staat in kolom B. Is daarmee iets mogelijk?


Ik hoop dat iemand hier een slimme workaround voor kent of kan bedenken.

Vriendelijk groeten en bij voorbaat dank voor het lezen van dit probleem,

Bart

 

[Gijsbert1]

Ik kan niet testen of het werkt aangezien de formules naar bestanden verwijzen.
Maar met de indirect functie zou het moeten kunnen, dan wordt het zoiets in C7:

=ALS.FOUT([B][/B][COLOR="#FF0000"]INDIRECT("[/COLOR]'G:\Marketing\Vensterbank-Natuursteen.nl\Offertes\[[COLOR="#FF0000"][/COLOR][B][COLOR="#FF0000"]"&B7&"[/COLOR][/B].xlsx]Offerte maatwerk'!$E$14[B][COLOR="#FF0000"])[/COLOR][/B];"")

En doortrekken naar beneden

 

[VNBart]

Ik ga het meteen uitproberen Gijsbert en zal mijn bevindingen terugkoppelen. Dank voor de reactie.

 

[VNBart]

Hmm, ik krijg het nog niet aan de praat. Ik vermoed dat het niet helemaal goed gaat met het genereren van de bestandsnaam? Ik mis daarin nu ook het woordje Offerte. Heb daarom B7 gewijzigd naar: Offerte VNT190001 maar helaas zonder resultaat. Ik mis ook een rood haakje sluiten, zo te zien?

 

[roeljongman]

er mist volgens mij een afsluitende dubbele aanhalingstekens in de formule van gijsbert

probeer het eens met deze..

=ALS.FOUT(INDIRECT("'G:\Marketing\Vensterbank-Natuursteen.nl\Offertes\["&B7&".xlsx]Offerte maatwerk'![COLOR="#FF0000"]"&[/COLOR]$E$14);"")

(rode tekens zijn de enige aanpassing)

 

[VNBart]

Dank Roel. De foutmelding is nu inderdaad verdwenen. De cel blijft nu echter leeg... Nog suggesties? Klopt de bestandsnaam wel op deze manier? Zie eventueel deze screenshots.

Overzichtslijst met formule:

Locatie en bestandsnaam op server:

 

[E v R]

E14 in welk bestand? Waar de formule staat of waar je zoekt?

 

[VNBart]

E14 is de cel in het Offerte document waar de klantnaam staat. Dat klopt dus. Dat veld is ook niet leeg.

 

[Gijsbert1]

Op basis van je printscreen:

=ALS.FOUT(INDIRECT("'G:\Marketing\Vensterbank-Natuursteen.nl\Offertes\[[COLOR="#FF0000"]Offerte [/COLOR]"&B7&".xlsx]Offerte maatwerk'!$E$14);"")

 

[VNBart]

Beste Gijsbert,

Dat ontbrekende woord in jouw formule was me opgevallen. Daarop doelde ik met deze opmerking

Hmm, ik krijg het nog niet aan de praat. Ik vermoed dat het niet helemaal goed gaat met het genereren van de bestandsnaam? Ik mis daarin nu ook het woordje Offerte. Heb daarom B7 gewijzigd naar: Offerte VNT190001 maar helaas zonder resultaat. Ik mis ook een rood haakje sluiten, zo te zien?

Zowel de naam in cel B7 aanpassen alsmede het toevoegen van 'Offerte[spatie]' in de functie heeft niet het gewenste resultaat. De cel blijft leeg :-(

 

[Gijsbert1]

Helaas moet ik op mijn oplossing terugkomen.
Normaliter bij verwijzing naar een bronbestand wat niet is geopend bevat de verwijzing het gehele pad.
Helaas werkt dat toch niet in combinatie met de indirect functie.

Als het bronbestand geopend is doet hij het vermoedelijk wel, maar ik denk dat je dat niet wilt.

 

[E v R]

Yep Indirect werkt niet op gesloten bestanden, is CTRL H zoeken-vervangen niet iets als eenmalige aktie?

 

[VNBart]

Goedemorgen,

Ik heb zowel de bronbestanden (Offertes) geopend staan alsmede de overzicht sheet. Maar dat werkt ook niet. CTRL-H is in dit geval ook geen oplossing. Hoe wij er tot op heden mee werken is als volgt:

- De overzicht sheet bevat de ALS.FOUT functie zoals hij in het voorbeeld bestand stond.
- Wij creëren dagelijks tientallen offertes oftewel nieuwe documenten.
- Als de overzicht sheet reeds geopend is worden de variabelen daarin ingeladen zodra een offertebestand wordt geopend.
- Als de overzicht sheet gesloten is en je opent hem, krijg je een update melding en dan laadt hij alle variabelen uit de gecreëerde documenten in. Deze hoef ik dan dus niet geopend te hebben.

Bovenstaande werkt eigenlijk perfect en bespaart enorm veel tijd, echter moet ik de functie dus handmatig in de overzicht sheet aanpassen per regel (om de documentnaam van het doelbestand te wijzigen). Het zou toch echt fantastisch zijn als iemand hier een fix voor kan bedenken ;-)

 

[Gijsbert1]

Je ontkomt er dan eigenlijk niet aan om een macrootje te gebruiken.

Sub gijs()
Dim rng As Range
    For Each rng In ActiveSheet.Range("C7:C56")
        rng.Replace What:="VNT??????", Replacement:=rng.Offset(, -1).Value
    Next
End Sub

 

[VNBart]

Dank wederom voor je inbreng, Gijsbert. Ik ben nog niet goed bekend met macro's, dus ik begrijp niet meteen hoe ik je code kan gebruiken. Maar ik ga er meteen mee aan de slag. Mocht ik vastlopen, dan kan ik hier hopelijk weer hulp vinden. Nogmaals dank.

 

[Gijsbert1]

heb er een voorbeeldbestandje bij gedaan

 

[VNBart]

Hoi Gijsbert, ik heb het bekeken en dit lijkt te kunnen werken!!! Kan ik de code kopiëren en in dezelfde macro zetten voor alle 4 de kolommen? Heb al wat geprobeerd, maar ik doe iets fout. Of moet ik 4 aparte marco's maken?

 

[Gijsbert1]

zie rood voor wijzigingen in de code.

Sub gijs()
Dim rng As Range
    For Each rng In ActiveSheet.Range("C7:[COLOR="#FF0000"]N56[/COLOR]")
        rng.Replace What:="VNT??????", Replacement:=[COLOR="#FF0000"]Cells(rng.Row, 2)[/COLOR].Value
    Next
End Sub

 

[VNBart]

Gijsbert, het werkt! Dit is geweldig en gaat ons flink wat tijd besparen! Enorm bedankt, ook aan de andere bijdragers!

 

[Gijsbert1]

Graag gedaan hoor! (Al had ik een oplossing zonder tussenkomst van VBA charmanter gevonden)
Vergeet je vraag niet op opgelost te zetten! :thumb: