Hoi Peter,
Hartelijk dank voor je antwoord! Helaas denk ik niet dat ik deze oplossing kan gebruiken. Ik wilde mijn eerste bericht niet meteen langer maken dan Oorlog & Vrede, dus ik had wat dingen weggelaten waarvan ik dacht dat het geen verschil zou maken.
1. Het bereik is niet altijd constant. Ik ben nu bezig met een model spreadsheet waar ik elke maand een kopie van maak voor de gegevens van die maand. In maanden met minder dan 31 dagen verwijder ik één (of meer, in februari) kolommen uit het deel waar ik de dagelijkse gegevens invoer. Als ik dit probleem met een formule in een cel oplos, dan zal Excel, automatisch alle verwijzingen naar een bereik aanpassen (dus bv J16:AN16 wordt J16:AM16). Met een macro lukt dat niet. Dat is nog wel op te lossen (vervang de FOR lus door een WHILE lus en klaar), maar om de TV programma's van de verkoopkanalen te citeren: er is meer.
2. Het gaat in het echt niet om één formule. De formule waar ik nu mee bezig ben komt uiteindelijk in cel AP16. En in cel AO16 komt een vergelijkbare formule, die echter de waarde niet uit rij 2 haalt maar uit rij 16 zelf.
En dan, als de formules correct werken, ga ik ze kopiëren naar een heleboel andere rijen (vandaar de combinatie van absolute verwijzingen naar rij 2 en relatieve verwijzingen naar de andere rijen). Totaal 70 keer. Dus 70 rijen x 2 formules per rij = 140 exemplaren van deze formule.
3. Een macro die ik expliciet moet starten en dan een venster toont met de cel waar ik moet kijken is een goed begin, maar kan niet mijn einddoel zijn. Denk aan die 140 keer! Ik wil dat Excel helemaal zelf de waarde opzoekt en in de doelcel plaatst. Het liefst helemaal automatisch als ik iets wijzig (zoals met normale Excel formules).
Vandaar dus mijn behoefte om dit met een formule op te lossen die alleen de standaard functies van Excel gebruikt. Het stomme is, ik heb deze formule (in iets andere vorm, dat wel) ergens van internet geplukt, en als ik het venster gebruik dat me helpt de parameters te vullen dan lijkt de functie gewoon het correcte resultaat te geven - alleen hij weigert het ook in de cel te zetten. Kennelijk heb ik iets fout gedaan bij het kopiëren van de formule - maar wat?
Als het écht niet kan met een formule, dan zal ik inderdaad naar VBA moeten uitwijken. Maar dan nog zal ik verder moeten werken vanaf jouw code. Daar heb ik dan vast ook wel weer vragen over, maar ik geef er op dit moment de voorkeur aan om nog even langer te zoeken naar een oplossing met formules.
Groetjes,
Hugo