Excel -spreadsheets bevatten vaak vervolgkeuzelijsten voor cellen om de gegevensinvoer te vereenvoudigen en/of te standaardiseren. Deze vervolgkeuzelijsten worden gemaakt met behulp van de gegevensvalidatiefunctie om een ​​lijst met toegestane items op te geven.

Om een ​​eenvoudige vervolgkeuzelijst in te stellen, selecteert u de cel waar de gegevens worden ingevoerd, klikt u op Gegevensvalidatie^{(Data Validation)} (op het tabblad Gegevens ), selecteert u ^(Data)Gegevensvalidatie^{(Data Validation)} , kiest u Lijst^(List) (onder Toestaan^(Allow) ​​:) en voert u vervolgens de lijstitems in (gescheiden door komma's) ) in het veld Bron^(Source) : (zie afbeelding 1).

In dit type basisvervolgkeuzelijst wordt de lijst met toegestane items gespecificeerd in de gegevensvalidatie zelf; daarom moet de gebruiker de gegevensvalidatie openen en bewerken om wijzigingen in de lijst aan te brengen. Dit kan echter moeilijk zijn voor onervaren gebruikers of in gevallen waar de keuzelijst lang is.

Een andere optie is om de lijst in een benoemd bereik in het werkblad^{(named range within the spreadsheet)} te plaatsen en vervolgens die bereiknaam op te geven (voorafgegaan door een gelijkteken) in het veld Bron^(Source) : van de gegevensvalidatie (zoals weergegeven in Afbeelding 2^{(Figure 2)} ).

Deze tweede methode maakt het gemakkelijker om de keuzes in de lijst te bewerken, maar het toevoegen of verwijderen van items kan problematisch zijn. Aangezien het benoemde bereik ( FruitChoices , in ons voorbeeld) verwijst naar een vast celbereik ($ H $ 3: $ H $ 10 zoals weergegeven), zullen als er meer keuzes worden toegevoegd aan de cellen H11 of lager, deze niet worden weergegeven in de vervolgkeuzelijst (aangezien die cellen geen deel uitmaken van het FruitChoices- assortiment).

Evenzo, als bijvoorbeeld de vermeldingen Peren^(Pears) en Aardbeien^{(Strawberries)} worden gewist, verschijnen ze niet langer in de vervolgkeuzelijst, maar in plaats daarvan bevat de vervolgkeuzelijst twee "lege" keuzes omdat de vervolgkeuzelijst nog steeds verwijst naar het volledige FruitChoices-bereik, inclusief de lege cellen H9 en H10 .

Om deze redenen moet, wanneer een normaal benoemd bereik als lijstbron voor een vervolgkeuzelijst wordt gebruikt, het benoemde bereik zelf worden bewerkt om meer of minder cellen op te nemen als items worden toegevoegd aan of verwijderd uit de lijst.

Een oplossing voor dit probleem is het gebruik van een dynamische^(dynamic) bereiknaam als bron voor de vervolgkeuzemenu's. De naam van een dynamisch bereik is een naam die automatisch wordt uitgebreid (of samengetrokken) om exact overeen te komen met de grootte van een gegevensblok wanneer items worden toegevoegd of verwijderd. Om dit te doen, gebruikt u een formule^(formula) , in plaats van een vast bereik van celadressen, om het benoemde bereik te definiëren.

Een dynamisch bereik^{(Dynamic Range)} instellen in Excel

Een normale (statische) bereiknaam verwijst naar een gespecificeerd celbereik ($H$3:$H$10 in ons voorbeeld, zie hieronder):

Maar een dynamisch bereik wordt gedefinieerd met behulp van een formule (zie hieronder, overgenomen uit een aparte spreadsheet die namen van dynamisch bereik gebruikt):

Voordat we beginnen, zorg ervoor dat u ons Excel-voorbeeldbestand downloadt  (sorteermacro's zijn uitgeschakeld).

Laten we deze formule in detail bekijken. De keuzes voor Fruit staan ​​in een blok cellen direct onder een kop ( VRUCHTEN^(FRUITS) ). Die kop krijgt ook een naam: FruitsHeading :

De volledige formule die wordt gebruikt om het dynamische bereik voor de Fruits- keuzes te definiëren, is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading verwijst naar de kop die één rij boven het eerste item in de lijst staat. Het getal 20 (twee keer gebruikt in de formule) is de maximale grootte (aantal rijen) voor de lijst (deze kan naar wens worden aangepast).

Merk op dat er in dit voorbeeld slechts 8 items in de lijst staan, maar er zijn ook lege cellen eronder waar extra items kunnen worden toegevoegd. Het getal 20 verwijst naar het hele blok waar boekingen kunnen worden gedaan, niet naar het daadwerkelijke aantal boekingen.

Laten we nu de formule in stukjes opsplitsen (elk stuk kleurcodering), om te begrijpen hoe het werkt:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

Het "binnenste" stuk is OFFSET(FruitsHeading,1,0,20,1) . Dit verwijst naar het blok van 20 cellen (onder de cel FruitsHeading ) waar keuzes kunnen worden ingevoerd. Deze OFFSET - functie zegt in feite: Begin bij de FruitsHeading -cel, ga 1 rij omlaag en meer dan 0 kolommen, selecteer vervolgens een gebied dat 20 rijen lang en 1 kolom breed is. Dus dat geeft ons het blok met 20 rijen waar de Fruits - keuzes worden ingevoerd.

Het volgende stuk van de formule is de ISBLANK- functie:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Hier is de OFFSET- functie (hierboven uitgelegd) vervangen door "the above" (om de zaken leesbaarder te maken). Maar de ISBLANK -functie werkt op het 20-rijenbereik van cellen dat de OFFSET - functie definieert.

ISBLANK maakt vervolgens een set van 20 TRUE- en FALSE -waarden, waarmee wordt aangegeven of elk van de afzonderlijke cellen in het bereik van 20 rijen waarnaar wordt verwezen door de OFFSET - functie, leeg (leeg) is of niet. In dit voorbeeld zijn de eerste 8 waarden in de set ONWAAR^(FALSE) omdat de eerste 8 cellen niet leeg zijn en de laatste 12 waarden TRUE zijn .

Het volgende stuk van de formule is de INDEX- functie:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Nogmaals, "het bovenstaande" verwijst naar de hierboven beschreven ISBLANK- en OFFSET - functies. De INDEX -functie retourneert een array met de 20 TRUE / FALSE - waarden die zijn gemaakt door de ISBLANK- functie.

INDEX wordt normaal gesproken gebruikt om een ​​bepaalde waarde (of reeks waarden) uit een gegevensblok te halen, door een bepaalde rij en kolom (binnen dat blok) op te geven. Maar door de rij- en kolominvoer op nul te zetten (zoals hier wordt gedaan), geeft INDEX een array terug die het hele gegevensblok bevat.

Het volgende stuk van de formule is de MATCH- functie:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

De MATCH - functie retourneert de positie van de eerste TRUE -waarde, binnen de array die wordt geretourneerd door de INDEX - functie. Aangezien de eerste 8 items in de lijst niet leeg zijn, zullen de eerste 8 waarden in de array FALSE zijn en de negende waarde TRUE (aangezien de 9e ^rij in het bereik leeg is).

Dus de MATCH- functie retourneert de waarde van 9 . In dit geval willen we echter echt weten hoeveel items er in de lijst staan, dus de formule trekt 1 af van de MATCH -waarde (die de positie van het laatste item geeft). Dus uiteindelijk geeft MATCH ( TRUE ,the above,0)-1 de waarde 8 terug .

Het volgende stuk van de formule is de IFERROR- functie:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

De functie IFERROR retourneert een alternatieve waarde als de eerste opgegeven waarde in een fout resulteert. Deze functie is inbegrepen omdat, als het hele blok cellen (alle 20 rijen) is gevuld met items, de MATCH - functie een fout retourneert.

Dit komt omdat we de MATCH- functie vertellen om te zoeken naar de eerste TRUE -waarde (in de array met waarden van de ISBLANK- functie), maar als GEEN^(NONE) van de cellen leeg is, wordt de hele array gevuld met FALSE - waarden. Als MATCH de doelwaarde ( TRUE ) niet kan vinden in de matrix die het zoekt, wordt er een fout geretourneerd.

Dus als de hele lijst vol is (en daarom MATCH een fout retourneert), retourneert de IFERROR- functie in plaats daarvan de waarde 20 (wetend dat er 20 items in de lijst moeten zijn).

Ten slotte geeft OFFSET(FruitsHeading,1,0,the above,1) het bereik terug waarnaar we eigenlijk op zoek zijn: begin bij de cel FruitsHeading , ga 1 rij omlaag en meer dan 0 kolommen, selecteer vervolgens een gebied dat zoveel rijen lang is als er zijn vermeldingen in de lijst (en 1 kolom breed). Dus de hele formule samen retourneert het bereik dat alleen de daadwerkelijke vermeldingen bevat (tot aan de eerste lege cel).

Als u deze formule gebruikt om het bereik te definiëren dat de bron is voor de vervolgkeuzelijst, kunt u de lijst vrij bewerken (vermeldingen toevoegen of verwijderen, zolang de resterende vermeldingen in de bovenste cel beginnen en aaneengesloten zijn) en de vervolgkeuzelijst altijd de huidige weergeeft lijst (zie figuur 6^{(Figure 6)} ).

Het voorbeeldbestand (Dynamic Lists) dat hier is gebruikt, is bijgevoegd en kan worden gedownload van deze website. De macro's werken echter niet, omdat WordPress niet houdt van Excel- boeken met macro's erin.

Als alternatief voor het specificeren van het aantal rijen in het lijstblok, kan het lijstblok een eigen bereiknaam krijgen, die vervolgens in een aangepaste formule kan worden gebruikt. In het voorbeeldbestand gebruikt een tweede lijst ( Namen^(Names) ) deze methode. Hier krijgt het hele lijstblok (onder de kop "NAMES", 40 rijen in het voorbeeldbestand) de bereiknaam NameBlock toegewezen^(NameBlock) . De alternatieve formule voor het definiëren van de NamesList is dan:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

waarbij NamesBlock OFFSET ( FruitsHeading,1,0,20,1 ) vervangt en ROWS(NamesBlock) de 20 (aantal rijen) in de eerdere formule vervangt.

Dus, voor vervolgkeuzelijsten die gemakkelijk kunnen worden bewerkt (ook door andere gebruikers die mogelijk onervaren zijn), probeer dynamische bereiknamen te gebruiken! En merk op dat, hoewel dit artikel is gericht op vervolgkeuzelijsten, dynamische bereiknamen overal kunnen worden gebruikt waar u naar een bereik of lijst wilt verwijzen die in grootte kan variëren. Genieten van!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often include cell dropdоwns to simplify and/or standаrdize data entry.  Τhese dropdowns are created using the dаta validation feаture to specify a list of allowable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Hoe lege regels in Excel te verwijderen

• De functie voor het uitspreken van cellen in Excel gebruiken

• Een Excel-werkblad invoegen in een Word-document

• De What-If-analyse van Excel gebruiken

• Hoe een rij in Excel te repareren

• Afhankelijkheden traceren in Excel

• Excel verbinden met MySQL

• Hoe maak je een cirkeldiagram in Excel

• Een VBA-macro of -script maken in Excel

• Excel's What-If Analysis Goal Seek Tool gebruiken

• Hoe gebruik je Flash Fill in Excel?

• Waarom u benoemde bereiken in Excel zou moeten gebruiken?

• 4 manieren om een ​​vinkje te gebruiken in Excel

• Hoe te zoeken in Excel

• Hoe Word en Excel te openen in de veilige modus

• Gegevens in meerdere Excel-bestanden samenvoegen

• Celverwijzingen behouden bij het kopiëren van een formule in Excel

• Hoe de PMT-functie in Excel te gebruiken?

• Sorteren op datum in Excel

• Een Excel-bestand delen voor eenvoudige samenwerking