**Question description:**

*This user has given permission to use the problem statement for this blog.*

HI - I have a dropdown list that contains a set of options, one of which is None. If one of my cells = greater than 0 "none" should not be a valid option .. . .how could I accomplish this?

Solved by T. S. in 46 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
02/08/2018 - 06:29

Hi

Excelchat Expert
02/08/2018 - 06:29

Welcome to Got It Pro.

User
02/08/2018 - 06:29

hi there

Excelchat Expert
02/08/2018 - 06:29

Please add sample data in preview.

User
02/08/2018 - 06:30

I have a picklist of names, and one of the selections is none

User
02/08/2018 - 06:31

I a certain cell is greater than 0, "None" CANNOT be selected

User
02/08/2018 - 06:31

how can I accomplish this?

Excelchat Expert
02/08/2018 - 06:31

What is the cell to check?

Excelchat Expert
02/08/2018 - 06:31

I can't see any value in the cells.

User
02/08/2018 - 06:32

I didnt recreate the list in the cells

Excelchat Expert
02/08/2018 - 06:32

Ok. If column D is zero, None should not appear in the list?

User
02/08/2018 - 06:32

so IF Cell D 8 is greater than 0, Cell C8 cannot be None

User
02/08/2018 - 06:33

but C8 should be a picklist with other values than Noone

Excelchat Expert
02/08/2018 - 06:33

Ok.

User
02/08/2018 - 06:33

Not even appear... just not be able to be selected

User
02/08/2018 - 06:33

the defaults are None and 0

Excelchat Expert
02/08/2018 - 06:33

Sure. I got it.

User
02/08/2018 - 06:40

but that cell would be a dropdown

Excelchat Expert
02/08/2018 - 06:41

Hi, I am working on it.

Excelchat Expert
02/08/2018 - 06:41

Please give me few more minutes.

User
02/08/2018 - 06:41

can you explain how to do it? since i need to do it in my spreadsheet?

Excelchat Expert
02/08/2018 - 06:42

.

[Uploaded an Excel file]

Excelchat Expert
02/08/2018 - 06:42

Please refer the file. I have used formula to do it.

Excelchat Expert
02/08/2018 - 06:43

=IF($D8=0,$H$4:$H$5,$H$3:$H$5)

User
02/08/2018 - 06:46

I dont see any formulas in that file

Excelchat Expert
02/08/2018 - 06:46

It is avaiable in the datavalidation.

Excelchat Expert
02/08/2018 - 06:46

Please select the cell and click on Datavalidation under Data menu.

User
02/08/2018 - 06:46

ok give me one minutes

Excelchat Expert
02/08/2018 - 06:53

Hi,there?

User
02/08/2018 - 06:54

hi

Excelchat Expert
02/08/2018 - 06:54

Did you checked the file?

User
02/08/2018 - 06:54

i see the validation

User
02/08/2018 - 06:55

but when the cell is 32

User
02/08/2018 - 06:55

nothing pops up

User
02/08/2018 - 06:55

also the cell is already validated bevcause of the list

Excelchat Expert
02/08/2018 - 06:55

I have applied it till row 19

Excelchat Expert
02/08/2018 - 06:55

Please extend the range, based on the requirement.

User
02/08/2018 - 06:57

this dosent work

Excelchat Expert
02/08/2018 - 06:57

What is the problem you are facing?

User
02/08/2018 - 06:58

yea

User
02/08/2018 - 06:59

its letting me inout any number

User
02/08/2018 - 06:59

and it stillis NOne\

Excelchat Expert
02/08/2018 - 06:59

The formula is applied for the range provided.

Excelchat Expert
02/08/2018 - 06:59

I have applied a condition, when the adjacent cell value =0, It does not include None. Else, It includes None.

Excelchat Expert
02/08/2018 - 07:00

Please let me know in the preview with specific example.

Excelchat Expert
02/08/2018 - 07:01

=IF($D8=0,$H$4:$H$5,$H$3:$H$5)

Excelchat Expert
02/08/2018 - 07:01

In this formula, Please check the range, where the data validation is present.

Excelchat Expert
02/08/2018 - 07:02

If your range is different, then, you have to adjust accordingly. Based on the example provided, i have applied the range.

User
02/08/2018 - 07:02

So in the example in the right

User
02/08/2018 - 07:02

where would I put the data validation ?

User
02/08/2018 - 07:03

IF C5 = 0 then None should not be selected

User
02/08/2018 - 07:03

or greater than 0

User
02/08/2018 - 07:03

sorry

User
02/08/2018 - 07:03

B5 6 and 7 are drop downs

Excelchat Expert
02/08/2018 - 07:03

C5 is Data1.

Excelchat Expert
02/08/2018 - 07:03

Please refer the values in column F.

Excelchat Expert
02/08/2018 - 07:03

When F4=0, it adds only Data 1,2,3.

User
02/08/2018 - 07:04

????

User
02/08/2018 - 07:04

Youre not getting it

Excelchat Expert
02/08/2018 - 07:04

Otherwise, it add None.

User
02/08/2018 - 07:04

It shouldnt add anything

User
02/08/2018 - 07:04

?

Excelchat Expert
02/08/2018 - 07:04

Please open the file.

Excelchat Expert
02/08/2018 - 07:04

I will explain the concept.

Excelchat Expert
02/08/2018 - 07:05

Select C8 to C19 cells.

Excelchat Expert
02/08/2018 - 07:05

Click on Datavalidation in Data menu.

Excelchat Expert
02/08/2018 - 07:05

Under Allow, select List.

Excelchat Expert
02/08/2018 - 07:05

Right side, 2 checkboxes are there, please check them.

Excelchat Expert
02/08/2018 - 07:06

1)Ignore Blank and 2) In-cell dropdown.

Excelchat Expert
02/08/2018 - 07:06

In Source:

Excelchat Expert
02/08/2018 - 07:06

enter the following formula.

Excelchat Expert
02/08/2018 - 07:06

=IF($D8=0,$H$4:$H$5,$H$3:$H$5)

Excelchat Expert
02/08/2018 - 07:06

Click ok.

Excelchat Expert
02/08/2018 - 07:07

Now, you can see the list in the dropdown based on the number in D column .

Excelchat Expert
02/08/2018 - 07:07

The data validation is only applied to C8 to C19.

User
02/08/2018 - 07:07

no None is still ther

Excelchat Expert
02/08/2018 - 07:07

What cell you are referring?

User
02/08/2018 - 07:07

C9 thry C19

Excelchat Expert
02/08/2018 - 07:08

Please delete the entries first.

User
02/08/2018 - 07:08

like if Cell D8 is 10

User
02/08/2018 - 07:08

then None is still there

Excelchat Expert
02/08/2018 - 07:08

The list is updated in the dropdown.

User
02/08/2018 - 07:08

no its not

User
02/08/2018 - 07:08

im testing it now

User
02/08/2018 - 07:08

what does it go to then?

Excelchat Expert
02/08/2018 - 07:09

Ok. Got it. When it is greater than zero, None should not present?

User
02/08/2018 - 07:09

yes

User
02/08/2018 - 07:09

or some kind of error message

Excelchat Expert
02/08/2018 - 07:09

=IF($D8>0,$H$4:$H$5,$H$3:$H$5)

Excelchat Expert
02/08/2018 - 07:09

Please use this formula.

User
02/08/2018 - 07:09

ok let me try

Excelchat Expert
02/08/2018 - 07:09

I will update and send you the file again.

Excelchat Expert
02/08/2018 - 07:09

I did reverse earlier.

User
02/08/2018 - 07:10

this still wont work

User
02/08/2018 - 07:10

because the default is None

Excelchat Expert
02/08/2018 - 07:10

.

[Uploaded an Excel file]

User
02/08/2018 - 07:10

and 0

User
02/08/2018 - 07:10

so when I change 0 to 1 it does not automatically update the NOne

User
02/08/2018 - 07:11

I would have to click into the cell

User
02/08/2018 - 07:11

and then None disappears

Excelchat Expert
02/08/2018 - 07:11

Yes.

Excelchat Expert
02/08/2018 - 07:11

If it has to change automatically, then you have to go for programming.

User
02/08/2018 - 07:11

essentially I need something to prevent None from happening when a certain Cell is = to zero

Excelchat Expert
02/08/2018 - 07:11

It is not possible with formula alone.

Excelchat Expert
02/08/2018 - 07:12

With data validation, we can change the contents of the dropdown.

Excelchat Expert
02/08/2018 - 07:12

And you have to change it manually.

Excelchat Expert
02/08/2018 - 07:13

If you want to get automatically, None, when you enter 1 in D column. then you have to go for programming, where programs places None text, when the value is >1.

User
02/08/2018 - 07:14

ok thanks for your help

User
02/08/2018 - 07:14

have a good day

Excelchat Expert
02/08/2018 - 07:14

Hope you got the idea.

Excelchat Expert
02/08/2018 - 07:15

The formula can control the list in the dropdown.

Excelchat Expert
02/08/2018 - 07:15

It can't control the cell content. As, cell content can be anything.

Excelchat Expert
02/08/2018 - 07:15

Thank you.

Excelchat Expert
02/08/2018 - 07:15

Have a great day ahead.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*