• 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.

zoeken en vervangen

Status
Niet open voor verdere reacties.
J

jowey

Gebruiker
Lid geworden
18 mei 2017
Berichten
98
Via zoeken en vervangen pas ik per week bepaalde formules aan omdat dat met doorvoeren niet lukt.
Nu loop ik echter tegen een probleem op. In de formules staat een verwijzing naar een ander excel bestand.
Echter worden die bestanden elke week klaar gezet. Dus week 32 bijvoorbeeld staat nog niet klaar.
Het bestand dat ik maak is echter voor heel het jaar.

Als ik zoek en vervang gebruik vervangt hij dan alleen de eerste cel en daarna opent hij een box om een bestand te zoeken. Als ik dan annuleren druk
stop het hele proces.

Is er een manier om toch alle waarden te vervangen. Het gaat letterlijk om een getal vervangen. Bestandsnaam is telkens hetzelfde alleen weeknummer loopt op.

Bijgaand een voorbeeld van een week die ik selecteer en dan met zoeken en vervangen week 22 naar week 23 vervang

Bekijk bijlage ZOEKEN EN VERVANGEN.xlsx
 
Hoi,

met dit bestand kan ik niks staan alleen verwijzingen in.
 
Beetje een lastig voorbeeld, maar wellicht zoek je zoiets?
Code: 
E6: =ALS.FOUT(INDIRECT("'G:\Operations\pmm mastersheet stad\2017\[PMM Mastersheet stad week "&$A$1&" 2017.xls]PMM DATA'!"&ADRES(RIJ()+2;KOLOM()-2));"")
Deze formule kun je wel doortrekken en geeft een lege cel als een bestand niet bestaat (of niet geopend is).

In A1 moet dan het gezochte weeknummer staan.
 
Laatst bewerkt:
Beide bedankt voor de reactie.
Voorbeeld is idd lastig maar geeft wel precies weer waar ik tegenaan loop.

Goed idee alex. Helemaal niet aan gedacht aan als.fout
Was helemaal gefixeerd op zoeken en vervangen.

Maar als ik mij iet vergis moet bij indirect de andere werkmap openstaan toch?
Kan ik indirect niet beter weglaten en gewoon als.fout gebruiken met lege waarde bij fout en waarde uit werkmap als die werkmap bestaat?

Ik ga het zo proberen :)
 
Laatst bewerkt:
Ik kan het helaas niet 100% testen aangezien ik niet op werk ben en geen toegang heb tot netwerkschijf.

Je geeft aan dat A1 gezocht weeknummer moet staan.
Bedoel je daarmee dat ik in A1 een 1 moet typen en als ik dan formule doorvoer hij overal 1 bij optelt? Of heb ik het verkeerd begrepen?
 
Als je in A1 een 1 intypt gaat de formule zoeken in het bestand voor week 1: "PMM Mastersheet stad week 1 2017.xls".

De cel in dit bestand waarnaar verwezen wordt gaat via het onderdeel: ADRES(RIJ()+2;KOLOM()-2). Deze gaat mee met doorvoeren naar rechts/onder. Week 1 blijft vast staan.

Als de formule in het voorbeeldbestand dus op een andere plek staat dan in je eigen bestand zul je dit nog aan moeten passen.
 
Laatst bewerkt:
Celverwijzing had ik begrepen idd en die werkt ook prima bij doorvoeren.

Echter weeknummer blijft mij nog onduidelijk.
Zal ik anders complete bestand plaatsen?

Probleem is namelijk bij de lay-out dat er ongeveer 7 rijen zitten tussen elke week die in het voorbeeld zat.
Vandaar dat ik zoeken en vervangen gebruikte wat hier overigens nu wel kan. Enige nadeel is dan dat ik dat 52 keer moet doen.
 
Allereerst: ik heb een lege regel ingevoegd tussen week 1 en 2 zodat de layout overal gelijk is (3 witregels tussen weken).

De weeknummers staan nu op rij 5, 20, 35, 50 etc. Om de 15 rijen dus. Dit heb ik ingebouwd in de formule middels het rode stukje.
Code: 
=ALS.FOUT(INDIRECT("'G:\Operations\pmm mastersheet Stad\2017\[PMM Mastersheet Stad week "&[COLOR="#FF0000"]INDIRECT("$A$"&5+15*INTEGER((RIJ()-5)/15))[/COLOR]&" 2017.xls]PMM DATA'!"&ADRES(RIJ()+2;KOLOM()-2));"")

Deze formule is nu te kopiëren zonder dat je handmatig de weeknummers hoeft aan te passen. Eventueel kan je ook nog een jaartalverwijzing meenemen zodat je voor volgend jaar ook al klaar bent.

PS Het bestand wordt wel erg traag met al die indirecte verwijzingen....
 
Laatst bewerkt:
integer is een functie die ik nog niet ken of eerder gebruikt heb. Ik begrijp de opzet van je formule gelukkig wel.
Ik ga je formule op mijn gemak even ontleden. Hij werkt iig perfect.
Hopelijk begrijp ik m straks en kan ik m bij de uren ook toepassen.

Thnx Alex :thumb:
 
Een alternatief, waarbij in ieder geval iets minder de functie INDIRECT wordt gebruikt, is nog de functie VERSCHUIVING:
Code: 
X6: =ALS.FOUT(INDIRECT("'G:\Operations\pmm mastersheet Stad\2017\[PMM Mastersheet Stad week "&[COLOR="#FF0000"]VERSCHUIVING($A$5;15*INTEGER((RIJ()-5)/15);)[/COLOR]&" 2017.xls]PMM DATA'!"&ADRES(RIJ()+2;KOLOM()-2));"")
 
Laatst bewerkt:
collega geeft helaas aan dat hij geen data toont. Ben morgen zelf op werk dan ga ik het nalopen.

Even ter verduidelijking. Het enige variabele is het bestand waar hij moet zoeken.
bij elke week moet hij wel naar dezelfde cellen verwijzen. Dus C8 t/m I8, C9 t/m I9 etc

Omdat de celverwijzingen nu wat complexer zijn en ik de bronbestanden niet heb kan ik nu niet 1,2,3 achterhalen of de verwijzing kloppen.

Verwijst de formule nu wel naar die genoemde cellen ALex?
 
Die verwijzingen ga ik niet uitzoeken, dat kost me teveel tijd.

Maar: de formule staat nu in X6 i.p.v. in E6 zoals in je oorspronkelijke voorbeeldje...

Uit post #6:
Als de formule in het voorbeeldbestand dus op een andere plek staat dan in je eigen bestand zul je dit nog aan moeten passen
Klik om te vergroten...
Hoe zou je het stukje ADRES(RIJ()+2;KOLOM()-2) nu aanpassen?
 
Laatst bewerkt:
Ik zou zeggen

ADRES(RIJ()+2;KOLOM()-21)

aangezien de rij 8 is en de formule begint op rij 6
en de kolom is C dus 3 en X is kolom 24

Maar ik moet eerlijk bekennen dat ik nog niet zo veel met kolom en rij verwijzingen heb gewerkt.
ik begrijp de formule wel per cel. Maar als ik m doorvoer wat gebeurt er dan?
1 naar beneden bijvoorbeeld. Hoe weet excel dat het dan D8 word? Dat gedeelte van de formule begrijp ik helaas niet
 
Laatst bewerkt:
Het wordt weer iets complexer aangezien de kolom per rij één moet toenemen, en het rijnummer per kolom één moet toenemen.

Omdat ADRES(8;3)=$C$8 wordt het dus voor X6: =ADRES(KOLOM()-16;RIJ()-3).

Toelichting:
Code: 
X6: =ADRES(KOLOM()-16;RIJ()-3) =ADRES(KOLOM(X6)-16;RIJ(X6)-3) =ADRES(24-16;6-3) =ADRES(8;3) =$C$8
Y6: =ADRES(KOLOM()-16;RIJ()-3) =ADRES(KOLOM(Y6)-16;RIJ(Y6)-3) =ADRES(25-16;6-3) =ADRES(9;3) =$C$9
Z6: =ADRES(KOLOM()-16;RIJ()-3) =ADRES(KOLOM(Z6)-16;RIJ(Z6)-3) =ADRES(26-16;6-3) =ADRES(10;3) =$C$10
etc.

X7: =ADRES(KOLOM()-16;RIJ()-3) =ADRES(KOLOM(X7)-16;RIJ(X7)-3) =ADRES(24-16;7-3) =ADRES(8;4) =$D$8
Y7: =ADRES(KOLOM()-16;RIJ()-3) =ADRES(KOLOM(Y7)-16;RIJ(Y7)-3) =ADRES(25-16;7-3) =ADRES(9;4) =$D$9
etc.

Complete formule wordt dus m.i.:
Code: 
X6: =ALS.FOUT(INDIRECT("'G:\Operations\pmm mastersheet Stad\2017\[PMM Mastersheet Stad week "&VERSCHUIVING($A$5;15*INTEGER((RIJ()-5)/15);)&" 2017.xls]PMM DATA'!"&ADRES(KOLOM()-16;RIJ()-3));"")
Wordt wel ingewikkeld... en foutgevoelig.
 
Laatst bewerkt:
Betrap ik je nu op een foutje :)

adres is C8, C is dan toch gelijk aan 3 en X is gelijk aan 24 dus dan is het toch -21 ipv -16?
Of snap ik de formule niet :(

Bedankt voor je uitleg maar is het dan niet verstandiger voor mij om het eerste deel van de formule te gebruiken voor het opzoeken van de juiste werkmap
en in het 2e gedeelte ipv ADRES gewoon met vaste CELLEN te werken. Ik kan de formule zoals hij nu is niet 52 weken doorvoeren maar wel via de zoek en vervang functie overal inzetten.

Dan moet ik wel 52 keer inplakken maar hoef ik voor 2018 bijvoorbeeld maar 1 keer zoek en vervang te gebruiken om het jaartal te wijzigen.

Code: 
ALS.FOUT(INDIRECT("'G:\Operations\pmm mastersheet Stad\2017\[PMM Mastersheet Stad week "&VERSCHUIVING($A$5;15*INTEGER((RIJ()-5)/15);)&" 2017.xls]'PMM DATA'!$C$8;"")

En dan voor 1 week alles invullen
 
Vaste celverwijzingen kan maar hoeft dus niet per se. Is een keuze. De eerste keer is het veel werk om alles aan te passen.

En nee geen foutje, de formule is goed... Zie de toelichting in post #17.
 
Hey Alex,

het heeft even geduurd maar ik begrijp het eindelijk.
Je gebruikt functie KOLOM bij rijgetal en RIJ bij kolomgetal

Nu heb ik m werkend van X6:AD12 met wat wijzigingen. Omdat ik C8,C9,C14,C16,C18,C21 en C25 moet hebben. De -3 blijft overal gelijk.

Nu een laatste vraagje. Dit houdt voor zover ik heb kunnen testen wel in dat ik bij elke nieuwe weektabel (volgende is X21:AD27) weer bij adres andere waarden moet gebruiken.

Was het jouw voorstel om dit 52 keer te doen of had je een andere methode achter de hand daarvoor?

en nog bedankt voor het huiswerk. Het heeft wat tijd gekost maar nu begrijp ik de verwijzingen stukken beter :thumb::thumb:
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan