Как сделать dropdown в excel?

При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение.

Элементарный способ

Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .

Вот только данный метод не подойдет, если необходимо сделать такой объект в Эксель в другой ячейке и в 2-3 и т.д до и после. При такой необходимости используйте следующий вариант.

Традиционный способ

Выделяете область самих ячеек, из которых создаете выпадающий перечень, далее переходите:

Вставка/Имя/Присвоить (Excel 2003)

В свежих версиях (2007, 2010, 2013, 2016) переходите:

Формулы/Определенные имена/Диспетчер имен/Создать

• 
• Вписываете любое наименование, тапаете ОК .
• 
• Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите:

В разделе «Источник» вносите ранее написанное имя, просто отмечаете диапазон. Полученную ячеечку можете копировать в любое место, в ней уже будет меню заданных табличных элементов.

Кроме того, вы можете ее растягивать, чтобы создать диапазон. Кстати, в случае изменения информации в нем, списочная информация тоже будет изменяться, она является динамической.

Как в Экселе сделать выпадающий список: пользуемся управленческими методами

При использовании этого варианта, вы вставляете элемент управления, представляющий собой непосредственно диапазон данных. Для этого:

• во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;

• во-вторых, переходите на раздел, кликаете «Вставить»;
• в-третьих, выбираете «Поле…» и жмете по значку;
• рисуете прямоугольник;

• делаете правый клик по нему и жмете Формат объекта;

• ищите «Формировать…», выделяете нужные границы;
• отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
• жмете ОК.

Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX

Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».

Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах — режим отладки, который позволяет менять параметры, и — режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик. С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.

Дополнительные возможности

В программе Excel возможно создание связанного выпадающего перечня. То есть при выборе какого-то значения, в другой графе можно подобрать нужные ему параметры. К примеру, вы выбираете какой-то продукт, и понадобилось отметить меру измерения, к примеру, грамм, килограмм.

В первую очередь, следует сделать табличку, где будут сами списочки, а потом отдельно окошки с названиями продуктов.

Далее следует присвоить именованный диапазон, как это делали ранее для стандартных меню. В первой ячеечке используем способ с проверкой данных.

Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.

После этого, чтобы окошки снизу дополнились такими же свойствами, выделяем верхний участок и с нажатой мышкой перетаскиваем все вниз. Готово.

Как в Эксель сделать выпадающий список? Как видите, это легко выполнить любым указанным методом. Просто выбирайте для себя самый оптимальный. А выбор будет зависеть от целей создания, предназначения, области использования, количества информации и прочего.

Источник: https://life-v.ru/how-to-make-excel-drop-down-list/

How to Make a Dropdown List / Menu in Excel

Dropdown menus, also popularly referred to as dropdowns, are ways to organize information that is often accessed. But what few people know is that this feature goes beyond websites and pages in html and this way you can also create them in spreadsheets in Excel to optimize and bring better organization and manipulation of the data.

Thinking about this, we separate a step by step so you can easily create a dropdown menu in Excel. Follow us!

There are two ways in Excel to create dropdown menus and one of them is through the data validation feature, in which it aims to avoid errors in filling out forms or facilitating the insertion of data into a worksheet. See the example below:

Above we have a list of names of people in which it must be filled with their respective birthday months. If we had an extensive list, probably filling this field would be very laborious. In this way, we can create a drop-down menu for each cell where you can select the desired month without typing it.

For this, we can type somewhere in the worksheet (or even in another worksheet) every month of the year in which they will be part of the menu. In this case, we will use any place just to exemplify.

In the next step, simply select the first cell that will receive the menu and then access: Data tab> Data Validation> Data Validation as shown below:

A new window will open. Choose the Allow: List option in the Settings tab.

Note that when you select the List option, Excel displays the «Source» field. In this field you must enter the information that will make up the menu. You can then write each item by separating them by semicolons (example: January, February, March …) or insert a range of cells that corresponds to the values ​​to be inserted in the menu.

For the example we will use the range of cells where we list the months as shown below:

At the end of the operation, just click OK and check the result.

Note that Excel has created a dropdown menu according to the range of cells defined.

To apply the same feature to other cells, simply use the auto-fill handle.

In this way, filling in the fields becomes easier as well as avoid typing errors.

Dropdown Menu with Combination Box

Another way to create dropdown menus in Excel is through the Combination Box option, and is ideal for implementing it in forms or at other times.

Below is a simple example to understand the implementation of this feature, where in this case, we intend to create a menu again with the months of the year. To do this, simply list the months and select the option: Developer's Guide> Insert> Combination Box.

• In the next step, simply draw the box in the desired dimensions and location, and you can adjust it later.

To configure the combo box and enter the desired information in the menu, just right-click on the box and select the «Format Control …» option.

1. A new window will open as below:
2. Fill in the fields indicated as follows:
3. Input range: It should contain the range of cells where the information that will be in the menu is present.
4. Cell Link: It represents the position that will make the bond;
5. Suspended lines: Represents the number of rows that will be displayed in the menu;
6. At the end of the operation, just click OK and check the result.
7. Note: If Excel is not enabled to display the developer tab, simply access the option: File> Options> Customize Ribbon and finally activate the Developer tab check box.

According to the tips mentioned here, it was possible to know in detail how to create dropdown menus in Excel. This makes it a more useful resource when you want to optimize worksheets and increase the productivity of jobs with the ease of can insert data into cells beyond the best to organize the information and avoid fill errors.

Источник: https://blog.luz.vc/en/excel/how-to-do-a-list-dropdown-in-excel/

Как добавить Выпадающий список в Excel Cell

Узнайте, как создать выпадающий список в MS Excel с помощью значений, разделенных запятыми, названный диапазон и диапазон ячеек. Выпадающие списки помогают более быстрому вводу данных.

/add-drop-down-list-excel-cell.html

Мы все знаем, что Excel является коммерческим приложением электронной таблицы производятся и распространяются Microsoft для Windows, и Mac OS X.

Вы можете использовать выпадающий список, чтобы ввести данные в таблицу из предварительно заданного списка элементов. Выпадающий список помогает ограничить количество вариантов, доступных для Вас и ускоряет ввод данных в Excel.

Создание Excel выпадающего списка

Excel раскрывающийся список может быть основан на:

• список значений, разделенных запятыми,
• именованный диапазон, и
• диапазон ячеек

Методы, описанные в данном разделе, применимы к большинству версий Excel, в том числе 2013, 2010, 2007 и 2003 годах.

Создание раскрывающегося списка Excel, основанный на значений, разделенных запятыми

Выберите ячейку или ячейки, где вы хотите выпадающий появляться. Это может быть одна ячейка, диапазон ячеек или весь столбец, как показано на рисунке ниже. Если выбран весь столбец, выпадающее меню будет создано в каждой ячейке этого столбца. Это оказывается экономия времени для создания типа анкетные листа.

В строке меню Excel выберите Инструменты группу данных> Data , а затем нажмите кнопку Проверка данных.

1. В диалоговом окне Проверка данных выберите вкладку Параметры

1. Из выпадающего списка Разрешить, выберите Список.
2. В поле Source введите элементы , которые вы хотите показать в вашем раскрывающемся меню разделены запятой (с или без пробелов).
3. Выберите в раскрывающемся списке в ячейке
4. Выберите Игнорировать пробелы , если вы хотите , чтобы ваши пользователи , чтобы оставить клетку пустой.
5. Нажмите кнопку OK , и ваш раскрывающийся список создается. Пользователи могут нажать на стрелку вниз, чтобы отобразить список и выберите элемент.

Описанный выше метод является возможным, если у вас есть только несколько клеток, находящихся на листе. Если вы хотите, тот же список, чтобы появиться в несколько ячеек, то этот метод следует избегать.

Редактирование будет громоздким , как вам придется изменить каждую ячейку , которая ссылается на список проверки данных.

Обнаружение каждую клетку и последующего редактирования может быть болезненным процессом.

Вот это лучший способ сделать это.

Создание раскрывающегося списка Excel, основанный на названном диапазоне

Этот метод, хотя и немного времени, но это экономит время в долгосрочной перспективе. Например, давайте создадим раскрывающийся список названий книг любимых авторов.

1. Рабочий лист 1 из списка имен авторов в колонке, как в соответствии с:

1. В рабочем листе 2 создания названий книг каждого автора в индивидуальной колонке. Эти значения должны быть введены в одну колонку без каких — либо пустых ячеек.
2. Выберите все названия в колонке конкретного автора, щелкните правой кнопкой мыши и из контекстного меню / всплывающее окно Выбрать Название диапазона. Или вы можете нажать кнопку Диспетчер имен на вкладке Формулы или нажмите Ctrl + F3. В диалоговом окне Имя менеджера, нажмите кнопку Создать.

В качестве альтернативы, вы можете выбрать ячейки и введите имя диапазона непосредственно в поле Имя, как показано ниже. Когда закончите, нажмите Enter, чтобы сохранить только что созданный с именем диапазона.

1. В диалоговом окне New Name введите имя в Имя Убедитесь , что нет места в имя , присвоенное. Не изменяйте параметр в поле Область. Укажите легко понять комментарий в поле , если это требуется или оставьте это поле пустым. Относится к поле заполняется автоматически и отображает диапазон ячеек , которые вы выбрали. Нажмите кнопку OK , чтобы сохранить имя диапазона , который вы создали.
2. Переключиться на Worksheet1. Выделите ячейку с именем автора.
3. На ленте Excel щелкните Data> Проверка данных.

1. В диалоговом окне Проверка достоверности данных, выберите настройки , как описано выше , и в поле Source введите имя диапазона , который вы определили и нажмите кнопку ОК. Убедитесь в том, что имя в источнике с префиксом = (равно) знак.
2. В ячейке против выбранного имени автора нажмите стрелку вниз, чтобы отобразить список названий книг автора.

Вы можете создать книгу выпадающий список для каждого автора. Список выпадающий с более чем восьми пунктов будет иметь полосу прокрутки.

Примечание: Вы можете создать список элементов на том же или другом листе. Для простоты понимания пример выше использует два различных рабочих листов.

Создание выпадающего списка Excel, основываясь на диапазоне ячеек

Этот метод очень похож на создание выпадающий список, основанный на названном диапазоне за незначительной разницы, за исключением.

При настройке выпадающего списка, а не вводить имя диапазона, нажмите на иконку выбора диапазона рядом с полем Source и выбрать все ячейки с элементами, которые вы хотите включить в раскрывающемся списке. Они могут быть в том же или в другом рабочем листе. Если последнее, вы просто перейти на другой рабочий лист и выберите диапазон с помощью мыши.

Проектирование выпадающий список в Excel довольно легко и экономит время. Это чистый способ отображения большого списка выбора, так как только один выбор отображается сначала до тех пор, пока пользователь не активирует раскрывающийся.

Мы надеемся, что эта статья о создании раскрывающегося списка в Excel была полезна для вас. Если у Вас возникли вопросы по этой теме, пожалуйста, не стесняйтесь задавать в разделе комментариев. Мы в TechWelkin и наш читатель сообщество будет пытаться помочь вам. Благодарим Вас за использование TechWelkin!

Источник: http://ru.affdu.com/add-drop-down-list-excel-cell.html

Drop Down Lists Excel 2003

Use Excel data validation to create drop down lists on a worksheet, so data entry is easier. Watch the video, and download the sample file

For the Excel 2010 video and instructions, click here.

What is Data Validation?

Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can:

In this tutorial, you'll see how to create a drop down list of choices in a cell.

Create a Drop Down List

Watch this video to see the steps for creating a drop down list in Excel 2003. Written instructions are below the video.

How to Create a Drop Down List

Use Data Validation to create a drop down list of options in a cell. List items can be typed in a row or column on a worksheet, or typed directly into the Data Validation dialog box.

Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Edit|Clear|ClearAll

1. Create a List of Items

If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists.

1. In single row or column, type the entries you want to see in the drop-down list. (Note: The list must be in a single block of cells — e.g. you can use A2:A6, but not A2, A4, A6, A8.)

2. Name the List Range

If you type the items on a worksheet, and name the range, you can refer to the list from any worksheet in the same workbook.

1. Select the cells in the list.
2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for the list, e.g. FruitList.
4. Press the Enter key.

Note: To create a named list that automatically expands to include new items, use a dynamic range.

3. Apply the Data Validation

1. Select the cells in which you want to apply data validation
2. From the Data menu, choose Validation.
3. From the Allow drop-down list, choose List

1. In the Source box, type an equal sign and the list name, for example: =FruitList
2. Click OK.

Tip: To select a range name, instead of typing it:

1. In the Data Validation dialog box, under Allow, select List
2. Click in the Source box, and on the keyboard, press the F3 key
3. From the Paste Name list, select a named range, and click OK.
4. Click OK, to close the Data Validation dialog box.

• 4. Using a Delimited List
• Instead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas. For example:
• Yes,No,Maybe
• Note: This method of Data Validation is case sensitive — if a user types YES, an error alert will be displayed.

5. Allow Entries that are not in the List

To allow users to type items that are not in the list., turn off the Error Alert.

6. Protect the List

To protect the list from accidental damage, if you have entered it on a different worksheet, you can hide that sheet.

1. Select the sheet that contains the list
2. Choose Format | Sheet | Hide

Download the Sample File

Download the zipped Excel 2003 sample workbook

More Tutorials

1. Create a Drop Down List
2. Create Dependent Drop Down Lists
3. Dependent Dropdowns from a Sorted List  
4. Data Validation Criteria Examples
5. Data Validation Tips

Источник: https://www.contextures.com/exceldropdownlists2003.html

How to Create a Drop Down List in Excel (the Only Guide You Need)

• A drop-down list is an excellent way to give the user an option to select from a pre-defined list.
• It can be used while getting a user to fill a form, or while creating interactive Excel dashboards.
• Drop-down lists are quite common on websites/apps and are very intuitive for the user.
• Watch Video – Creating a Drop Down List in Excel
• In this tutorial, you’ll learn how to create a drop down list in Excel (it takes only a few seconds to do this) along with all the awesome stuff you can do with it.

How to Create a Drop Down List in Excel

In this section, you will learn the exacts steps to create an Excel drop-down list:

1. Using Data from Cells.
2. Entering Data Manually.
3. Using the OFFSET formula.

#1 Using Data from Cells

Let’s say you have a list of items as shown below:

Here are the steps to create an Excel Drop Down List:

1. Select a cell where you want to create the drop down list.
2. Go to Data –> Data Tools –> Data Validation.
3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
   • As soon as you select List, the source field appears.
4. In the source field, enter =$A$2:$A$6, or simply click in the Source field and select the cells using the mouse and click OK. This will insert a drop down list in cell C2.
   • Make sure that the In-cell dropdown option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, however, you can manually enter the values in the list.

Note: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

#2 By Entering Data Manually

In the above example, cell references are used in the Source field. You can also add items directly by entering it manually in the source field.

For example, let’s say you want to show two options, Yes and No, in the drop down in a cell. Here is how you can directly enter it in the data validation source field:

• Select a cell where you want to create the drop down list (cell C2 in this example).
• Go to Data –> Data Tools –> Data Validation.
• In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
  • As soon as you select List, the source field appears.
• In the source field, enter Yes, No
  • Make sure that the In-cell dropdown option is checked.
• Click OK.

This will create a drop-down list in the selected cell. All the items listed in the source field, separated by a comma, are listed in different lines in the drop down menu.

All the items entered in the source field, separated by a comma, are displayed in different lines in the drop down list.

Note: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps.

#3 Using Excel Formulas

1. Apart from selecting from cells and entering data manually, you can also use a formula in the source field to create an Excel drop down list.
2. Any formula that returns a list of values can be used to create a drop-down list in Excel.

3. For example, suppose you have the data set as shown below:

Here are the steps to create an Excel drop down list using the OFFSET function:

• Select a cell where you want to create the drop down list (cell C2 in this example).
• Go to Data –> Data Tools –> Data Validation.
• In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
  • As soon as you select List, the source field appears.
• In the Source field, enter the following formula: =OFFSET($A$2,0,0,5)
  • Make sure that the In-cell dropdown option is checked.
• Click OK.

This will create a drop-down list that lists all the fruit names (as shown below).

Note: If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

How this formula Works??

• In the above case, we used an OFFSET function to create the drop down list. It returns a list of items from the ra
• It returns a list of items from the range A2:A6.
• Here is the syntax of the OFFSET function: =OFFSET(reference, rows, cols, [height], [width])

It takes five arguments, where we specified the reference as A2 (the starting point of the list). Rows/Cols are specified as 0 as we don’t want to offset the reference cell. Height is specified as 5 as there are five elements in the list.

Now, when you use this formula, it returns an array that has the list of the five fruits in A2:A6. Note that if you enter the formula in a cell, select it and press F9, you would see that it returns an array of the fruit names.

Creating a Dynamic Drop Down List in Excel (Using OFFSET)

The above technique of using a formula to create a drop down list can be extended to create a dynamic drop down list as well. If you use the OFFSET function, as shown above, even if you add more items to the list, the drop down would not update automatically. You will have to manually update it each time you change the list.

Here is a way to make it dynamic (and it’s nothing but a minor tweak in the formula):

• Select a cell where you want to create the drop down list (cell C2 in this example).
• Go to Data –> Data Tools –> Data Validation.
• In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria. As soon as you select List, the source field appears.
• In the source field, enter the following formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,””))
• Make sure that the In-cell drop down option is checked.
• Click OK.

In this formula, I have replaced the argument 5 with COUNTIF($A$2:$A$100,””).

The COUNTIF function counts the non-blank cells in the range A2:A100. Hence, the OFFSET function adjusts itself to include all the non-blank cells.

Note:

• For this to work, there must NOT be any blank cells in between the cells that are filled.
• If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

You can copy paste the cells with data validation to other cells, and it will copy the data validation as well.

For example, if you have a drop-down list in cell C2, and you want to apply it to C3:C6 as well, simply copy the cell C2 and paste it in C3:C6. This will copy the drop-down list and make it available in C3:C6 (along with the drop down, it will also copy the formatting).

If you only want to copy the drop down and not the formatting, here are the steps:

• Copy the cell that has the drop down.
• Select the cells where you want to copy the drop down.
• Go to Home –> Paste –> Paste Special.
• In the Paste Special dialogue box, select Validation in Paste options.
• Click OK.

This will only copy the drop down and not the formatting of the copied cell.

Caution while Working with Excel Drop Down List

1. You need to to be careful when you are working with drop down lists in Excel.
2. When you copy a cell (that does not contain a drop down list) over a cell that contains a drop down list, the drop down list is lost.
3. The worst part of this is that Excel will not show any alert or prompt to let the user know that a drop down will be overwritten.

How to Select All Cells that have a Drop Down List in it

• Sometimes, it ‘s hard to know which cells contain the drop down list.
• Hence, it makes sense to mark these cells by either giving it a distinct border or a background color.
• Instead of manually checking all the cells, there is a quick way to select all the cells that have drop-down lists (or any data validation rule) in it.

• Go to Home –> Find & Select –> Go To Special.
• In the Go To Special dialogue box, select Data Validation
  • Data validation has two options: All and Same. All would select all the cells that have a data validation rule applied on it. Same would select only those cells that have the same data validation rule as that of the active cell.
• Click OK.

This would instantly select all the cells that have a data validation rule applied to it (this includes drop down lists as well).

Now you can simply format the cells (give a border or a background color) so that visually visible and you don’t accidentally copy another cell on it.

Here is another technique by Jon Acampora you can use to always keep the drop down arrow icon visible. You can also see some ways to do this in this video by Mr. Excel.

Creating a Dependent / Conditional Excel Drop Down List

1. Here is a video on how to create a dependent drop-down list in Excel.
2. If you prefer reading over watching a video, keep reading.
3. Sometimes, you may have more than one drop-down list and you want the items displayed in the second drop down to be dependent on what the user selected in the first drop-down.

4. These are called dependent or conditional drop down lists.
5. Below is an example of a conditional/dependent drop down list:
6. In the above example, when the items listed in ‘Drop Down 2’ are dependent on the selection made in ‘Drop Down 1’.
7. Now let’s see how to create this.

8. Here are the steps to create a dependent / conditional drop down list in Excel:

• Select the cell where you want the first (main) drop down list.
• Go to Data –> Data Validation. This will open the data validation dialog box.
• In the data validation dialog box, within the settings tab, select List.
• In Source field, specify the range that contains the items that are to be shown in the first drop down list.
• Click OK. This will create the Drop Down 1.
• Select the entire data set (A1:B6 in this example).
• Go to Formulas –> Defined Names –> Create from Selection (or you can use the keyboard shortcut Control + Shift + F3).
• In the ‘Create Named from Selection’ dialog box, check the Top row option and uncheck all the others. Doing this creates 2 names ranges (‘Fruits’ and ‘Vegetables’). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list.
• Click OK.
• Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example).
• Go to Data –> Data Validation.
• In the Data Validation dialog box, within the setting tab, make sure List in selected.
• In the Source field, enter the formula =INDIRECT(D3). Here, D3 is the cell that contains the main drop down.
• Click OK.

Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update.

Download the Example File

How does this work? – The conditional drop down list (in cell E3) refers to =INDIRECT(D3). This means that when you select ‘Fruits’ in cell D3, the drop down list in E3 refers to the named range ‘Fruits’ (through the INDIRECT function) and hence lists all the items in that category.

Important Note While Working with Conditional Drop Down Lists in Excel:

• When you have made the selection, and then you change the parent drop down, the dependent drop down would not change and would, therefore, be a wrong entry. For example, if you select the US as the country and then select Florida as the state, and then go back and change the country to India, the state would remain as Florida. Here is a great tutorial by Debra on clearing dependent (conditional) drop down lists in Excel when the selection is changed.
• If the main category is more than one word (for example, ‘Seasonal Fruits’ instead of ‘Fruits’), then you need to use the formula =INDIRECT(SUBSTITUTE(D3,” “,”_”)), instead of the simple INDIRECT function shown above. The reason for this is that Excel does not allow spaces in named ranges. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. So ‘Seasonal Fruits’ named range would be ‘Seasonal_Fruits’. Using the SUBSTITUTE function within the INDIRECT function makes sure that spaces are converted into underscores. 

Источник: https://trumpexcel.com/excel-drop-down-list/

Dynamic Dropdown List

This article will show you how to automatically update your drop-down list

Create a dropdown list in Excel

To create a dropdown list activate the menu Data > Data Validation

1. Select a cell (in our example F1)
2. Select the menu Data > Data Validation
3. In the dialog box, select the option List
4. Then select the cells that contain your data
5. Validate

Now your cell (F1) displays a small arrow. Click on it to display the list of the data selected.

But now, if you add new values in your source, they will not be display in your dropdown list.

The reason is simple. The range of cells of your dropdown list is still D2:D5 (and it should be D2:D6).

So, in order to create a dynamic dropdown list, we must use a Table, because with the tool Table, the range of data are dynamic.

Create a dynamic dropdown list

To make a dropdown list dynamic, use the Table tool.

1. Select your data source
2. Activate the tool Insert > Table
3. Very important: Check the option My table has headers

1. Re-open the tool Data > Data validation
2. Change the source with the following formula:

=INDIRECT(«Table1[Name]»)

And now your data source is dynamic ????????????

Tutorial video

Permanent link to this article: https://www.excel-exercise.com/dynamic-drop-down-list/

Источник: https://www.excel-exercise.com/dynamic-drop-down-list/

Create a Drop Down List in Excel

I love using drop down lists in Excel! They are extremely simple to create and are a great way to make a spreadsheet easier to use. In this article, I'll first show how to create an in-cell drop-down list using data validation, and then I'll show some examples that demonstrate awesome things you can do with drop downs.

Example drop-down lists in the Money Tracker template for the mobile Excel app.

To see some of the examples from this article in action, download the Excel file below.

Download the Example File (DropDownLists.xlsx)

Watch the Video

Drop Down List in Excel

You can create an in-cell drop down list in Excel by following these 4 easy steps:

1. Select the cell, or range of cells, where you want to add the drop-down list.
2. Go to Data > Validation > Settings tab (see image below)
3. Select «List» from the Allow: drop-down box
4. Enter your list in the Source: field using a comma to separate the items, or select a range of cells from your worksheet.

Entering the Source of a Drop Down List as a Comma-Delimited List

This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet.

The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you'll miss one. The more elegant approach is to use a reference to a range, or even better than that — a named range.

Defining a Drop-Down List using a Range

Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells.

For example, let's say I have a separate worksheet with my list defined in cells A1:A3 as shown below. In this case, I've named the range «myList».

You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list.

In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below:

Источник: https://www.vertex42.com/blog/help/excel-help/create-a-drop-down-list-in-excel.html

Как создать DropDown с динамическим списком проверки

У меня есть таблица данных со столбцами «Категория» и «Продукт». Названия продуктов не являются уникальными, имена категорий не являются уникальными, но комбинация Категория-Продукт появляется в таблице только один раз. Эта таблица данных НЕ сортируется каким-либо образом.

Category  Product

========  =======

Chairs      Victorian

Chairs      Beautiful

Chairs      Edwardian

Chairs      Gross

Tables      Victorian

Tables      Edwardian

Tables      Huge

Tables      Kool

Tables      Lambda

Closets     Edwardian

Closets     Excellent

Closets     Major

Closets     Hello

Chairs      Huge

Tables      Picturesque

Closets     Picturesque

Chairs      Incredible

Closets     Minor

Chairs      Just

Chairs      Kool

Я уже создал временную таблицу с уникальными именами категорий, которая будет использоваться в качестве диапазона проверки для первого раскрывающегося списка. Эта часть работает как надо.

Categories

==========

Chairs

Tables

Closets

Рядом с первым раскрывающимся списком у меня есть другой раскрывающийся список, в котором должен быть динамически созданный список продуктов категории, выбранной в предыдущем раскрывающемся списке.

Category:   Chairs           Product: Victorian

                                                  Beautiful

                                                  Edwardian

                                                  Gross

                                                  Huge

                                                  . . .

Проблема в том, что эти выпадающие пары должны быть частью другой таблицы со столбцами: «Категория», «Продукт», «Количество».

Итак, когда пользователь выбирает в первом столбце значение раскрывающегося списка Категория «Стулья», в следующем столбце раскрывающийся список должен быть доступен в списке только товаров из категории «Стулья».

В следующей строке, когда пользователь выбирает категорию «Таблицы», в соседней ячейке должны быть доступны только товары из категории «Таблицы».

• Я пытаюсь сделать это, используя только формулы, формулы массивов, именованные функции (не путайте их с функциями UDF) без кода VBA.
• Возможно ли это сделать или я трачу свое время (уже 2 дня)?
• Файл Excel с тем, что я уже сделал: здесь
• ОБНОВЛЕНИЕ (2019-09-30):

Нашел это в комментарии:
«Однако Excel не позволяет использовать формулы массивов при проверке данных», — Фернандо Дж. Ривера, 4 ноября 17 г. в Динамический раскрывающийся список Excel по отфильтрованной таблице

Таким образом, это означает, что НЕ можно проводить динамическую проверку данных.

Источник: http://qaru.site/questions/18545455/how-to-create-dropdown-which-have-dynamic-validation-list