In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets. Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation. Continue reading
Comments page 3. Total comments: 286
I want to creat a custom date like mm-dd-yyyy is possible
Hi!
Try to use the recommendations described in this article: How to change Excel date format and create custom formatting.
Good day
How would I go about to allow only a-z or A-Z (no numeric in the text) and also that the entry may not exceed 40 characters. With ISTEXT it allows abc123. Thanking you in advance.
Hello!
You can find the answer to your question in this article: Excel Data Validation using regular expressions.
Without wanting to push my luck (because I already had a question answered here in record time)... :-)
Is it possible to automatically hide/unhide rows/columns depending on the answer in a validated list?
Example:
A1 contains a validated drop down list of nationalities (100+ options)
In Row 2 I have written (field B1): ID number. But that entire line (where the answer has to be given in B2 what the ID number is) should only be visible if in A1 the Answer "Dutch" was selected.
If any other value that "Dutch" was selected in A1, then row 2 should be invisble but row 3 (where the person has to fill in his Social Security number should become visible.
So in short: if A1 = "Dutch" then make row 2 visible and row 3 invisble. If A1 = any other value than "Dutch" in the dropdown menu, then make row 2 invisble and row 3 visible.
Thanks again!
Hi!
To hide a row based on the value of a cell, you can use an Excel Filter or create a VBA macro.
Hi,
I need to find a solution to the following challenge
Cell B2 has a dropdown list of 5 choices (A/B/C/D/E as an example)
Cell C2 now needs to contain dropdown lists which are particular to what is in B2. So If B2 = "A" then the dropdown in C2 needs to contain a certain amount of options in a dropdown menu which is tied to "A" (these values that are linked to A/B/C/D/E are in a separate tab and are each in a different column in that tab sheet).
I tried "custom" and then went for =if(B2="A"; [target fields for dropdown linked to A here]); if(B2="B"; [target fields for dropdown linked to A here]); ... and so on.
But that does not work. :-(
Many thanks!
Hello!
If I understand your task correctly, the following tutorial should help: How to make a dependent (cascading) drop-down list in Excel.
Hi Alexander,
Yes, that's it! Many thanks!!! (also for the lightning-fast reply :-)
Kind regards,
Philip
Hello,
Is it possible to create the drop down (data validation) using formula? I need to create the excel file in email attachment in SAP environment and I can only create the data validation in excel file using formulas (in development perspective).
Hello,
Is there a possibility to set up a different areas to search in based on data in a specific cell?
Imagine you have ten customers. In one cell (B11), you use data validation - list - source (K10:K20) to choose the customer from the list.
Any idea how to arrange that in another cell (B21) I could choose from the list of different sides that every customer has, but only from the list that belongs to the particular customer?
Thank you,
Hello!
You can write a range address or range name in a cell and convert it to a reference using the INDIRECT function. I hope it’ll be helpful.
Using custom validation, the cell can only show "0" or values between 10,000 - 36,000. How do I do this?
Hello!
Create a data validation formula using the IF function. Use the instruction: Excel IF OR statement.
=IF(OR(A1=0, AND(A1>=10000,A1<=30000)),TRUE)
This should solve your task.
Is it possible to create a data validation formula that returns only the distinct values from a table column?
Hello!
If I understand your task correctly, the following tutorial should help: Data validation to allow only unique entries and disallow duplicates.
Sorry Alexander, I did not qualify the challenge well. The end game is to have a cell with a drop-down data validation list. The content of the list contains only the distinct values contained in a column of a table. The table rows are dynamic based on the data query that populates the table.
Hi!
Write in a separate cell (for example, Z1) a formula with the UNIQUE function to get a list of unique values in a column.
For example,
=UNIQUE($A$3:$A$100)
Create a dropdown list and specify the data source for it:
=$Z$1#
You will get a dropdown list with unique values.
For more information, please visit: Make a dynamic drop down list in Excel.
Sir, how I write a data validation formula for entering a text and number combination format of 320.6 ABC.
It should always be 3 digits, 1 decimal, then space and 3 English characters. format should be 320.6 ABC
Thanks
Hello!
Try to use the recommendations described in this article: Excel Data Validation using regular expressions (Regex). I hope it’ll be helpful.
Hey!
This was awesome. If I want to have a drop down of the numbers 1-50 and they can only select each number once (for example, identifying a list of their top 50 out of 2000) what formula would I use?
Hi!
If I understand your task correctly, here is the article that may be helpful to you: How to prevent duplicates in a column in Excel.
Hello,
Could you tell me please how to set up a column where only a four digit number is allowed (a year)? Thank you.
Hello!
Determine the number of digits in a number using the LEN function.
You can add a check for a number using the ISNUMBER function.
=AND(LEN(A1)=4,ISNUMBER(A1))
Hi, thank you for your valuable help. Could you please tell me how I can ensure that my user enters Text first then numbers? Examples: ABC123 or ABCD12 or AB1234 etc, there is no set rule to 'count' of alphabet or numbers.
Hello!
The following tutorial should help: Excel Data Validation using regular expressions (Regex). I hope this will help.
Hey! So I'm trying to do a nested if function for a list dropdown and my issue is that I have too many options and it exceeds the character limit of the source bar in the data validation popup. Is there any way to get around this?
Hello!
Instead of a nested IF, try using the IFS function, which allows you to evaluate up to 127 different conditions. I hope this will help.
Hi,
I have a drop down menu with a list of percentage ranges and I want a different calculation dependent on each selection:
< 90% then deduct 10% from cell E9 and update total in cell L9
90-95% then deduct 5% from cell E9 and update total In cell L9
96-99% then deduct 2.5% from cell E9 and update total in cell L9
I initially tried IF statements based on the ‘text’ selected but it is only updating the total based on each individual cell with a drop down list, and I need each cell with the drop down menu to keep updating one cell total collectively.
Can you help at all please?
Hi!
If a value is written in cell E9, then you cannot change it using an Excel formula. We have talked about this many times in our blog. This can be done using a VBA macro. You can write an IF formula with nested conditions in another cell based on the selected text.
HI,
I have a data set with a validation rule which does not allow names to be duplicated across the row. I would like to add a drop down list for the names to be selected from but when I try to do this it erases the previous validation rule which prevents the duplication. Is there any way I can add these two rules together?
Thanks
Hi!
There can only be one validation rule or dropdown list per cell.
Hello,
Please, I need help in excel coding:
first, I want to know the coding formula for phone number validation to be equal to 8 characters e.g.: 65000000
second, I used the formula "${hh_depend}+${hh_workf}" to calculate the sum of 2 variables. And I need validation coding which allows this total value to be strictly less than the answer of a previous variable ${hh_size} value.
Thank you
Hi!
To allow data entry of a specific length, when creating a rule select Text length in the Allow box. To check the sum of two cells, create a formula as described in the article above.
Hi,
I created a data validation drop down list.
A: Date not synced
B: Term
C: Item
D: Amount
E: Combination: ________
Like to ask your help on how to put a formula, when I pick "E" I can write a comment on the it. Like for example, combination of A&B or C&D or ABC or combination of everything
Hi!
You cannot change the values that are inserted into a cell using a drop-down list. You can create in your dropdown list more options for item e and write down all the possible combinations.
I need to restrict a field using Data Validation so that the user can only enter a Proper Name which means it must be entered using uppercase letters, numbers and only the following 4 special characters
,(comma)
-(dash)
& (Ampersand sign)
' (Apostrophe)
How would I do this? in a long statement specifying all the valid characters, or in a table that is 1 column with all the valid characters?
Hello!
Here is the article that may be helpful to you: Excel Data Validation using regular expressions.
I hope it’ll be helpful.
Hi!! I'm really dumb at such things like this, I'm in my 1st yr of college and we need to do data validation. C2:C7 contains the salary ( which the criteria, the amount must be 12, 000-35,000 ), and the next cell D2:D7 contains the bonus salary. The bonus salary must be 12% of the salary or cannot be greater than 12%. I know I need to make a custom formula but I'm really dumb at logic and maths, please help. Thank you and I apologize for my dumbness, have a great day <3
Hi!
The formula returns TRUE if the bonus salary does not exceed 12% of the basic salary:
=IF(D2/C2<0.12,TRUE,FALSE)
or
=D2/C2<0.12
Please check out this article to learn how to create conditions with IF function in Excel.
Hi,
How to restrict any profile in validation rule formula, that means I don't need to run that user in validation rule.. So any formula
Hi!
Standard Excel tools cannot help solve this problem.
In my drop-down list i have 3 options (1,2.3), from this when i have choose 1 and 3 i should receive error message. when choose 2 i don't want error message. can you help me.
One more item in the list can be validating field based on conditional values from other fields.
For example:
1. Value in Sheet2!B2 should be a date greater than or equal to the date found using vlookup of Sheet2!A2 on Range Sheet1!A:D for D column, and less than similar vlookup on another range.
2. Value in B2 should be some percentage of sum of value from some other range.
3. Value in B2 should be either from the list from some other range, or should be from a list from some other range 2.
4. Value in B3 should be in (the list/range + B2), and so on... That is, adding previous value to the list in the data validation.
These kind of validations are more common in our day to day work. Simple list validations or date validations are very simple to use.
Hi Alexander, thanks for your answer.
I have done the regular drop-down list.
Now, what I need is to be able to select data from the list in cell A1 and get it to show me "blue", whilst on cell B1, I select the same item on the same list but I get it to show me "1".
Do you understand what I mean?
Thanks for your help.
Hi,
If you use one list for drop-down lists, then your task is impossible.
Hi there,
I have a cell with a data validation list dropdown. I want to make a rule to get the cell to show something different when a particular value of the list is selected. Is this possible?
For example, let's say I have a list from 1 to 10. I want the list to show all the numbers normally, but I want the 1 to show "Improvement needed!" and the 10 to show "Awesome!", however, I need the list to still be 1 to 10, as I would need to do other operations with the data of that list.
Thanks for your help.
Hello!
A regular drop-down list inserts the data it shows into the cell.
An ActiveX ComboBox (Developer - Insert menu tab) may be suitable for your task.
I want the cell to except only Capital M. How would I that?
Thanks
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
Formula =A1="Capital M"
Can I restrict the entry to one decimal point? The problem is even if I set the format of cell as number with one decimal point, when I enter 0.12, the value is still stored as 0.12 despite the number show in the cell is 0.1.
Hello!
Use this formula for Data Validation:
=A1=ROUND(A1,1)
Hope this is what you need.
thanks a lot
Column D has data validation rule to select from a drop down list.
I wish column E to automatically fill based on a custom rule:
=VLOOKUP(D2,Suppliers!A:B,2,FALSE) /* No errors when setting up the custom rule.*/
But when i select a value from the drop down list for cell D2, nothing fills in cell E2.
Thanks for your feedback.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. Please check out the following article on our blog, it’ll be sure to help you with your task: Excel VLOOKUP not working.
If there is anything else I can help you with, please let me know.
Hello,
This is the dropdown list worksheet called Suppliers:
----------------------------------------------------------------------------
Allen & Sons Ltd Water
Beacham & Sons Ltd Electricity
Chisholm & Sons Ltd Medical Equipment
Davidson & Sons Ltd Building Maintenance
Elias & Sons Ltd Computer Equipment
Fanshaw & Sons Ltd Window Cleaning
George & Sons Ltd Gardening
This is the data entry worksheet:
-----------------------------------------------
Supplier (col D) Supplies (col E)
Allen & Sons Ltd
Beacham & Sons Ltd
Chisholm & Sons Ltd
Davidson & Sons Ltd
Elias & Sons Ltd
When I select Allen & Sons Ltd in the Supplier column D, I expected column E to fill automatically with the word Water because of the custom data validation formula, but it remains blank.
=VLOOKUP(D2,Suppliers!A:B,2,FALSE)
I know I can make cell E do a VLOOKUP, but I was testing to see if the VLOOKUP function could be incorporated as part of a custom data validation formula.
This is not a problem, it's just a technical challenge.
Thank you.
Hello!
Dropdown list in D1 cannot insert value in E1. In cell E1, you can write down the VLOOKUP formula. Once you select a value in D1, it will find the value you want.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi
Using data validation, I know it is possible to do the below separately:
1. Create a data validation drop down list (ALT + A + V + V then select list from the 'Allow' field and denote the cell range in the 'Source' field)
2. Prevent users from entering data into one cell (e.g. cell A2) if another cell (e.g. cell A1) has not yet been populated (ALT + A + V + V then select Custom from the 'Allow' field and insert '=NOT(ISBLANK(A1))' in the 'Formula' field)
However, I am in need of a combination of both. I need to customise a cell such that if cell A1 is not populated, then cell A2 cannot be populated but, when cell A1 has data inserted, A2 should be able to be populated but only through a drop-down list. May you assist
Using data validation, I know it is possible to do the below separately:
...
2. Prevent users from entering data into one cell (e.g. cell A2) if another cell (e.g. cell A1) has not yet been populated (ALT + A + V + V then select Custom from the 'Allow' field and insert '=NOT(ISBLANK(A1))' in the 'Formula' field)
...
How do you know that?? It's NOT working!
...Does anyone know if it's possible to use as a condition in formula of Data Validation empty cells??
Hello!
Use the following formula for data validation
=NOT(ISBLANK(A1))
Uncheck "Ignore blank". It works.
Does data validation does not read result in formula result?
Hi,
Just wondering if you can help me with something. I'm trying to create a variable list dependent on a variable value. E.g. If cell equals a number then give me list of job titles relating to that number.
I say its variable because the number depends on which department is selected. The job titles are on a separate worksheet within the workbook. The list is variable because job titles can be added or removed to the list so I can't label specific ranges.
Essentially I want to do a vlookup but the result being a list rather than a single value... is this possible and if so please let me know how to do this!
First row of my spreadsheet is the column titles.
In row 2 I have four Data Validation drop downs, each column drop down is dependent upon whichever item is picked in the drop down in a previous cell in the same row, but not necessarily adjacent columns. All is working correctly in row 2
The spreadsheet has 500 data entry rows to allow recording of approx. 6 to 10 jobs per day. How do I extend the Data Validation List rules to all 500 rows?
I have used Copy/Paste Special/Validation on each column but this is not working correctly. Is the copy/paste method I am using the wrong one for this application?
Hello!
The information you provided is not enough to understand your case and give you any advice.
What data validation formula are you using? Specify in more detail what problem occurs in this case.
Above issue resolved. Was a syntax error in one of the formulae where $C$4 was locking drop-down lists to a single cell.
Hi,
We have a spreadsheet that currently monitors the jobs our contractors are at, and the total hours worked overall for the week. We have the list of employees on the one sheet, and we use the Data Validation drop down to select the jobs for the hours, and I was wondering if there is a formula to also calculate the number of hours based on each specific job, as well as the overall total hours I have listed.
Thanks.
Hello!
I can't see your details. But to calculate the sum of hours by condition, you can use the SUMIF function.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I want to know formula to list down the data validation list when x collum value is Yes else it shoud show not applicable. please help
Hello!
Explain what the phrase "list when x collum value is Yes" means? Is each cell in the column "Yes"?
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.
Hi, I have a requirement to allow multiple set of numbers in a cell, for example, (00001, 02334, 01289), retaining leading 0s and limit to 5 digits each number, how can I write a formula for this? can someone help me, thanks
Hello!
If I understand your task correctly,
Set the cell to "Text" format and use Data Validation - Allow - Text length
If you want to use numbers, set the custom format "00000" in the cell and use the Data Validation formula
=A1<1000000
I hope it’ll be helpful.
Hi, thanks for getting back on this, the formula and the steps mentioned works for one set of number given on that cell, for example, 00012. My requirement was to enter multiple set of numbers, for example (00012, 11111, 12359, 88888, etc...) which does not work.
Hello!
I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case. However, even now your question is not precise enough. 00012, 11111, 12359, 88888 written as text in one cell? If so, why are you talking about numbers and leading 0s?
=AND(COUNTIF(Root_Rank,O6)0,O6<=3)
Why might a logically TRUE formula trigger a FALSE in Custom Validation.
=AND(COUNTIF(Root_Rank,O6)0,O6<=3) is TRUE when O6 is 1, 2, or 3 but fails validation.
Sorry formula truncated. =AND(COUNTIF(Root_Rank,O60, O6<=3)
I have written a custom public function which returns a true or false value, and I have tested it in a cell and it works. But when I use the function in the formula field on the Data Validation Criteria pop up, I get this error: "A named range you specified cannot be found" The only range in my formula is a cell reference. Any help would be appreciated
Unfortunately, without seeing your data it hard to give you advice. For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.
Hi team,
For some reason this validation rule is not triggering the stop alert in cell E9 when I enetr "X". $R$6=3 and I have entered "X" in three other worksheets in cell "E9". I wonder if you can help-
=SUMPRODUCT(COUNTIF(INDIRECT('Sheet names'!$E$2:$E$7&"!"&CELL("address",E9)),"*"&"X"&"*"))>$R$6
Hello!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail.Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
I figured it out. Just realized that the alert is triggered by "False" argument, not "true" argument. I just had to change the formula to "".
Hi,
I'm having difficulty with a custom Data Validation formula. I want it to check two nearby cells for existing values of 1; both these cells must not contain 1 (so if either does contain 1, the validation fails).
I've tried creating a macro but no matter what it does, it always fails to validate. I then tried entering a formula which works when tested in the sheet, but it always fails to validate when entered as a formula in Data Validation. Any clues please?
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4))1,INDIRECT(ADDRESS(ROW(),COLUMN()+3,4))1)
Obviously the system here doesn't like my greater-than/lesser-than symbols as it's stripped them out of my formula!! Here it is again but without using those symbols:-
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()+1,4))not equal to 1,INDIRECT(ADDRESS(ROW(),COLUMN()+3,4))not equal to 1)
Hello Teresa!
In the conditional formatting and data validation formula, the INDIRECT function does not work with the logical functions IF, AND, OR, and others. Therefore, I recommend that you divide your formula into 2 parts and create 2 conditions.
=INDIRECT(ADDRESS(ROW(),COLUMN()-1,4))<>1
and
=INDIRECT(ADDRESS(ROW(),COLUMN()+1,4))<>1
I hope this will help
Hello Dear Sir,
How can I do for folling,
"Data validation allows copy paste if it is validated number.If not, data validation reject this data."
How to restrict equal to sign (=) in excel cell
How do i lock a validation rule but can enter data in the cell
Hello!
It is not possible to block a data validation rule. It can only be deleted. More sophisticated data validation methods are possible using the VBA macro.
Dear Alexander,
Thank you for engaging to this thread once again. Despite the news are not what I was hopping for, still, excel carrying on the end result is something I kinda expected to tell you the truth.
If it wasn't necessary to have an alternative zero value in every cell, it would be much easier. I might write all the formulas I need manually, and create separate data validation for every cell. It might take me some time, but it'll totally worth the effort.
Thank you for your assistance. You're all great in Ablebits at what you're doing. Don't stop giving out essential and helpful hints!
Hello Ablebits team,
I'm having a hard time to figure out how to make options of a drop-down list, containing formulas, working with relative reference when I paste special with validation in another cell, without using VBA if possible.
For example, when I copy validation from cell P31, including a list option with the formula "=MIN($D31;$K31+L31)" and paste it to cell P33, I need this formula to change automatically into "=MIN($D33;$K33+L33)".
Could that be possible in anyway? Thank you in advance!
Hello Yannis!
When you copy cells, data validation formulas change in the same way as regular formulas. You can check it yourself.
Hello Alexander and thank you for your quick response,
I've tried it before and it seems not to be working, since the result I get by selecting this list component, is the same for every cell I paste the validation into.
Let me clarify that it's a list containing the formula (including the value zero and the formula) and not a custom validation, where I could write the formula directly into the box while creating it, in case this detail is of any importance.
Is there any worksheet setting I should turn on or off?
Yannis,
Thank you for the clarification. I mush have misunderstood your previous comment. I supposed you're using a custom validation formula applied to some data range and this formula is the one that doesn't get updated. If however, it is the range with different formulas you've set as the data validation source range, those formulas won't be recalculated when copying. You see, Excel uses not the formula but the end result for the validation, no matter where this validation list is.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi,
I am working on the excel to validate specific texts cannot be entered into the cell. Example, when user keys in Hello or HELLO or bye or NA, the cell will show specific text entered is not allowed. If user keys other text, it will be allowed to be entered into the cell. Appreciate your help to list out the formula to resolve this probllem. Thanks
Column B Defines the scope of the product with two variables Yes or No e.g. Cell B1 = Yes/No
Column C - Cell C1 has data validation of three parameters Simple/Medium/Complex.
If the value of Cell B1 is Yes for the Scope; then only Cell C1 should get activated and the user should be able to choose the type of the parameter by clicking the drop-down arrow i.e. Simple/Medium/Complex.
Please advise the formula or procedure to do the same.
Best Regards
Hello Praveen!
I recommend reading these articles on drop-down lists in Excel and how to make a dependent drop-down list: https://www.ablebits.com/office-addins-blog/dependent-cascading-dropdown-lists-excel/
Hope this is what you need.
Dear All I am from India, please help me I got fade of to make this 'custom data validation isuue solved. I made a table having multiple column.
Column 'C' contains 'Items in innitial Stock', 'D' contains 'No. Of Used items', and 'E'containes 'Faulty items'.
Now three isuue are that (1) 'E' cant be greater than either the 'C'or 'C-D' and (2) if Stock 'C' is empty/blank no Entry should be allowed in 'E'.
And (3) if C is having 20 items and no item is used (it means 'D' is balnk), but some items foud faulty, so 'E' must not be greater than 'C'. And it must generate erro message if these condition violated.
I tried more than 50 Formula but it satisfied only 2 conditions.
plase help to make this formula.
Hello Santosh,
Please try the following formula for data validation for cell E3:
=IF(AND((C3-D3)>=E3,C3>0,C3>=E3), TRUE, FALSE)
You can learn more about Data Validation in Excel in this article on our blog:
https://www.ablebits.com/office-addins-blog/data-validation-excel/#custom-data-validation-rules
https://www.ablebits.com/office-addins-blog/excel-data-validation-custom-formula/
Hope you’ll find this information helpful.
Hello,
I am working on excel and for the specific columns i need to restrict the data-entry of " ."(space and dot, consecutively). For such validation which command should i use.
Help.
I'm trying to set up a validation rule to connect the value in one column to an appropriate valid value list in another column. For example, I have a validated cell (let's say B2) where the value is validate via a list on Sheet 2 in cells A1-A3. THe valid values in the list include three names: Paul (A1), Peter (A2) and Pamela (A3). Now, on sheet 2 I have a list of Paul's prefered toys in Cells C1-C10, Peter's list is in D1-D10 and Pamela's are in E1-E10. Now, back on Sheet 1 I want to create a validation formula in Cell B3 that says...... Refer to cell B2, if the value in the cell is the value in Sheet 2, Cell A1 then select from the list in Sheet 2, between cells C1-C10. If it's A2, then select from the list in sheet 2 cells D1-D10, and so forth. Is this possible? Another way to look at this is, I want to limit choice number 2 based on choice number 1. If I'm making a selection for Pauls' Christmas present, I only want to see the things on Pauls' Christmas list. Thanks for any help you can provide.
Hello Teri!
You should first select a particular name, and then get the list of toys based on this name. It can be easily done. For more details on how to solve this task, please have a look at this article: https://www.ablebits.com/office-addins-blog/dependent-cascading-dropdown-lists-excel/#cascading-dropdown-multiword
If you still have any questions, I will be glad to answer them.
Please Help me someone. How can I use two data validation in a column? I want only 9 digits to be entered in a cell and if it's more then 9 or less then 9 it should not accepted. and also this number should be uniqe in the column