• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Data validation does not work|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Data validation does not work|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Data validation does not work
sp_PrintTopic sp_TopicIcon
Data validation does not work
Avatar
Annamaria Katona

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
1
October 17, 2018 - 9:41 pm
sp_Permalink sp_Print

Hi, I have created a data validation to stop entering certain unacceptable values. The formula has only 120 characters, yet the data validation does not work.

At a certain point it was working but hen i made a final correction it stopped doing so, and I have not idea why.

I tried also to recreate but it does not work.

Any suggestion what I am doing wrong?

I attach the file.

Thank you

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 132
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
2
October 18, 2018 - 12:39 pm
sp_Permalink sp_Print sp_EditHistory

Looked at your submission, but am unable to determine where the data validation is located.  You have many columns of data.  Please be more specific in your request for help.  Tell us specifically where your issue is and what is happening and what is not happening that you want to happen.

Avatar
Annamaria Katona

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
3
October 18, 2018 - 5:19 pm
sp_Permalink sp_Print

You can see at F5 Go to special --> Data validation. It is in the range AM8:BX4000

The formula I used is =IF(ISNUMBER(AM8);IF(AM8>=AM$4;TRUE;FALSE);IF(OR(AM8="x";AM8="xE";AM8="E";AM8="-";AM8=AM$3601;AM8="<"&AM$4);TRUE;FALSE))

if I put the formula in a cell it works, so there is not a problem with the formula. In the dialog box of Data Validation there is no sign that something is wrong and not accepted, still it doesn't work.

If I check the data validation of any of the cells it is there, still it doesn't work, still it accept values I defined not to accept.

I don't find the problem.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
October 19, 2018 - 2:06 am
sp_Permalink sp_Print

Hi Annamaria

Try unticking the Ignore Blank option in your DV.

Sunny

Avatar
Janice Duffney

New Member
Members
Level 0
Forum Posts: 1
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
5
October 19, 2018 - 4:28 am
sp_Permalink sp_Print

Hi.

 

I put this into the data validation and it worked.

 

=(ISNUMBER(AM8)) * (AM8>AM$4)+((AM8="x")+(AM8="xE")+(AM8="E")+(AM8="-"))

 

The * is equivalent to AND and the + is equivalent to OR.  So the statement reads:

if AM8 is a number and it's > AM4 OR AM8 is one of x, xE, E, - then the value is ok.  I didn't get your last two criteria there - is AM$3601 supposed to be a number value?  There is nothing in that cell for me to determine that.  Also on your last criteria (AM8="<"&AM$4 - what are you trying to do there?  What is that ampersand for?

Avatar
Annamaria Katona

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
6
October 29, 2018 - 7:08 pm
sp_Permalink sp_Print

Hi,

Sorry for answering late, I had to deal with other things...

Thank you for all answers, both versions worked (unticking the ignore blank option or the formula proposed by Janice).

However I still have a question:

With my last condition AM8="<"&AM$4

I wanted to regulate that users can write that the value is less than the limit of quantitation of the method (the limit is given in row4) only in a specific format.

For ex if the limit is 0,2, it is only allowed to write <0,2 and not 0,20

It works, but it doesn't work in case of integers, if the limit is 5,0 (written so in row4), it accepts to be written <5 but I would need to create a condition that accepts <5,0.

Is that possible?

with AM$3601 the condition I is that sometimes there is a template formula in that row and users should be able to copy paste that formula in a given row above.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
October 30, 2018 - 10:47 am
sp_Permalink sp_Print

Hi Annamaria

You can try using CELL("format",X) where X is the cell to check the format.

The formula will return F1 (1 decimal place) and  F2 (2 decimal places) etc

You will need to format the cell X first to the required format.

Hope this helps.

Sunny

Avatar
Annamaria Katona

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
8
October 30, 2018 - 7:11 pm
sp_Permalink sp_Print

Hi Sunny,

Thank you for the tip, but unfortunately it doesn't work, because I need the condition for the "<5,0", and that is interpreted as text not as number.

But the 5,0 is variable, at every column it is different. That is why I used the AM8="<"&AM$4 formula, but this does not allow me to use one decimal in case it is an integral value...

I was thinking to use instead of AM$4 the formula fixed(AM$4,1), but it is not OK, for it doesn't match all my columns, in some cases I need two decimals or 3...

Annamaria

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
October 31, 2018 - 11:17 am
sp_Permalink sp_Print sp_EditHistory

Hi Annamaria

I think I misunderstood your original question as I thought you wanted to check on the format..

"I wanted to regulate that users can write that the value is less than the limit of quantitation of the method (the limit is given in row4) only in a specific format."

Are you saying that users can enter <5.0 (with the < symbol) into cell AM8 and it should be validated as TRUE if the value portion (5.0) is the same as AM4?

Even the decimal place must match i.e. if AM4 has 5.0 then user cannot enter <5.00 in AM8 but only <5.0?

The formatting part with decimal is very tricky. That is why I suggested using CELL() to check on the format of cell AM4 to determine the decimal placing.

I hope I got it correct this time. I will try and see what I can do.

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
October 31, 2018 - 12:52 pm
sp_Permalink sp_Print sp_EditHistory

Hi Annamaria

Try this. You can then incorporate this formula into your actual DV.

You will first need to format the rows 4 of your actual data to the correct decimal place for this to work.

If you change the cell format you will need to press F9 for the CELL function to recalculate.

Hope this helps.

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
11
October 31, 2018 - 1:20 pm
sp_Permalink sp_Print

What I am doing is to convert the values in row 4 to text so I need to determine the number of decimal place formatted in that cell.

I will then format using TEXT to the correct decimal place. So it is very important that you format the cells in row 4 to the required format.

You can use my example to check for any number of decimal place. It will show F0, F1, F2 etc.

Don't forget to press F9 to update the formula if you change the cell format.

If you have already predetermined the decimal place for each cell in row 4 then you don't need to calculate the decimal place. Just use TEXT with the correct decimal place format.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Jeanine Hagge
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31916
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.