Som van dynamisch naambereik

[rebmog]

Zonder VBA gaat je dat volgens mij niet lukken. Zoals ik al aangaf is wat je ziet als naambereik afhankelijk van de actieve cel, maar in werkelijkheid wordt het bereik van deze naam dynamisch bepaald op het moment dat deze gebruikt wordt. Dat kun je in mijn voorbeeldje zien in cellen E21 t/m G21. Deze bevatten allemaal de formule =SOM(nieuw), maar deze verwijzen alle drie naar andere bereiken die tegelijkertijd worden berekend.

In werkelijkheid bestaat er echter maar één definitie en die kun je ook zien met =SOM(INDIRECT("nieuw")). Met formule evalueren zie je dat deze dan verwijst naar $A$1048573:$A$1048576. Dat is hetzelfde bereik dat je ook met VBA ziet.

 

[HSV]

Zet de cursor eens in A10 en A11 of A12 en zie de formule in E9.

 

[snb]

@Cobbe

Ik zou het zo beschrijven:

- je hebt geen dynamisch naambereik gedefinieerd maar een relatief naambereik.

Wat betekent dat:

- het naambereik nieuw is als basis gedefineerd als A6:A9.
- dat betekent dat, als ik het ergens toepas, dit relatieve bereik wordt aangepast aan de plek waar ik het gebruik.
- jij gebruikt dit naambereik in E9; dan wordt het relatieve naambereik aangepast; het verwijst nu naar E5:E8
- dit kun je testen door in E5:E8 getallen te zetten, waarvan de som direkt in E9 duidelijk wordt.
- omdat je het naambereik volledig relatief hebt gemaakt past het zich aan op zowel rijen als kolommen.
- om de mogelijkheden hiervan te testen kun je variëren met naambereik A6:A9; $A6:$A9, A$6:A$9 en $A$6:A$9.

- duidelijk is dat de omvang van het gebied nooit wordt gewijzigd; daarom vind ik de term dynamisch ook minder gelukkig.

Als ik in A10: =sum(nieuw) invul krijg ik het gewenste resultaat.
Deze manier van werken is een handige methode om met een relatief benoemd bereik in een regelmatig opgebouwd werkblad simpel formules in te voeren die zich ook nog eens 'dynamisch' aanpassen aan verwijdering/toevoeging van rijen/kolommen.
In dit voorbeeld: overal waar je in het werkblad de som van de bovenstaande 4 cellen wil hebben gebruik je de formule '=sum(nieuw)'

Zoals ik eerder zei, is dit verschijnsel volledig in overeenstemming met het argument/eigenschap 'Relativeto' van de eigenschap Address.
Test maar met

Sub tst()
   MsgBox Cells(10, 10).Address(0, 0, -4150, -1, Cells(1))

   MsgBox Cells(10, 10).Address(0, 0, -4150, -1, Cells(5, 5))
End Sub

Amaai, in fraaier Vloms kan ik nie klappe.

 

[Cobbe]

Dit werkt wel zonder VBA maar met vooraf afgebakende bereiken.
Dit wijzigt niet bij selecteren van een willekeurige cel.

Maar goed, ga het oplossen met behulp van (verborgen)hulpcellen.

Bedankt aan allen voor het meedenken en de voorstellen.

 

[snb]

Dit wijzigt niet bij selecteren van een willekeurige cel.

Het is mij niet duidelijk wat je hiermee bedoelt.

Wat wil je dan bereiken bij het selecteren van een willekeurige cel ?

 

[Cobbe]

Bij het selecteren van een willekeurige cel in een bereik wil ik de som(of gemiddelde of wat dan ook) van een benoemd bereik, als uitkomst zien in een welbepaalde cel.
En dat kan ik heel gemakkelijk bereiken mbv VBA maar dat is nu net wat ik niet wil(kan gebruiken.

 

[snb]

'Slechts' in VBA heb ik deze voor je in de aanbieding:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells(12, 1) = Application.Names("somvan").RefersToRange.Address
    Cells(14, 1) = Application.Sum(Application.Names("somvan").RefersToRange)
End Sub

 

[Cobbe]

Dus als jij het ook al zegt zal het wel niet mogelijk zijn zonder VBA.

Toch bedankt voor het meedenken.

 

[HSV]

#22 gemist?

 

[Cobbe]

Maar neen niets gemist, echter de oplossing gebruikt VBA en werkt zoals voorgesteld enkel over kolom A.

Toch nogmaals bedankt en sorry dat ik niet rechtstreeks heb gereageerd op #22

Gr.

 

[HSV]

Formule van gedefinieerde naam is simpel aan te passen toch?
De berekening vraagt Vba, maar kan zonder door op F9 te drukken.

 

[Cobbe]

https://we.tl/whdZ9Tt8CP

Dit zoek ik met telkens de som van de geselecteerde cellen zonder een of andere tussenstap.

Maar ik heb me er al in gevonden dat dat niet gaat lukken, dus stopt dit ook hier.

 

[snb]

Even terug naar je vraag:

Als ik je goed begrijp vraag je in feite:

- naar een Excel-formule
- die het adres (of de range) van de actieve cel weergeeft.

 

[HSV]

De aangereikte formule doet het gevraagde.
Je moet het wel even aanpassen.

somvan is:

=VERSCHUIVING(INDIRECT(ADRES(CEL("rij");CEL("kolom")));-4;0;4;1)

Of:

=VERSCHUIVING(INDIRECT(CEL("adres"));-4;0;4;1)

Maar goed, als jij er klaar mee bent, ik ook.
Succes.

 

[Cobbe]

Ik ga nog eenmaal reageren:

Voorbeeld:
Ik wil in cel A15 de som hebben van H4:H7 als ik in cel H8 sta,
Ik wil in cel A15 de som hebben van G18:G20 als ik in cel G21 sta,
en zo verder.

Groetjes en nog een fijne avond allemaal.

 

[HSV]

Als je in cel A15 staat geef je in als formule in je gedefinieerde naam: =blad1!A14:A11
De formule =som(somvan) doet het overal, maar met een druk op F9.

 

[WHER]

Toch nog een poging, zonder VBA en zonder genaamd bereik, selecteer een willekeurige cel in het grijze bereik, druk F9 en kijk naar A15

edit: werkt misschien enkel in de engelstalige versie?

 

[HSV]

Werkt ook in Nederlandse versie WHER.
Is hetzelfde principe als die ik in naambereik had.
Kan ook rechtstreeks in cel.

=SOM(VERSCHUIVING(INDIRECT(CEL("adres"));-4;0;4;1))

 

[WHER]

whoopsie, overheen gelezen