Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a drop down list with Yes/No values for a column and the next column over there needs to be a comment if no, but I want to make it so they must add a comment before moving to the next row.
Solved by Z. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 31/05/2018 - 02:07
User 31/05/2018 - 02:07
Hi Domingo. I have a slight problem with my excel file I'm hoping you have an answer to
Excelchat Expert 31/05/2018 - 02:07
let's see what can I do for you :)
User 31/05/2018 - 02:08
I have two columns with Yes or No choices in drop down lists. The third column is where comments need to be added if the answer in column 2 is No. I want it so that they must enter a comment before moving on
Excelchat Expert 31/05/2018 - 02:09
I've just replicated your description. Am I correct?
Excelchat Expert 31/05/2018 - 02:10
or have I missed something?
User 31/05/2018 - 02:10
there is also a column (the very first one) pertaining to the speicfic report I am writing Yes/No for
User 31/05/2018 - 02:10
but i dont know that it matters
User 31/05/2018 - 02:11
mainly just need to figureo ut the formula to make it so comments are required for "No" in column 2 there before moving on
User 31/05/2018 - 02:11
I have tried data validation, but could not get it to work
Excelchat Expert 31/05/2018 - 02:11
Ok
Excelchat Expert 31/05/2018 - 02:12
so you fill this table row by row, correct?
Excelchat Expert 31/05/2018 - 02:12
and Only if Dropdown2 is No, a comment should be added
User 31/05/2018 - 02:13
Yes, that is correct. Dropdown 1 will more than likely always be Yes, but No is an option in the evnet it is not
Excelchat Expert 31/05/2018 - 02:13
Ok
User 31/05/2018 - 02:13
If dropdown 2 is No then a comment is added in the next column over on the same row
Excelchat Expert 31/05/2018 - 02:14
is it a custom or standard comment?
User 31/05/2018 - 02:14
Custom, it's user input based on why they chose No
User 31/05/2018 - 02:17
So for example, in the line you have now, if drop down 2 is No as it is there in row 2 I need a message to pop up requiring a comment as to why before they can move on to row 3
Excelchat Expert 31/05/2018 - 02:17
Understood
Excelchat Expert 31/05/2018 - 02:17
Could you please give me a brief moment to validate my solution?
User 31/05/2018 - 02:18
Yes, by all means
Excelchat Expert 31/05/2018 - 02:22
I'm still with you
Excelchat Expert 31/05/2018 - 02:22
almos done
Excelchat Expert 31/05/2018 - 02:22
almost*
User 31/05/2018 - 02:22
Okay great
Excelchat Expert 31/05/2018 - 02:25
Ok
Excelchat Expert 31/05/2018 - 02:25
just to confirm, are you working with excel or a google sheet?
User 31/05/2018 - 02:26
Excel
Excelchat Expert 31/05/2018 - 02:26
Ok
Excelchat Expert 31/05/2018 - 02:27
I have one alternative as my thought solution is not working right now. But I can keep trying a little more if you want.
User 31/05/2018 - 02:27
Excelchat Expert 31/05/2018 - 02:27
Great
Excelchat Expert 31/05/2018 - 02:27
Thanks for the patience
User 31/05/2018 - 02:27
I have been able to get it to show "Please add a comment!" into the comment line and a message popping up, but it only works when Yes is selected
User 31/05/2018 - 02:27
NO problem
User 31/05/2018 - 02:28
If it helps my formula is:
User 31/05/2018 - 02:28
User 31/05/2018 - 02:28
With an error message as well
User 31/05/2018 - 02:29
but it only works for yes, when I change to no it jsut stays there with no message popping up. it need to pop up when changed form yes to no
Excelchat Expert 31/05/2018 - 02:30
This is very similar to my alternative, you can try flipping the condition=IF(D4="Yes","","Please add acomment!")
User 31/05/2018 - 02:31
That made it so that it shows up when changed to no, but no error message shows up
Excelchat Expert 31/05/2018 - 02:32
that's right.
Excelchat Expert 31/05/2018 - 02:32
at case you can try this:
Excelchat Expert 31/05/2018 - 02:32
Excelchat Expert 31/05/2018 - 02:32
alright
User 31/05/2018 - 02:33
Let me try
Excelchat Expert 31/05/2018 - 02:33
I finally made my solution work
User 31/05/2018 - 02:34
okay, can we start fresh and show me on here? changing the condition again and trying tio duplicate it on a line messed it up
Excelchat Expert 31/05/2018 - 02:34
definitively!
Excelchat Expert 31/05/2018 - 02:35
ok. so what you need to do is the following
Excelchat Expert 31/05/2018 - 02:35
Select the range you want to apply the data validation
Excelchat Expert 31/05/2018 - 02:35
I've set to light blue
Excelchat Expert 31/05/2018 - 02:35
the first row is not necessary
Excelchat Expert 31/05/2018 - 02:36
Now, you have to create a custom formula data validation and insert the following formula:
Excelchat Expert 31/05/2018 - 02:36
"=NOT(AND(C2=2,ISBLANK(D2)))"
Excelchat Expert 31/05/2018 - 02:37
sorry, should be this:
Excelchat Expert 31/05/2018 - 02:37
"=NOT(AND(\$C2=2,ISBLANK(\$D2)))"
Excelchat Expert 31/05/2018 - 02:37
the \$ symbols
Excelchat Expert 31/05/2018 - 02:37
don't forget :)
Excelchat Expert 31/05/2018 - 02:38
I've just applied the data validation
Excelchat Expert 31/05/2018 - 02:39
I'm sorry, this should be the formula:
Excelchat Expert 31/05/2018 - 02:39
=NOT(AND(\$C2="No",ISBLANK(\$D2)))
Excelchat Expert 31/05/2018 - 02:39
was testing with a "2" :)
User 31/05/2018 - 02:40
i applied it to my sheet and nothing happened
User 31/05/2018 - 02:40
There is also a fucntion that highligths the comment line if blank right now, could this be messing it up?
Excelchat Expert 31/05/2018 - 02:41
it shouldn't.
Excelchat Expert 31/05/2018 - 02:41
did you changed the \$C2 and \$D2 with your respective cells in your sheet?
User 31/05/2018 - 02:41
that formula does nothing to my sheet even if I try with jsuto ne line
User 31/05/2018 - 02:42
my cells are different in this excel
Excelchat Expert 31/05/2018 - 02:42
User 31/05/2018 - 02:43
here hold on
User 31/05/2018 - 02:43
Excelchat Expert 31/05/2018 - 02:43
ok
User 31/05/2018 - 02:44
dont think I can sicne company document
Excelchat Expert 31/05/2018 - 02:44
its ok
Excelchat Expert 31/05/2018 - 02:44
which column letter is your second dropdown?
User 31/05/2018 - 02:45
here we go
User 31/05/2018 - 02:45
this is what mine looks like
Excelchat Expert 31/05/2018 - 02:47
Ok. Here I can see that Column A is working as expected
User 31/05/2018 - 02:49
Column A isnt important, jsut there as reference, I dont need ot change those values
Excelchat Expert 31/05/2018 - 02:50
which cells do you want to block if the previous is not correct
User 31/05/2018 - 02:51
Now it looks identical to mine.
User 31/05/2018 - 02:51
When any values in column D chagne form yes to no, then a commnetm ust be added to column E
User 31/05/2018 - 02:51
sorry, typing too fast, typos
User 31/05/2018 - 02:52
But column E needs to have the message saying "Please add a comment!" and an error message to notify them to
User 31/05/2018 - 02:52
when changed form yes to no in D
Excelchat Expert 31/05/2018 - 02:53
ok.
Excelchat Expert 31/05/2018 - 02:53
do people modify column D to choose "No" or they pick "No" when filling a new row?
User 31/05/2018 - 02:54
By default they all say Yes, but if it needs to be changed in C or D they can
User 31/05/2018 - 02:54
but only D matters for column E, meaning the comment needs to reflect why they changed from yes to no
User 31/05/2018 - 02:55
still a custom somment and the same issue
User 31/05/2018 - 02:55
comment*
Excelchat Expert 31/05/2018 - 02:55
Got it. in that case, we can change the validation to be on Column E specifying that if Colum D = NO then Column E mustn't be empty
Excelchat Expert 31/05/2018 - 02:56
it will not trigger a warning but will add a red corner to the cell in column E.
User 31/05/2018 - 02:56
That would be perfect, as long as they are notified to add a comment to E when D changed to no then its good
User 31/05/2018 - 02:56
is this the same formula as posted by you above?
Excelchat Expert 31/05/2018 - 02:57
User 31/05/2018 - 02:58
okay
Excelchat Expert 31/05/2018 - 02:58
or
Excelchat Expert 31/05/2018 - 02:58
a better alternative
Excelchat Expert 31/05/2018 - 02:59
dd a conditional formatting con the comment IF Accuracy = No
Excelchat Expert 31/05/2018 - 02:59
what do you think?
User 31/05/2018 - 02:59
Im not sure what you mean
User 31/05/2018 - 02:59
I tohught about condition formatting but ive never used it
Excelchat Expert 31/05/2018 - 03:00
I will show
User 31/05/2018 - 03:00
Ok
User 31/05/2018 - 03:04
Need some time?
Excelchat Expert 31/05/2018 - 03:04
Done
Excelchat Expert 31/05/2018 - 03:04
rry for dissapearing
User 31/05/2018 - 03:05
That's okay
Excelchat Expert 31/05/2018 - 03:05
you have to pick Format, Conditional Formatting and Custom Formula
Excelchat Expert 31/05/2018 - 03:05
en you enter this:
Excelchat Expert 31/05/2018 - 03:05
=AND(\$D1="No",ISBLANK(\$E1))
Excelchat Expert 31/05/2018 - 03:06
you have to specify the format that you want

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.