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.