Excel's cell data validation sequence settings

This article focuses on: the source of the Excel cell data validation sequence

Effect: When you click on a cell that has a set data validation sequence, a Drop-down list is available for the user to choose from.

The steps to bring up data validation settings.

1, first select the target you want to set data validation is a column or row or a cell or a range of cells.

2, the menu bar---data---validity, open the "Settings" interface, "Allow" to select "sequence", then next, the focus of this article is in the "source" box how to set.

First, the constant type of source settings

To put it simply, the list will not be changed as you set it up, and it will apply to lists that don't change frequently. For example: Sales department, customer service Department, Finance Department, Ministry of Personnel and so on, the company will not arbitrarily change these several departments.

The benefits of this setting are that list information does not account for worksheet resources, and information is stored in the application.

Set method: Enter List List into the box under source.

Special Note: Different lists in the middle of "," the number of separate, this symbol is in the English input method status, do not confuse. (My practice is, first with Wubi the text out, and then switch to the English state, enter the "," number)

When the settings are complete, click OK, and then return to the Excel table to see the effect

Second, the variable source settings, also known as the reference type source settings.

This setting is where you can specify a range of cells to be the source of the list, either this worksheet or another worksheet in this workbook. Of course, what text do you want to use as a list in this area, allowing content to be changed at any time. Of course, the sequence of data validation changes is also updated.

1, the common reference type "source" setting

(1, specified directly in this table.)

For example, set a1:a4 as the source, the method is as follows: directly in the "Source" box, click the mouse to activate, and then click the mouse to live A1 cell, drag down directly to the A4 cell. The default state is an absolute reference, if it is manually entered in the Source box, remember to press F4, or shift+ 4 keys, so that you can enter the "$" symbol

This method is suitable to be done in the current sheet table. If your source data list is from another sheet table in this workbook, you will not be able to specify "source", and you need a name formula.

(2, specified in the other tables in this workbook.)

For example, the list of data is in the A1:a4 cell of the Sheet 1 worksheet, and now you want to set the data validation sequence for the B5 cell of the Sheet 2 worksheet, which comes from the sheet area of the A1:A4 1 worksheet.

The method is as follows:

A 1th step.

Switch the work window to the Sheet 2 worksheet, open the menu bar-insert-name, select Define, and open the Define Name Settings window.

In the reference location, delete the current fill, then click the Sheet 1 worksheet tab and select the A1:a4 area with the mouse. By default, your final result should be =sheet1! $A $: $A $ check it out oh ... Then, in the box under the name in the current workbook, type the name you defined for the formula, such as "department list," and then click "Add" and "OK" on the right button.

As shown in the following figure

A 2nd step.

Sets the data validation sequence for the B5 cell of the sheet 2 worksheet. Menu Open sequence Do you remember that? The menu bar---"Data"-----"effectivity", and in the settings interface, set "Allow" to "sequence."

So how do you write "source" next? Please enter the "= Department list" inside the quotation marks

The 3rd step, check the effect, hehe, became, then, if you sheet 1 worksheets in the "Ministry of Personnel" changed to "administrative department"? How did it turn out? Oh, is not very fun.

So if you insert a row in the A1 and A4 cell, enter "General manager", now return to the Sheet 2 worksheet, see B 5 cell, and tell me what you found? is not similar to the following figure. Oh.....

The above method applies to situations where hurry cannot determine the contents of a list. If there is an increase, allow new cell data to be inserted between cell 1th and the last 1 cells.

Note: The last cell in our example is A4, and if you now want to fill in the new data in cell A5, then the data validation sequence will not show this one. Because of our name formula, we only define the A1:A4.

If you're not sure what the last cell in the future is, you'll need this more complex approach.

In the previous article, the data validation sequence of constants was set, similar to a fixed target, using a common reference type setting method, similar to a single direction moving targets. Next, we'll talk about moving targets in two directions, and you can't be sure how to set the position of the cell at the end of the data source.

The contents are as follows:

2, find the source setting for the offset reference type

Prerequisite: List data from the Sheet 1 worksheet A column, the number of the list is uncertain, whether or not to add data before and after the list is uncertain. The data validation sequence is now set in the B 5 cell of the Sheet 2 worksheet. The sequence is derived from the sheet column

Method: Because the data is using another sheet table, so as before, we preferred the sheet 2 Working window to set the name formula. To set the name formula do you still have it? If not, let's do it together.

<1, definition name

Point Open Sheet 2 Worksheet window, menu bar---"Insert"---"name"-"definition"

Fill in the Reference position box

=index (sheet1! $A: $A, 1,1): OFFSET (INDEX (sheet1! $A: $A, 1,1), COUNTA (sheet1! $A: $A) -1,0,,)

Define the name "My list," and then click "Add" and "OK".

<2, setting validity

Still in the Sheet 2 Worksheet window, click B 5 Cell, and then from the menu bar-"Data"-"effectivity" setting allows values to be sequence, enter = My list in the source box is OK. OK, hehe.

So now try the results, you add a new line to line 1th of column A in sheet 1, and then fill in the data, look at the effect in the B5 cell in sheet 2, or you can fill out a data in the sheet column immediately after the last line. Oh, is not the flexibility is very big.

<3, Formula parsing

=index (sheet1! $A: $A, 1, 1)

This section represents the beginning of the position source data, always the 1th cell.

The index function, which specifies the cross cell that is returned to row 1th and column 1th of column A in the Sheet 1 worksheet.

OFFSET (INDEX (sheet1! $A: $A, 1,1), COUNTA (sheet1! $A: $A) -1,0,,)

This section represents the end cell of the location source data,

The offset function is an offset function that is represented in this formula to (INDEX (sheet1! $A: $A, 1, 1) as the reference point, offset downward counta (sheet1! $A: $A)-1 rows, and offset 0 columns to the right.

The CountA function counts the number of rows of data in the sheet1! $A. Minus 1 is because this statistic is about how many rows there are, and the offset function does not compute the reference line, so you need to subtract 1 rows.

<4, related instructions

The above formula counts the range is column A, because this column cannot be used for other purposes, and if you do not need to count 1 columns, you can specify the CountA function as similar to

COUNTA (sheet1! $A: $A, 1,1): sheet1! $A 30)

You cannot delete a data row from a source datasheet, or you can cause an error, and if you need to change it, use a copy-and-paste format to keep the top 1 rows and the next 1 lines blank.

<5, about Excel cell data validation sequence settings other use

Limited to space and difficulty, this time only brief introduction

Use a: Two-level reference application.

For example, writing tools can be divided into pens, pencils, water-based pens, and pens with hero pens, parker pens, Montblanc pens and so on. Simply list the categories of the source data. You can use the validation sequence to display a list of small classes yourself, depending on the name of the large class.

Design idea: 1, use Index to find the location of large category name;

2, using offset with large category name as the reference point, two-way moving targets to determine the end

Use two: Set the validation sequence for numbers that are constantly changing.

For example, A5 cell is the current sales price, in A17 set the data validation sequence for =A5, then the input is the price, but if the next month sales price changes, A5 value changes, then the next month A17 value is the new value. But the value you entered last month is not affected.