Functie INDIRECT en bladnaam in een cel opgeven

[daan108]

Beste mensen,

Met Excel 2003 wil ik een bladnaam in een cel opgeven en gegevens van het opgegeven blad ophalen. Dit lukt aardig met de functie INDIRECT maar nog niet helemaal zoals ik het zou willen. Voorbeeld:

De data staan op blad Data1, Data2 etc. die dezelfde structuur hebben maar de kolomlengtes kunnen verschillend zijn. Daarom heb ik op elk blad een lokale naam gegeven aan de celbereiken, bijvoorbeeld voor blad Data1:

Data1!Datarange
=VERSCHUIVING(Data1!$A$1,0,0,Data1!Datacount,1)

Resultaten moeten op blad Res komen. Hier vul ik in een cel, bijvoorbeeld in $A$1 een bladnaam in, en een formule, bijv. in cel $B$1, die de data moet sommeren:

$A$1: Data1!
$B$1: =SOM(INDIRECT(A1&"datarange"))

Bovenstaande met het dynamisch bereik met de functie VERSCHUIVING geeft de foutmelding #VERW! maar als ik voor het databereik in plaats van de VERSCHUIVING-functie een celbereik opgeef, dan gaat het wel goed. Echter, dan past het bereik zich niet automatisch aan. Dit gaat dus wel goed met sommeren als bovenstaand, benoemd bereik:

Data1!Datarange
=Data1!$A$1:$A20

Hoe kan ik toch indirect verwijzen naar een benoemd bereik dat een formule bevat die ingewikkelder is dan een celadres?
Alle suggesties zijn welkom! Alvast bedankt,
Daan.

 

[bandito bob]

Je dynamische bereik is al gekoppeld aan een werkblad. Je hoeft het dus niet dubbel te doen. Je kunt gewoon doen:

=SOM(naam van dynamisch bereik)

 

[daan108]

Beste Paul,

Bedankt voor de reactie, maar de naam Datarange van het bereik is een lokale naam die op verschillende tabbladen voorkomt, dus Blad1!Datarange, Blad2!Datarange etc. Ik weet van tevoren niet van welk tabblad de gegevens opgehaald moeten worden; het is de bedoeling dat dit in een cel aangegeven wordt. De invoer in de cel is bijvoorbeeld de tabbladnaam. Op de een of andere manier moet de tabbladnaam die gekozen/ingevoerd wordt, gekoppeld worden aan de formule die de som van het databereik uitrekent.

Ik heb al geprobeerd om de namen op de verschillende tabbladen globaal te definiëren, dus op Blad1 heet het dynamische bereik DataRange1 en op Blad2 heet het bereik DataRange2. Stel bijvoorbeeld dat DataRange1 de VERSCHUIVING-formule is die verwijst naar het bereik A1:A5, en de som 150 is. Op weer een ander tabblad kunnen de volgende formules ingevoerd worden, met resultaat erachter:

=SOM(Blad1!A1:A5)  =150
=SOM(INDIRECT("blad1!A1:A5"))  =150
=SOM(INDIRECT("blad1!"&"A1:A5"))  =150
=SOM(INDIRECT("blad1!"&D1))  =150 met in cel D1 de tekst A1:A5 (zonder aanhalingstekens)
=SOM(INDIRECT(D1&"a1:a5"))   =150 met in cel D1 de tekst blad1!
=SOM(DataRange1)  =150
maar dit gaat fout:
=SOM("Datarange"&"1")  =#VERW!
=SOM(INDIRECT("datarange1"))  =#VERW! dus ook
=SOM(INDIRECT("datarange"&"1")  =#VERW!
=SOM(INDIRECT(D1))  =#VERW!  met in D1 de tekst Datarange1

In bovenstaande is Datarange1:

=VERSCHUIVING(Blad1!$A$1,0,0,5,1)

Het gaat wel in alle gevallen goed als Datarange1 gedefinieerd is als

=Blad1!$A$1:$A$5

 

[HSV]

Hallo Daan,

Waarom doe je er geen .xls bestandje bij.

 

[bandito bob]

Oke, zo te lezen kan jij dan niet werken met naam gedefinieerde dynamische bereiken.
Ik denk dat je dan een dynamisch bereik moeten aangeven in een indirect formule.
Je krijgt dan zoiets:

=SOM(INDIRECT(A1&"B2:B"&AANTAL(INDIRECT(A1&"B2:B10000")+1)))

Met in cel A1 een werkblad naam.

 

[daan108]

Harry, zie bijgevoegd bestand.

Paul, ik denk dat dat inderdaad moet lukken (heb 't nog niet geprobeerd) maar de formule wordt wel erg lang en minder goed leesbaar.

 

[popipipo]

Hallo Daan

Je geeft aan bv Cel A2 in tabblad Datasheet1 de naam Rowscount
Diezelfde naam geef je aan Cel A2 in tabblad Datasheet2.
Dit kan niet, 2 cellen de zelfde naam geven

 

[bandito bob]

Hier een voorbeeldje van mijn manier.

 

[daan108]

Hallo Willem,
De namen zijn lokaal gedefinieerd, d.w.z. met de bladnaam ervoor, dan kan het wel. Als je de cel(len) vanaf een ander tabblad wilt aanroepen moet je de bladnaam met ! erbij zetten.
Groet, Daan.

 

[daan108]

Paul,
Bedankt voor je reactie. Formule is zoals je al eerder had voorgesteld. Dat werkt dus, maar ik vind 'm te lang, vooral omdat ik de formule SOM als voorbeeld voor dit forum heb gebruikt; de werkelijke formule zal uit twee, drie of zelfs vier van dit soort bereiken bestaan. Dan is het niet goed meer leesbaar en moeilijk te onderhouden.
PS Op dit moment staan de verschillende data die ik eigenlijk op meerdere tabbladen wil verdelen naast elkaar op één tabblad. Met VERSCHUIVING t.o.v. het eerste blok data laat ik de gegevens uit de andere blokken ophalen. Werkt op zich goed, maar ik vind meerdere tabbladen mooier, bovendien kan een tabblad (+grafiek) makkelijker gekopieerd worden dan een blok gegevens (i.v.m. grafiek en verwijzingen met gemixte $ in celadres).
Groet, Daan.