Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc