Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-26-2018, 01:41 AM
mohsen.amiri mohsen.amiri is offline Windows 8 Office 2013
Competent Performer
 
Join Date: Aug 2014
Posts: 202
mohsen.amiri is an unknown quantity at this point
Default Question of data Validation

Hello
Have a look at the file attached. I used 'Data Validation' to force cell B1 to accept just numbers 1 or 2 (if A1 be equal to the word "Iamgood").


If the word "Iamgood" (in cell A1) changes to "I am good", then it does not work. It seems spaced between characters make problem for it?
Let me know your ideas.
Thanks a lot.
Attached Files
File Type: xlsx Data Validation.xlsx (8.6 KB, 2 views)
Reply With Quote
  #2  
Old 12-26-2018, 02:22 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 942
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

You have defined (as defined name) iamgood, referring to E1:E2. And in your Data Validation you refer indirectly to iamgood (in A1) so of course if you change A1 to I am good then =INDIRECT($A$1) returns an error (#REF!).
Reply With Quote
  #3  
Old 12-26-2018, 02:42 AM
mohsen.amiri mohsen.amiri is offline Windows 8 Office 2013
Competent Performer
 
Join Date: Aug 2014
Posts: 202
mohsen.amiri is an unknown quantity at this point
Default

xor
The problem is Data Validation does not accept Spaces between words as defined name and the error appears. Have a look at photos below and open the file again and try to change the defined name to "I am good" and see what happens.
Attached Images
File Type: jpg 1.jpg (192.6 KB, 5 views)
File Type: jpg 2.jpg (203.7 KB, 5 views)
Reply With Quote
  #4  
Old 12-26-2018, 02:59 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 942
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Then what about using

=INDIRECT(SUBSTITUTE($A$1," ",""))

in Data Validation?
Reply With Quote
  #5  
Old 12-26-2018, 04:05 AM
mohsen.amiri mohsen.amiri is offline Windows 8 Office 2013
Competent Performer
 
Join Date: Aug 2014
Posts: 202
mohsen.amiri is an unknown quantity at this point
Default

It done. Thank you very much.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation - List Webbn111 Excel 9 03-09-2016 04:30 PM
Paste over data validation saurabhlotankar Excel Programming 2 05-24-2015 11:52 PM
data validation from another sheet paulys Excel 1 06-30-2014 01:40 AM
Data Validation drop down rkalapura Excel 1 05-27-2012 02:23 AM
Data validation with IF Klyxrastafari Excel 1 10-03-2011 04:41 PM


All times are GMT -7. The time now is 06:19 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft