If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

**AdrianaBotha2** · 05-10-2023, 11:21 PM

Good day.

Please help me if you can.

I have a problem,

I have a table - columns A/B/C - Column B will have YES, NO or N/A - If NO then the row below must automatically get text "Complete Reason" - if reason is typed in in Column C then the "Complete Reason" must disappear.

That is the first part. Once that is done, I need a conditional formatting or setting that if the person did not select YES, NO or N/A then they cannot proceed completing any of the further YES/NO/N/A - This way trying to force them to complete the form properly.

Column B currently has Data validation list to select the YES/NO/N/A

If anybody can help me please. I have atttached a file and hope you can see it.

**FDibbins** · 05-10-2023, 11:43 PM

A cell can contain either a formula or an entry (text/numeric etc), it cannot contain both...and DV enters text into a cell. So what you want is not possible without VBA

There is nothing magical about a drop down (data validation), it acts in the exact same way as if you had entered the data manually, and excel formulas only see the data in the cell as if it had been entered manually.

The only real advantages of using a DD is that it saves typing, avoids typos/spelling mistakes, restricts entry to what you specify, and maybe saves a bit of time in entering data.
Other than that, no difference to manual entry

**AdrianaBotha2** · 05-10-2023, 11:52 PM

Thank you Ford, the cell will only contain a formula - in the sample spreadsheet i have the formula that if B6 is "No" the message pops up in C7, so the first part is working. This message tells the person to please complete the note or reason in B7 - so the rest of the formula is what I cannot get right. I am trying to get it that if C7's text is inputted that message that popped up in B7 to tell them to complete C7 must just disappear in otherwords the first part of the formula which is working is not relevant anymore. In my mind it is something like if B6 = No then B7=complete reason, but if B6 is no and c6 is not blank then that message to complete must not pop up. Hope this makes more sense?

**FDibbins** · 05-10-2023, 11:59 PM

OK see if this is what you want...

=IF(C7="",IF(B6="no","Complete Reason",""))

Note that you still have DV in B7, so you will need to adjust that to allow you to enter that formula

**FDibbins** · 05-11-2023, 12:00 AM

Also, pleas do not post duplicate threads, I have closed your other 1, you may continue on this 1

**AdrianaBotha2** · 05-11-2023, 12:06 AM

Thank you so much!!! This is working, but it is not going blank it is giving a "false" How do I get rid of the "false" please? Apologies about the second Post - I was not sure where to post.

**AdrianaBotha2** · 05-11-2023, 12:06 AM

Is there some way which I can do it in Conditional Formatting that it is not a visible formula which somebody could accidently delete?

**FDibbins** · 05-11-2023, 12:15 AM

OK so I understand properly...

1. we are talking about col B, correct? And in particular, B7

2. If A6 = Yes or NA, B7 = ""

3. If A6 = No, B7 = Complete

4. But if A6 = NO and C7 contains anything, B7 = "" (shows nothing)

Am I understanding you correctly?

The best way to prevent accidental deleting is to protect the cell/s

**AdrianaBotha2** · 05-11-2023, 12:21 AM

OK so I understand properly...

1. we are talking about col B, correct? And in particular, B7

2. If A6 = Yes or NA, B7 = ""

3. If A6 = No, B7 = Complete

4. But if A6 = NO and C7 contains anything, B7 = "" (shows nothing)................ this one should be C6 and not C7

Am I understanding you correctly?

The best way to prevent accidental deleting is to protect the cell/s THANK YOU

SO THE FORMULA I HAVE IS =IF(C6="",IF(B6="no","Complete Reason","")) IT IS WORKING PERFECTLY JUST RETURNING "FALSE" WHICH I WOULD THE TO HAVE JUST A BLANK CELL AND NOT "FALSE"

**FDibbins** · 05-11-2023, 12:26 AM

It seems to be working for me...
Excel 2016 (Windows) 64 bit

	B	C
6	No
7	FALSE	xxx
8	Complete Reason

Sheet: Client Care

what have you entered/not entered, and where, that you get FALSE?

**AdrianaBotha2** · 05-11-2023, 12:30 AM

I hope you can see this?

Attachment 828874

**FDibbins** · 05-11-2023, 12:37 AM

ahh OK so you want B7 to show nothing is C6 has text?

I have...
B6 = No
C6 = "some text"
B7 = ""
C7 = ""

B7 now =IF(C6="",IF(B6="no","Complete Reason",""))

**AdrianaBotha2** · 05-11-2023, 12:42 AM

Hi - Yes, you have it perfectly. but if I add that formula exactly - the 'COMPLETE REASON' pops up, but if I then input a reason - the the 'COMPLETE REASON' changes to 'FALSE' and not only being blank.

**AdrianaBotha2** · 05-11-2023, 01:01 AM

Thank you again!!! Here is a copy of the file which might be easier

It might also help to see the questions I have which need to be answered in column B

Column B is the one which I would love to get a formula added to the one that you gave me that you cannot select the answer of b7 if b6 was not answered, same going down the column so can't answer b8 if b7 wasn't answered.

Hope this makes sense. Attachment 828882

**FDibbins** · 05-11-2023, 01:02 AM

huh. I dont get FALSE at all

and everything is based on B6 (and only B6, no matter what the row is)?

Can you upload another copy of your file showing teh FALSE plz?

**AdrianaBotha2** · 05-11-2023, 06:23 PM

Thank you - It pops up when you type a reason in Column C next to "NO" in the same row as "No" So it lliterally replaces the Complete Reason. Here is a copy

Thank you again.Email again New Checklist for Disclosures under the UTAA.xlsx

**AliGW** · 05-11-2023, 01:52 AM

The formula that returns FALSE is this:

=IF(C6="",IF(B6="no","Complete Reason",""))

It returns FALSE because C6 is NOT blank, therefore it fails at the first test. What are you expecting it to to when C6 is not blank?

You need this:

=IF(C6="",IF(B6="no","Complete Reason",""),value_if_false)

**FDibbins** · 05-12-2023, 12:16 AM

I think the problem here lays with which cells are being referenced.

My understanding is that the primary reference is to B6, and all subsequent formulas need to reference this, no matter which row they are in
(confirm this please)

Then, after that reference has been made, the formula in cell B?? then references C?? to see what to do next?

Lets get that sorted out 1st please.

Also, it appears as if your file is referencing an external like that we do not have access to - can you, for the sake of testing here - remove that link please.

**AliGW** · 05-12-2023, 12:30 AM

You still haven't added a value_if_false!

Try this:

=IF(C6="","",IF(B6="no","Complete Reason","")

You will need to remove the data validation in the cell to be able to update the formula.

**AdrianaBotha2** · 08-14-2023, 07:08 PM

[SOLVED] Thank you for everrybody's help

**FDibbins** · 08-15-2023, 12:21 AM

Happy to help.

**AliGW** · 08-15-2023, 01:15 AM

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Hybrid View

If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Re: If B6 = "NO" input "COMPLETE REASON" in B7, then if C7 has text make B7 blank

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Unable to Make FIFO Code work If I change text From "Bought" to "Buy"

[SOLVED] Code that copies row of data to another sheet based on text "Complete"/"Delete"

[SOLVED] If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...

Bookmarks

Bookmarks

Posting Permissions