Need help with Data Validation Formula for a set of numerical andtext-based values

[bozhidar.ivanov91]

Hello,

I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells. Do you have any idea how I can achieve this?

Thank you very much in advance!

Best regards,
Bobby

 

[Claus Busch]

Hi Bobby,

Am Wed, 30 Apr 2014 04:12:43 -0700 (PDT) schrieb
(e-mail address removed):

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells. Do you have any idea how I can achieve this?

write your values into a list:
Data => Data Validation => List:
1,0.5,A,H,0.5A,0.5H


Regards
Claus B.

 

[bozhidar.ivanov91]

Hi Bobby,



Am Wed, 30 Apr 2014 04:12:43 -0700 (PDT) schrieb

(e-mail address removed):







write your values into a list:

Data => Data Validation => List:

1,0.5,A,H,0.5A,0.5H





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Hello Claus,

Thank you very much for your prompt reply. I tried using this but it doesn't look quite neat. Do you know how this can be achieved with COUNTIF/ SUMPRODUCT instead?

Thanks!

 

[Claus Busch]

Hi,

Am Wed, 30 Apr 2014 04:30:00 -0700 (PDT) schrieb
(e-mail address removed):

I tried using this but it doesn't look quite neat

eventually you have to change the comma to your separator?

Or try Data => Data Validation => Custom:
=OR(A1=0.5,A1=1,A1="A",A1="H",A1=0.5&"A",A1=0.5&"H")


Regards
Claus B.

 

[bozhidar.ivanov91]

Or try Data => Data Validation => Custom:

=OR(A1=0.5,A1=1,A1="A",A1="H",A1=0.5&"A",A1=0.5&"H")

Hey, I tried this one, but it doesn't seem to be working. It still gives me the error message even if I input, 1 or 0.5. Is there any specific prerequisite for the A1 cell? Does it have to be empty or?

Thank you!

 

[Claus Busch]

Hi,

Am Wed, 30 Apr 2014 05:16:44 -0700 (PDT) schrieb
(e-mail address removed):

Hey, I tried this one, but it doesn't seem to be working. It still gives me the error message even if I input, 1 or 0.5. Is there any specific prerequisite for the A1 cell? Does it have to be empty or?

look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "DataValidation"
Rightclick and download the file because Data Validation doesn't work in
OneDrive


Regards
Claus B.

 

[bozhidar.ivanov91]

This is great! The custom part is exactly what I was looking for! Just one last thing, how would I apply these rules across the whole spreadsheet (or selected portions of it) instead of just the columns as you have given me? So for example how can I apply this rule to A5:K40 and then M15: HJ60? Or the whole spreadsheet (say A1K70)

Thank you very much again!

 

[Claus Busch]

Hi,

Am Wed, 30 Apr 2014 06:54:39 -0700 (PDT) schrieb
(e-mail address removed):

This is great! The custom part is exactly what I was looking for! Just one last thing, how would I apply these rules across the whole spreadsheet (or selected portions of it) instead of just the columns as you have given me? So for example how can I apply this rule to A5:K40 and then M15: HJ60? Or the whole spreadsheet (say A1K70)

select the range that should have this validation => Data => Data
Validation and write the formula that it refers to the active cell, the
lighter cell in your selection.


Regards
Claus B.