Select from the excel merge list. Create a list of what appears in Excel. Definition of ActiveX elements

An element to the list of knowledge for us behind the forms on the sites. You can manually select ready-made values. For example, you should not enter the month manually, but take it from such transfer. You can fill out a drop-down list in Excel using other tools. Let's take a look at the skin of them.

How to create a list of what appears in Excel

How can I create a list of what appears in Excel 2010 or 2016 using one additional command on the toolbar? On the “Data” tab, in the “Working with Data” section, find the “Data Verification” button. Click and select the first item.

The window will open. In the “Parameters” tab, in the “Data type” section, select “List”.


Below there will be a row for inserting dzherel.


Information can be presented in different ways.

From the very beginning it is significant. Why create a table for anyone?

See it and press the right mouse button. Click on the “Assign names” command.

Enter the names of the animals in a row.

Select the “Data Verification” box and enter the name in the “Dzherelo” field, placing the “=” sign in front of it.


In any of the three situations, you will obtain the required element. The selection of values ​​from the Excel list is carried out according to your choice. Press and a stream of the designated data will appear.

You learned how to create a list of what comes up in Excel. You can also earn more.

Substituting dynamic Excel data

If you add any values ​​to the range of data that are submitted before the transfer, then no changes will be made until new addresses are entered manually. To link a range and an active element, you need to format the first one as a table. Create such an array.

See it and on the “Head” tab, select any table style.


Obviously check the box below.

You reject this design.

Create the active element as described above. How to enter the formula

DVSSIL("Table1[Location]")

To find out the name of the table, go to the “Design” tab and look at it. You can change the name to something else.


The DVSSIL function creates a message sent to the computer or range. Now your element has ties to an array of data.

Let's try to increase the number of places.


Gate procedure - substitution of data into the list that falls into Excel spreadsheet It's very simple. For a business where you want to insert the selected table values, enter the formula:

Middle_addresses

For example, if the data flow is in the middle D1, then in the middle where the selected results will be displayed, enter the formula

How to tidy up (visualize) the list that appears in Excel

Open the customized list and select “Whatever value” in the “Data type” section.

The unnecessary element has disappeared.

Delayed elements

Sometimes in Excel there is a need to create many transfers if one lies behind the other. For example, the skin area has the same address. When choosing first, we can select the address of the selected locality.


For any problem, give it to the skin. See without the first mark (name) and press the right mouse button. Select the “Assign a name” item.

This will be the name of the place.


With the name of St. Petersburg and Nizhny Novgorod You cancel the payment, the fragments of your name cannot be filled with spaces, accent symbols, special characters etc.


Therefore, we change the place by placing the lower armchairs.


The first element in the middle A9 is created in the first order.


And in another we write the formula:

DVSSIL(A9)

You will receive a notification about the amendment as soon as possible. Wait a minute.

The problem is with the specificity of the selected value. As soon as a place is chosen in the first shift, another will be asked.

How to customize stale lists that fall into Excel by search

You can select a dynamic range of data for another element. This is more important because the address is so large.
Let's pour over the place that falls out. The naming range is visible in orange.


For another transfer you need to enter the formula:

ZMISH($A$1,SHUKPOZ($E$6,$A:$A,0)-1,1,RAKHUNKI($A:$A,$E$6),1)

SHOOKPOZ rotates the sales number from the first list (E6) to the designated area SA:$A.
RAKHUNKI depends on the number of escapes in the range of the values ​​​​at the designated price (E6).

We selected the related lists that fell into Excel in order to find a range for the new one.

Multivibir

Often we need to extract some value from a set of data. You can display them in different middles, or you can put them into one. Every skin needs a macro.
Press the mouse button on the arkush label at the bottom right and select the “Review code” command.


The retailer window will open. Insert the next algorithm into the next track.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Bring back what's in order

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Place the address in the middle of the list. We will have E7.

Go to the Excel sheet and create a list in the middle E7.

When selected, the values ​​will appear below it.

The current code will allow you to accumulate value in business.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval)<>0 And oldval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

As soon as you transfer your order to another company, you will receive a list of selected places. To read this article.


We learned how to add and change the drop-down list Excel middle. We hope this information will help you.

Have a wonderful day!

The list that appears in Excel is a manual function that will help you create more complex documents and perform manual work for the visual recognition of the clerk.

Some of the most extensive types of drop-down lists that can be created in Excel:

Create a list in Excel with multi-select

You can create a list in the middle of the program by using the installed tools in the menu panel.

Let's take a look at all the main and most broad types, and the process of their creation in practice.

Please! The classic collapsible list program has a multi-select function, so that the user, by clicking on a similar group, can select a similar option.

Multi-select is necessary if you need to set the values ​​of several document ends in addition to one transition.

Exterior look with multi-selection to fill multiple middles.

• See commercials. If you marvel at the little ones, you need to start with C2 and end with C5;
• Find the “Data” tab, which is located on the main toolbar in the program window. Then press the data verification key, as shown in the picture below;

Data verification

• In the window, open the first tab and select the element as shown in the little one. In this manner, it will be overflowing in these places. In the text field, indicate the range of items that will be displayed when selecting elements.

Fill in the information for the following list with what appears, with a multi-select middle value

EtcNumber of entries:

In this way you create a classic horizontal list with multi-select function.

However, in order for it to be filled in automatically, it is necessary to create a macro from the current one. output code, which is shown on the baby.

Program code for creating a macro

• Excel hotkeys - The most useful options
• EXCEL formulas with butts - Application instructions
• How to create a graph in Excel - Instructions

Create a list in Excel with new additions

Standard transfer from the top allows you to automate the process of entering information. When pressed on it, a selection of possible values ​​will appear.

Koristuvachevi is deprived of selecting the required value for filling.

List of koristuvachs with ranks

The simplest way to create such a change is to use “smart program tables”.

With their help, you can easily and quickly format the types of lists you need from the following:

• See the necessary cells and click on the “Format as table” button in the main tab;

Butt formatting and layout of keys:

Format process

• Using the built-in constructor, you can change the table columns and their meanings.

Format the flow from the surface using an additional “smart table”

Place the list that opens in the middle (program version 2010)

You can also create a spill in the middle of the arkush.

The butt is pointed at the little one below:

Butt at the middle of the sheet

To create one, follow the instructions:

Completed and re-verified value to be entered in the process of creating a list from the completed middle of the arch

You may also like:

• Rounding in Excel - Pokrokov's instructions
• Excel table - Creation and adjustment

Pouches

The article looked at the main types of lists that appear and how to create them. Please remember that the creation process is identical for the following program versions: 2007, 2010, 2013.

Vip lists will help you get more knowledgeable Swedish robot of the entire document, and we will adapt it to work with great projects.

Thematic videos up to the statistics:

Creating drop-down lists in Excel

4 ways to create a list of what comes up in Excel.

How to create a list of what you need to create in Excel

How to create a collapsible list in Excel How to add a collapsible list to Excel How to create a list in Excel that will open

Select to add to the list. In the menu, open the items “Data” - “Reverification”. After that, go to the “Parameters” tab in the new window and in the “Data type” field that opens, select the “List” row. In this case, the “Dzherelo” field will appear. Enter the symbol “=” to indicate the name of the visible range that was specified in the middle of the data. To set the parameters, press “Enter” or “Ok”. This is the simplest option for the list that appears.

In this case, the “Dzherelo” field will appear. Enter the symbol “=” to indicate the name of the visible range that was specified in the middle of the data. Press “Enter” or “Ok” to finalize the settings. This is the simplest option for the list that appears.

Excel has the ability to create a list, which is more flexible. For this purpose, create an element under the name “combo box”, which is inserted into the Excel worksheet. To install it, open the “View” menu items, then “Toolbars” and the “Forms” sub-item.

Select the “list box” icon on the menu item panel - this is the list that drops down. Draw a teddy bear next to the field shape. Use the right mouse button to view the list of drawings and select the command “Format Object...”.

In the dialog box that appears, in the “Form list by range” field, specify the required range of products. For whom you can see the items that are responsible for entering into this Excel list that appears. In the “Contact with commercial” field, set the commercial number to enter the serial number of the item selected from the list. Indicate the required number of rows folded list. The “Ok” button will freeze all installed parameters and the list of ready ones until they are selected.

In the simplest way, this is how it works. Onslaught right button by trade under the supplier with data viklimo context not menu. Here's a field Select from the list. You can do the same by pressing the key combination Alt+Down Arrow.

However, this method does not work if you want to create a list in another company that is not included in the range of more than one before or after. This allows you to use the offensive method.

Standard method

Required see the range of averages for which it will be created random list, after what Insert – I'm – Assign(Excel 2003). More new versions(2007, 2010, 2013, 2016) go to tab Formula, where we separated Song names show button Name Manager.

Embossed button Creativity, enter them, you can, after which OK.

See commercials(or sprout), if you want to insert a selection of required fields. Select from the menu Dani – Data type – overflow. In the field Dzherelo Enter the name before creating it, or you can simply enter a range that will be equal.

Now you can remove the middle copy If there is a place on the table, there will be a transfer of the necessary elements of the table. You can also expand to see the range of lists that appear.

The right moment is that when you change the data in the range, the grounds for the new list will also change, so that you will dynamic.

Vikoristamo elementi keruvannya

Foundation method inserted control element, called " list box“, What will be the range of data.

Select tab Rozrobnik(for Excel 2007/2010), in other versions you need to activate this tab on the page in parameters – Adjust the line.

Go to the qiu tab - emboss the button Paste. Selectable in the control elements List box(not ActiveX) and click . Paint upright.

Right button on nom - Object format.

Click on the checkbox to select the field where you can place the serial number of the item in the list. Then we emboss it OK.

Definition of ActiveX elements

Everything, as before, is only chosen List box(ActiveX).

Hello everyone, dear friends and guests of my blog. I’m calling you again, Dmitry Kostina, and today I would like to tell you more about Excel, or more precisely about one wonderful feature that I’m now forever stagnant with. We were worried about the situation. When you fill out the table and in each column, you must continuously enter one of several values. Eeeee. Let's tell you more clearly in practice.

For example, if I created a table for the type of computer equipment (long ago) on my computer, then in order to make the whole process more manual and quick, I would go through the list of what appears in the columns and insert the values ​​there. And if I smelled a hundred percent. Operating system"(And even though not all computers have the same one), then I entered a number of values ​​(7, 8, 8.1, 10), and then simply selected all with one click of the mouse button.

And in this way, it is no longer necessary to type the Windows version into each middle, or copy from one middle and paste into another. I won’t bore you with anything, let’s get started quickly. Let me show you how to create a list of what comes up in Excel, using data from other sources. For this purpose we create a sign, as far as possible. I am working in the 2013 version, but for other versions the process is identical, so don’t worry.

Preparation

Main actions

Now just do the same with the graphs “The nickname of the fakhivtsya”і “Pіdsumok usunennya”, then turn back to the head arch and begin to fully work with the table. You will see for yourself how healthy and easy it is if you can select data from the values ​​available in advance. For those who routinely fill out the table, it will be easier.

Before the speech in such documents is more beautiful for the manual presentation. Then everything will be cool.

Well, I’ll finish my article for today. I am confident that what you learned today will be of use to you when working in Excel. If you enjoyed the article, don’t forget to subscribe to the new blog. Well, I eagerly welcome you again on the pages of my blog. Good luck and goodbye!

With respect, Dmitro Kostin