If complex embedding problems for Excel data validity? 100

You're really tired of doing this!

You should use 4 columns on the right to make a data list, all the data encoding start and end numbers and the corresponding letters and models, and the list is complete, you can use the formula to operate the validity. For example: a, Zhang San, and you can also control the repetitive input problem!

Finally, hide these four columns.

Why does everyone have to set the number of digits differently? Zhang San has 10 digits, Li Si has 12 digits, and more than 100 people, have to be set to more than 100 digits?

In your current format, the formula is written as =and(left(d2)=f2,len(d2)=e2).

Conditional formatting can also be used to alert you to errors.

Make a one-to-one correspondence table.

Use the vlookup function.

or VBA to control.

I probably know what you mean.

Such a problem, if it is me, is generally to make a list, and then use the macro ** to compare the results, and an error prompt pops up.

If you have to use validity, you can also make a list of conditions first, and then use the countifs function to compare.

Personnel made a mistake, and the results of scanning Zhang San were scanned.

Take the question as the content (the subject of the email must contain "excel", I will use this as a basis to identify non-spam emails, so as not to delete them by mistake), and send the excel sample file (try to explain the current situation and target effect in detail) as an attachment to help you see.

Suppose column j returns the verdict result. j3=if(and(i3>2,i3<8),if(e3 do you mean that column e is blank at the beginning, according to the month of column fg and i2, enter column e? This is going to be done with macros.

There must be a cell in which row to determine which is the month.

This is a secondary drop-down menu issue.

Define the name first, and then select [Square Tank Shape!] d3:d1000], enter [square can] in the name box in the upper left corner, and select [rectangular can! d3:d1000], enter [rectangular can] in the name box in the upper left corner.

Then set the validity in the target cell, sequence --input=indirect(c$11).

Effect: When C11 is square tank shape, square tank shape can be selected! D3: D1000, when it is a rectangular tank, a rectangular tank can be selected! d3:d1000

There is no option when it is not equal to both.

Allow "Sequence" to Allow "Custom" Try it.

Select the A1:E20 cell range, in Data Validity under the Data option, Settings, Type, Custom, and enter =sum(A1:E20)<=2000;Enter "Sum exceeds 2000" in the error message field and OK to generate a custom error message.

Enter data into cell a1:e20, when the sum exceeds 2000, the error window will automatically pop up and prevent the wrong input.

See attached drawings for details.

Select a1:e20 first, and then click Data Validity, Custom, and enter in the formula.

sum($a$1:$e$20)<2000

Customize the data validity with custom settings and customize the inputs.

and(a1>=b1+c1,a1=d1+e1+f1)

You can't do it with a formula.

If you enter the number in column A, column B will automatically display the name, then column B will enter the formula, otherwise, enter the name in column B, and if you want to display the number in column A, then you can only choose one of the formulas in column A, (vlookup can be used), and the formula cannot be used at the same time.

If you want to meet your requirements, you should be able to do it with a VBA.

Fill in the formula first, then set the validity.

if(a2="","",vlookup(a2,d:f,2,))=if(a2="","",vlookup(a2,d:f,3,)).

Take your diagram to see: the data in the database is in columns d-f, you can:

b2 vlookup(a2,d:f,2,0)c2=vlookup(a2,d:f,3,0) are all pulled down for aesthetic good:

b2＝if(a2="","",vlookup(a2,d:f,2,0))

c2=if(a2="","",vlookup(a2,d:f,3,0))

1.First, open the made excel**.

2.Set the gender, you don't need to enter the data into it, you can directly click to select "male" or "female", select the cell in the gender, and then click the menu data - validity.

3.Set the data validity condition to Sequence in Data Validity, and then enter "Male and Female" in ** and separate them with commas in English.

4.Now you don't need to enter men and women, you just need to use the mouse to select them. This saves time for typing.

5.How can the data validity be set? For example, the ID number is usually 18 digits, and the cell format of the ID number position is set to text first. Because numbers with more than 11 digits are represented by scientific notation.

6.You can then set the validity of the data in the ID card cell. Set the data validity condition to Text Length, and set Data Length to 18.

7.This can effectively prevent us from entering one more or one less digit when entering the ID number, which can effectively improve the correctness of the input.

The assumptions are as follows. Enter =b1*if(a1,1) in c1

The meaning is: if a1 1, then c1 b1, otherwise 0

Of course, c1 can also be used for other formulas, the condition is multiplied by 1, and the condition is multiplied by 0, which basically achieves the goal.

Excel's data validity is not very friendly, this doesn't need to be explained too much, you can use conditional formatting in the future**, I build you with conditional formatting.

For example, I would like to:

When A1 11, make B1 less than 100, and when A1 22, let B1 be greater than 100, otherwise call the police.

We can do this in:

Set the conditional format of b1 to the "formula" option, and the content is =and(a1=11,b1>=100), note that "b1>=100", not that I wrote it wrong. Choose the format to exaggerate, the background is all red, and condition 1 is translated as, if A1 is equal to 11, and B1 is greater than or equal to 100, then "alarm", and the content of the alarm is that the background of the selected cell is pure red.

Set the second condition, also select "formula", the content is =and(a1=22,b1<=100), and the format is the same as above.

In this way, when the input is incorrect, the background of the set format will be red immediately, and it will automatically return to normal after modification.

Pure manual input, give points to it.

Do you want to complete this comparison table or do you want to make judgments based on this comparison table?

How to set the data validity in the mobile phone WPS**, the data validity controls the data length.

1. First open Excel** and enter the relevant information.

2. Then select the options shown in the diagram. This is important.

3. Then copy its contents.

4. Then we need to check all selections.

5. Then press Ctrl+G to check the options shown in the diagram.

6. Then find a random cell and right-click to select paste.

7. Finally, you can paste it. The method is simple.

Excel has cell formatting and text formatting. Data validity is the setting of cells, and when copying and pasting, the original cell format is pasted together, which destroys the cell format of data validity.

The specific solution is: when pasting, paste selectively, select "value", so that the cell validity can be preserved.

There is a picture and there is a truth:

Effectivity is a formatting setting, and if you choose a value when copying and pasting, it means that the formatting is removed.