**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.*