[Excel 2003] functie indirect voor rijen en kolommen

[Brangus Glee]

Ik ben bezig met een bestand waarin ik gebruik maak van de indirect functie.
De formule die ik wil gebruiken lukt voor een groot gedeelte, maar enkele
onderdelen krijg ik niet werkend zoals ik wil.

Dit is de formule:

=SOM.ALS(INDIRECT((HORIZ.ZOEKEN(Invoer!$D$15;Hulpblad!$A$30:$IR$33;4;0))&8&":DX"&223);'Doorloop overzicht'!B135;INDIRECT("'"&C231&"'!bz8:DX223"))

Deze formule resulteert weliswaar in de gewenste uitkomst, maar om de
formule goed te laten werken wil ik in het eerste gedeelte het bereik
flexibel maken en dit ook in het 2e gedeelte doen waarin de optelling van de
som.als wordt gedaan.
De horiz.zoeken functie gebruik ik om het beginbereik variabel te kunnen
maken. Wat ik wil is dat ik ook het eindbereik variabel wil maken. Als ik een
soortgelijke horiz.zoeken functie in het eindbereik van het eerste gedeelte
plaats dan geeft hij een foutmelding.
Voor het 2e gedeelte waarin het optelbereik van de som.als staat wordt
verwezen naar een ander tabblad. Als ik hier de formule horiz.zoeken wil
inpassen geeft hij ook foutmeldingen.
Ik heb zelf een vermoeden dat ik mogelijk meer indirect functies moet
nesten, maar na wat experimenten vind ik in ieder geval nog geen oplossing.

Als iemand suggesties, tips en of ideeen heeft dan hoor ik het graag.

Alvast bedankt.

[Reptile209]

Dè beste tip voor problemen met indirect() is altijd om het eerst even achterwege te laten en te kijken naar de string die je samenstelt. Maak eens een handmatige verwijzing zoals hij zou moeten worden, en vergelijk dat met de uitkomst van:

code:

=(HORIZ.ZOEKEN(Invoer!$D$15;Hulpblad!$A$30:$IR$33;4;0))&8&":DX"&223);'Doorloop overzicht'!B135;INDIRECT("'"&C231&"'!bz8:DX223")

Makkelijker voorbeeld: als je handmatig zou uitkomen op 'Blad1'!A1, moet het deel tussen haakjes bij de volgende functie daar ook op uitkomen als er 1 in B3 staat:

code:

=indirect("'Blad'"&10/10&"!A"&B3)

dus je test het met

code:

="'Blad'"&10/10&"!A"&B3

Dat is echt veel makkelijker trouble-shooten dan naar je formule(s) te staren!

[ Voor 4% gewijzigd door Reptile209 op 29-07-2009 20:47 ]

[Verwijderd]

idd. of je kan ook de formule zo laten, een logisch deelgebied selecteren in de formulebalk en dan F9 drukken om dat gedeelte te evalueren, of deze functie in een apart venster oproepen via het extra menu.

[Brangus Glee]

Bedankt voor jullie tips, het vereenvoudigt het analyseren van de formules.
Echter ik blijf met een probleem zitten waarvoor ik hoop dat er een oplossing is:

De volgende formule =INDIRECT(B9&"!$E$4"&$D$2) levert 0 op met onderstaande gegevens

In B9 staat de naam van het tabblad (test)
In Test E4 staat de letter a
In Test D2 staat het cijfer 5

De formule heeft als gewenste uitkomst de waarde van cel A5 in tabblad test. Door de aanhalingstekens ziet hij het gedeelte "!$E$4" als tekst en resulteert het in een verwijzing naar cel E45 in tabblad test.
Dit geeft =B9&"!$E$4"&$D$2 ook aan. Hoe krijg ik het nu voor elkaar dat de formule na het uitroepteken ook een celverwijzing kan verwerken? Zonder deze aanhalingstekens geeft excel een foutmelding.

Wederom alvast bedankt.

[onkl]

=INDIRECT(B9&"!$"&E$4&"$"&D$2)

offtopic:
Hé. Grappige F9 functionaliteit. Die is handig bij hinderlijk lange formulestapelingen.

[ Voor 64% gewijzigd door onkl op 03-08-2009 15:34 ]

[Brangus Glee]

Bedankt, hiermee kan ik weer verder. De celverwijzing van E4 heeft nu betrekking op het huidige tabblad, maar dat is volgens mij relatief makkelijk te veranderen in het tabblad test.

Die F9 functionaliteit kan inderdaad handig zijn, vooral bij geneste als functies.

[Reptile209]

Snap je nu ook het verschil tussen je eigen formule en die van onkl? Alles dat tussen aanhalingstekens staat, zal letterlijk zo gebruikt worden (in jouw geval "!$E$4". Van alles dat niet tussen aanhalingstekens staat, gebruikt de formule de waarde uit de cel. Alle losse brokjes 'plak' je vervolgens met & operators aan elkaar.
Onkl maakt het nog wat ingewikkelder met de toevoeging van de $-jes die je niet persé nodig hebt. =INDIRECT(B9&"!"&E$4&D$2) zou ook werken en levert test!a5 op in plaats van test!$a$5.

[onkl]

Reptile209 schreef op maandag 03 augustus 2009 @ 15:50:
Snap je nu ook het verschil tussen je eigen formule en die van onkl? Alles dat tussen aanhalingstekens staat, zal letterlijk zo gebruikt worden (in jouw geval "!$E$4". Van alles dat niet tussen aanhalingstekens staat, gebruikt de formule de waarde uit de cel. Alle losse brokjes 'plak' je vervolgens met & operators aan elkaar.
Onkl maakt het nog wat ingewikkelder met de toevoeging van de $-jes die je niet persé nodig hebt. =INDIRECT(B9&"!"&E$4&D$2) zou ook werken en levert test!a5 op in plaats van test!$a$5.

Inderdaad. Over absoluut/niet absoluut hoef je je in beginsel niet druk te maken als je formules opbouwt binnen "indirect". Je ziet als je cursor in de formulebalk staat ook dat de gebruikte cellen een kleurtje krijgen. Als je zit te rommelen met aanhalingtekens niet, altijd een soort subtiele hint dat het tijd voor koffie wordt.

[Brangus Glee]

Indirect en absoluut maken is niet echt relevant, ik had het voorbeeld even overgenomen van een sheet waarin verwijzingen werden getoond zodat ik stap voor stap de opbouw van celverwijzingen via de syntax beter kon visualiseren. Die aanhalingstekens zijn inderdaad nu wel helder, al zal ik vast nog eens in de toekomst weer een minder scherp moment hebben aangaande de syntax van formules en aanhalingstekens.

Ik ben uit de formule gekomen zodat ik nu een geheel flexibel bereik heb van zowel begin als einddatum waardoor ik de gewenste flexibiliteit plus overige mogelijkheden heb die ik wilde bereiken.

Nogmaals hartelijk dank!