Excel: In bereik bijv. A2:D10 werken met variabele waarden

[SrA]

Mijn vraag
In een formule waarin met celbereik wordt gewerkt wil ik werken met een variabele getalswaarde maar Excel accepteerd dit niet op de manieren waarop ik het tot nu toe geprobeerd heb:

Bijv. in de formule ='Controlelijst'!B5:D3505 wil ik het bereik beperken door i.p.v. 3505 te werken met een formule die 'kijkt' tot hoever het bereik maar hoeft te worden ingesteld.

Dit laatste doe ik via de formule: =X.VERGELIJKEN(Personen!F514;'Controlelijst'!B6:B3505;0;-1), de uitkomst hiervan is 5

Deze formules wil ik 'in elkaar schuiven':
='Controlelijst'!B5:D5+X.VERGELIJKEN(Personen!F514;'Controlelijst'!B6:B3505;0;-1))

Dit zou als resultaat het bereik: ='Controlelijst'!B5:D5+5 (=D10) op moeten leveren, maar Excel snapt nog niet dat hij de uitkomst van X.VERGELIJKEN achter de D moet zetten

Weet iemand of dit überhaupt mogelijk is binnen Excel en zo ja wat ik nog moet aanpassen aan de formule

[g0tanks]

Om een variabel celbereik op te geven moet je gebruik maken van INDIRECT, functie

[Sirhc_95]

Je zou dit inderdaad met de indirect formule kunnen doen, maar wellicht is een dynamisch bereik (dynamic array) een beter alternatief: https://exceljet.net/glos...0a%20%22spill%20range%22.

[Ronenmon]

staan veel voorbeelden op internet, bijvoorbeeld: =LOOKUP(2,1/(B:B<>""),B:B) levert laatste waarde op in de lijst waarna je met =Match (LOOKUP(2,1/(B:B<>""),B:B);B:B;0) ook de lokatie (row) kan bepalen

.... of nog korter =LOOKUP(2,1/(b:b<>""),ROW(b:b))

volgende stap is die waarde optellen bij jouw startwaarde (D5, dus 5), laten we zeggen als resultaat 10 en inderdaad mbv indirect functie aan elkaar plakken dmv =indirect("Controlelijst'!B5:D"&10) welke je weer in een functie kan plakken

[ Voor 43% gewijzigd door Ronenmon op 30-03-2022 21:33 ]

[SrA]

Formule is als volgt geworden:

=INDIRECT("Controlelijst'!B"&5+(X.VERGELIJKEN(Personen!F514;'Controlelijst'!B6:B3505;0;1))&":D"&5+(X.VERGELIJKEN(Personen!F514;'Controlelijst'!B6:B3505;0;-1)))

De uitkomst is dan - in dit geval - de volgende formule: 'Controlelijst'!B6:D10 met alleen de regels die bij de betreffende persoon horen i.p.v. de hele ;'Controlelijst'!B6:B3505.

(Hierbij is de Controlelijst natuurlijk wel gesorteerd op de kolom met de waarden waarop met F514 wordt gezocht, anders het het geen zin om het 1e en laatste recordnummer op te zoeken )

Met de functie FILTER verfijn ik het resultaat vervolgens en vergelijk dat met een celwaarde uit een andere tabel

[ Voor 23% gewijzigd door SrA op 02-04-2022 14:19 ]

[Reptile209]

Waarom filter je dan niet in één keer op de persoon in je FILTER? Dan hoef je helemaal niet met een bereik te klooien.