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
Hello! My name is Domingo. How can I help you today?
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
Yes please
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
=IF(D4="No","","Please add acomment!")
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
=IF(D4<>"No","","Please add acomment!")
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
wHich are your cells?
User
31/05/2018 - 02:43
here hold on
User
31/05/2018 - 02:43
adding file for you
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
similar, I'm adjusting it now
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.
