As planilhas do Excel^(Excel) geralmente incluem listas suspensas de células para simplificar e/ou padronizar a entrada de dados. Esses menus suspensos são criados usando o recurso de validação de dados para especificar uma lista de entradas permitidas.

Para configurar uma lista suspensa simples, selecione a célula onde os dados serão inseridos, clique em Validação de Dados^{(Data Validation)} (na guia Dados ), selecione ^(Data)Validação de Dados^{(Data Validation)} , escolha Lista^(List) (em Permitir^(Allow) :) e insira os itens da lista (separados por vírgulas ) no campo Source : (veja a Figura 1).

Nesse tipo de menu suspenso básico, a lista de entradas permitidas é especificada na própria validação de dados; portanto, para fazer alterações na lista, o usuário deve abrir e editar a validação dos dados. Isso pode ser difícil, no entanto, para usuários inexperientes ou em casos em que a lista de opções é longa.

Outra opção é colocar a lista em um intervalo nomeado dentro da planilha^{(named range within the spreadsheet)} e, em seguida, especificar esse nome de intervalo (prefaciado com um sinal de igual) no campo Source : da validação de dados (como mostrado na Figura 2^{(Figure 2)} ).

Esse segundo método facilita a edição das opções na lista, mas adicionar ou remover itens pode ser problemático. Como o intervalo nomeado ( FruitChoices , em nosso exemplo) se refere a um intervalo fixo de células ($H$3:$H$10 como mostrado), se mais opções forem adicionadas às células H11 ou abaixo, elas não aparecerão no menu suspenso (já que essas células não fazem parte do intervalo FruitChoices ).

Da mesma forma, se, por exemplo, as entradas Peras^(Pears) e Morangos^{(Strawberries)} forem apagadas, elas não aparecerão mais no menu suspenso, mas o menu suspenso incluirá duas opções "vazias", pois o menu suspenso ainda faz referência a todo o intervalo FruitChoices, incluindo as células vazias H9 e H10 .

Por esses motivos, ao usar um intervalo nomeado normal como fonte de lista para uma lista suspensa, o próprio intervalo nomeado deve ser editado para incluir mais ou menos células se as entradas forem adicionadas ou excluídas da lista.

Uma solução para esse problema é usar um nome de intervalo dinâmico^(dynamic) como a origem das opções suspensas. Um nome de intervalo dinâmico é aquele que se expande (ou se contrai) automaticamente para corresponder exatamente ao tamanho de um bloco de dados à medida que as entradas são adicionadas ou removidas. Para fazer isso, você usa uma fórmula^(formula) , em vez de um intervalo fixo de endereços de células, para definir o intervalo nomeado.

Como configurar um intervalo dinâmico^{(Dynamic Range)} no Excel

Um nome de intervalo normal (estático) refere-se a um intervalo especificado de células ($H$3:$H$10 em nosso exemplo, veja abaixo):

Mas um intervalo dinâmico é definido usando uma fórmula (veja abaixo, retirada de uma planilha separada que usa nomes de intervalo dinâmico):

Antes de começarmos, certifique-se de baixar nosso arquivo de exemplo do Excel  (as macros de classificação foram desabilitadas).

Vamos examinar essa fórmula em detalhes. As opções para Frutas estão em um bloco de células diretamente abaixo de um título ( FRUTAS^(FRUITS) ). Esse título também recebe um nome: FruitsHeading :

A fórmula inteira usada para definir o intervalo dinâmico para as opções de Frutas é:^(Fruits)

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

FruitsHeading refere-se ao título que está uma linha acima da primeira entrada na lista. O número 20 (usado duas vezes na fórmula) é o tamanho máximo (número de linhas) para a lista (isso pode ser ajustado conforme desejado).

Observe que, neste exemplo, há apenas 8 entradas na lista, mas também há células vazias abaixo delas, onde entradas adicionais podem ser adicionadas. O número 20 refere-se ao bloco inteiro onde as entradas podem ser feitas, não ao número real de entradas.

Agora vamos dividir a fórmula em partes (codificando cada parte por cores), para entender como ela funciona:

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

A peça “mais interna” é OFFSET(FruitsHeading,1,0,20,1) . Isso faz referência ao bloco de 20 células (abaixo da célula FruitsHeading ) onde as opções podem ser inseridas. Esta função OFFSET basicamente diz: Comece na célula FruitsHeading , desça 1 linha e mais de 0 colunas, depois selecione uma área com 20 linhas de comprimento e 1 coluna de largura. Isso nos dá o bloco de 20 linhas onde as opções de Frutas^(Fruits) são inseridas.

A próxima parte da fórmula é a função ISBLANK :

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

Aqui, a função OFFSET (explicada acima) foi substituída por “a acima” (para facilitar a leitura). Mas a função ISBLANK está operando no intervalo de 20 linhas de células que define a função OFFSET .

ISBLANK então cria um conjunto de 20 valores TRUE e FALSE , indicando se cada uma das células individuais no intervalo de 20 linhas referenciado pela função OFFSET está em branco (vazio) ou não. Neste exemplo, os primeiros 8 valores no conjunto serão FALSE , pois as primeiras 8 células não estão vazias e os últimos 12 valores serão TRUE .

A próxima parte da fórmula é a função INDEX :

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

Novamente, “o acima” refere-se às funções ISBLANK e OFFSET descritas acima. A função INDEX retorna um array contendo os 20 valores TRUE / FALSE criados pela função ISBLANK .

INDEX é normalmente usado para selecionar um determinado valor (ou intervalo de valores) de um bloco de dados, especificando uma determinada linha e coluna (dentro desse bloco). Mas definir as entradas de linha e coluna para zero (como é feito aqui) faz com que INDEX retorne uma matriz contendo todo o bloco de dados.

A próxima parte da fórmula é a função MATCH :

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

A função MATCH retorna a posição do primeiro valor TRUE , dentro da matriz que é retornada pela função INDEX . Como as primeiras 8 entradas na lista não estão em branco, os primeiros 8 valores na matriz serão FALSE e o nono valor será TRUE (já que a 9ª ^linha do intervalo está vazia).

Assim, a função MATCH retornará o valor de 9 . Neste caso, no entanto, realmente queremos saber quantas entradas estão na lista, então a fórmula subtrai 1 do valor MATCH (que dá a posição da última entrada). Então, finalmente, MATCH ( TRUE , o acima, 0)-1 retorna o valor de 8 .

A próxima parte da fórmula é a função SEERRO^(IFERROR) :

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

A função SEERRO^(IFERROR) retorna um valor alternativo, se o primeiro valor especificado resultar em um erro. Esta função está incluída, pois, se todo o bloco de células (todas as 20 linhas) estiver preenchido com entradas, a função MATCH retornará um erro.

Isso ocorre porque estamos dizendo à função MATCH para procurar o primeiro valor TRUE (na matriz de valores da função ISBLANK ), mas se NENHUMA^(NONE) das células estiver vazia, a matriz inteira será preenchida com valores FALSE . Se MATCH não puder encontrar o valor alvo ( TRUE ) no array que está procurando, ele retornará um erro.

Portanto, se a lista inteira estiver cheia (e, portanto, MATCH retornar um erro), a função SEERRO^(IFERROR) retornará o valor 20 (sabendo que deve haver 20 entradas na lista).

Finalmente, OFFSET(FruitsHeading,1,0,the above,1) retorna o intervalo que estamos realmente procurando: Comece na célula FruitsHeading , desça 1 linha e mais de 0 colunas e selecione uma área que tenha quantas linhas há entradas na lista (e 1 coluna de largura). Portanto, a fórmula inteira em conjunto retornará o intervalo que contém apenas as entradas reais (até a primeira célula vazia).

Usar esta fórmula para definir o intervalo que é a origem da lista suspensa significa que você pode editar livremente a lista (adicionando ou removendo entradas, desde que as entradas restantes comecem na célula superior e sejam contíguas) e a lista suspensa sempre refletirá o atual lista (veja a Figura 6^{(Figure 6)} ).

O arquivo de exemplo (Listas Dinâmicas) usado aqui está incluído e pode ser baixado neste site. As macros não funcionam, no entanto, porque o WordPress não gosta de livros do Excel^(Excel) com macros neles.

Como alternativa para especificar o número de linhas no bloco de lista, o bloco de lista pode receber seu próprio nome de intervalo, que pode ser usado em uma fórmula modificada. No arquivo de exemplo, uma segunda lista ( Names ) usa esse método. Aqui, todo o bloco de lista (abaixo do cabeçalho “NAMES”, 40 linhas no arquivo de exemplo) recebe o nome do intervalo NameBlock . A fórmula alternativa para definir a NamesList é então:

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

onde NamesBlock substitui OFFSET ( FruitsHeading,1,0,20,1 ) e ROWS(NamesBlock) substitui o 20 (número de linhas) na fórmula anterior.

Portanto, para listas suspensas que podem ser facilmente editadas (inclusive por outros usuários que podem ser inexperientes), tente usar nomes de intervalo dinâmico! E observe que, embora este artigo tenha sido focado em listas suspensas, os nomes de intervalo dinâmico podem ser usados ​​em qualquer lugar que você precise fazer referência a um intervalo ou lista que pode variar em tamanho. Aproveitar!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel ѕpreadѕheets often іnclude cell dropdowns to sіmplify and/or standardize data entry.  Τhese dropdowns are created using the data validation feature to specify a list of allоwable 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

• Como excluir linhas em branco no Excel

• Como usar o recurso Speak Cells do Excel

• Como inserir uma planilha do Excel em um documento do Word

• Como usar a análise de variações hipotéticas do Excel

• Como corrigir uma linha no Excel

• Como classificar por data no Excel

• Centralize seus dados de planilha no Excel para impressão

• Como remover linhas duplicadas no Excel

• Diferenças entre o Microsoft Excel Online e o Excel para Desktop

• O que é uma matriz VBA no Excel e como programar uma

• 5 maneiras de converter texto em números no Excel

• Planilhas Google vs Microsoft Excel – Quais são as diferenças?

• Como remover linhas de grade no Excel

• Como usar o preenchimento do Flash no Excel

• Como calcular o Z-Score no Excel

• Use o Spike para recortar e colar vários itens de texto no Word

• Como pesquisar no Excel

• Como usar a função PMT no Excel

• Como mesclar dados em vários arquivos do Excel

• Como rastrear dependentes no Excel