List in Excel cell

The dropdown list is a very useful feature that saves a lot of time. It is useful if you need to restrict the ability to enter certain data. For example, each employee on duty must fill out a plate where it is necessary to indicate the department in which he works. To simplify the task as the worker himself, as well as to automate the data collection process, saving him from misprints made by people, there are lists. After all, then it is enough just to specify the list of departments in advance, and the user needs to choose the right one.

The most elementary method

The easiest way is to left-click a cell that does not contain any values, and then go to the context menu, where you click “Select from drop-down list”. You can also use Alt + Down Arrow. However, this method has a limitation. This means that the list does not work if the cell in which the list is scheduled is separate from the cells from which the information is retrieved. Also, this method comes with very few additional features.

But for basic tasks, the result will be normal.

Standard method

  1. Select the cells where the data is indicated, which will be the source for the drop-down list (in the case of our table, these are the names of the goods).
  2. The next steps depend on the version of Excel. If it is from 2003 (yes, it is still being used), you need to follow the path “Insert – Name – Assign”. If you are using a newer version of the program, you need to click on the “Formulas” tab, where the Name Manager is located. After clicking on it, you need to find the “Create” button and also left-click on it. After that, you need to enter a name that meets the following requirements:
    1. no gaps.
    2. starts with a !.
  3. Confirm the settings by pressing the “OK” button.
  4. Select the range that will be used as the data source for the list and click on the “Data” button on the ribbon where you need to click on the “Check” item. A list will appear in which you need to select a data type. In our example, this is “List”. Then enter the symbol “=” in the field, and the name of the range. In our case =Products.

Next, you need to click on the “OK” button, and the cell will turn into a list that appears when you click on it.

It is important to consider that dynamic range can also be used. Often, price lists are also used as a source of information. Then when new elements appear, they are automatically added to the list.

Lists of this type can be linked to others.

Control element

This method provides for the creation of a new element – a combo box and with further binding of the necessary cells to it. To accomplish this task, follow the instructions below:

  1. If you are using the latest versions of Excel (from 2007), then you should open the “Developer” tab. If the version is older, you need to go to the “Forms” panel, which can be accessed through the menu View – Toolbars. If this tab is not displayed for you, then you need to find the “Office” button (available only in versions 2007 and newer) and click on it. Next – open “Excel Options”, and there check the box next to the item “Display Developer tab on the ribbon.”
  2. Find the drop-down symbol on the top panel (not Active X, but normal). To find the correct element, you need to hover over each of them with the mouse and wait a few seconds until a tooltip appears, as shown in the figure. 3.gif
  3. Click on this icon.
  4. Draw an element, then call its context menu.

After all these operations, a context menu will appear, where you should select the “Object Format” item and set the following parameters:

  1. Form a list by range. Then it will be necessary to select the data on the basis of which the future list will be formed. 
  2. Communication with a cell. Here you need to select the cell in which the data you specified will be displayed.
  3. The number of lines in the list. Here you need to specify how many lines the list should output. 8 items are preset, but you can make the list more. The previous paragraph does not have such possibilities.

Next, click “OK” and the list is ready to use.

It can be done so that the name of the element is displayed instead of the element number. You must use the function INDEXto accomplish this task.

Active X element

This method is similar to the previous one, with the only difference being that it uses the Combo Box Active X control located on the same menu where it is invoked by clicking the Insert button on the Developer tab. It has several advantages over the previous one.

In general, the mechanism for adding it is the same as for a regular element: you need to select it and draw it. After that, the fundamental differences begin. The fact is that the Active X element has two modes: debug and input. In the first variant, its flexible configuration, editing of parameters, appearance and location on the sheet is possible. If the input mode is selected, then it can be used only for its intended purpose: select any of the items presented in it.

To switch between debug and input modes, you need to click on the “Design Mode” button located on the same “Developer” tab.

When you click this button, you can edit the list options, which can be accessed through the Properties button located to the right of the Design Mode button.

The most useful options are:

  1. ListFillRange – The range that supplies the elements of the list. In this case, there is no way to select the range yourself, so you have to enter it manually. For example, like this: “Sheet2!A1:A5”.
  2. LinkedCell – the linked cell in which Excel will write the element.
  3. ListRows – number of rows.
  4. Font is a fundamentally new element that allows you to change the font (except for its color).
  5. ForeColor and BackColor are two more new options that let you customize the color of the text and background.

A huge advantage of this method is that it is possible to quickly access the required list element when entering the first letters. This is a unique advantage that other methods do not have. In addition, you can customize the appearance of the list, which allows you to make the process of entering data into the table faster with a large amount of data distributed among different types.

If you use this listing method, you can also specify multidimensional ranges. For example, a range consisting of two columns, additionally specifying the need to display two columns (for this, you need to set the ColumnCount=2 parameter). Then you can achieve very interesting results that will more than pay off all the effort and time spent on setting up the Active X Combo Box control.

How to create a linked list in Excel?

Linked lists can be useful in a variety of situations. For example, to provide the ability to select a list of cities in a particular area. In practice, it will look like this: a person selects a region, and the second list is updated with a list of cities that are unique to him.

This can be done by different methods.

Method 1: INDIRECT Function

In the first case, you need to apply the function INDIRECT, capable of converting any text into a link. For example, if A1 is written, then the function will return a reference to a cell that has the same address. If the text “Masha” is written in the cell, then this formula will be able to make a link to a named range with that name. Simply put, she can change the link and title in places.

Suppose we need to compile such a list of models of brands Toyota, Ford, Nissan.

The Toyota lineup must be distinguished, starting with cell A2 up to the end of the list and named in the same way. If an ancient version of Excel is used, this function is implemented through the menu “Insert – Name – Assign”. If the version of Excel is not the oldest (starting from 2007), then you need to go to the “Formulas” tab, where you will find the “Name Manager”. A similar operation is carried out with a list of the model range of other car manufacturers.

When naming, it’s important to keep in mind that you can’t compose range names with spaces or punctuation. Also, you can’t start them with a number. Thus, if at least one of the marks contained a space, it should have been replaced with an underscore.

Now we need to generate a list of cars. You need to click (left click) on an empty cell and open the menu Data – Validation for Excel 2003 versions and the “Data Validation” button on the “Data” tab for Excel 2007 versions and more modern. Then, in the “Data type” list, you need to select the List, and in the “Source” field, write the cells with the names of the brands.

Now you should form a second list, where cars from the lineup will be listed. The sequence of actions is the same, but with the only exception that in the Source field you need to specify the formula =INDIRECT(F3). Naturally, in brackets you need to indicate the address of the cell that is suitable in your case. Everything, after you click OK, the formation of the second list will be carried out based on the data in the first.

But this method has a number of significant drawbacks:

  1. You cannot use as dependent lists those that are specified by functions of the type DISPOSAL.
  2. The names of the elements of the primary drop-down range can only be specified without spaces, so they will have to be replaced with an underscore.
  3. The need to create a large number of named ranges. this is especially inconvenient if there are many car brands.

Using the OFFSET and MATCH function

To use this method of generating dependent lists, you must have a sorted list of make-model matches, as shown in this example.

To form the first list, which will affect the contents of the second, the standard method described above is also suitable, that is:

  1. Open the Name Manager and name the appropriate range.
  2. Click on the “Data” tab, where select the “Data Validation” command.
  3. Select, and as a range that serves as a source, specify the appropriate name or select the necessary cells.

But for the second list, which will be affected by the first, you will have to perform other actions, namely, create a named range with the =OFFSET function, which creates a link to a cell where only models of a certain brand are indicated. And for this, the following actions are carried out:

  1. You need to press the combination Ctrl + F3. You can also open the Name Manager and name the appropriate range.
  2. Create a new range along with a function DISPOSAL in the link and give it a name. It can be anything, for example, “Models”.

In our situation, the formula will be as follows:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

It is important to specify only absolute links. That is, they must include the $ sign. After pressing the Enter key, Excel will automatically add the sheet names. This shouldn’t scare you.

The result will be as follows:

Finally, you need to insert a dropdown based on the above formula. To do this, in our example, you need to do the following:

  1. Select cell G8.
  2. Select the Data Validation command on the Data tab.
  3. Where the source of the list is, specify the name of the range: =Models.

And yet!

Conclusions

In a word, of all the ways, the first is the easiest. It is characterized by low complexity, but you can neither adjust the font color, nor the style, background, number of lines that will be displayed, quickly find the desired element by the first letters (which is especially important if the list is large), and also create related lists.

The most advanced method is the Active X control, but which can do just about anything. But it also has a limitation. If it is necessary to create linked lists, then this possibility is typical only for the second method.

It is also important to remember about the limitation of the first two methods – the ability to use no more than 8 elements in one list. The last two do not have this disadvantage.

Leave a Reply