Require cell entry based on another cell's answer

melissapalmer

New Member
Joined
Apr 16, 2013
Messages
8
The last thing I need in my excel spreadsheet is to require a date be input in B6 should the answer in B5 be "yes". The scenario is as follows: B5 can be yes, no or blank. If B5 is no or blank I dont need anything in B6 and the overall answer to all the previous answers will be displayed in B12. However, if B5 is "yes", B6 must have a date before the overall answer can be displayed in B12. Is there a formula to do this? I have tried excel formulas but I believe I may need a visual basic code and I have very little experience with VB. ANy help is GREATLY appreciated!!!!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
melissapalmer,

Welcome to Mr Excel.

Perhaps this in the Worksheet code module, to date stamp with today's date...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = "$B$5" Then Exit Sub
If Target = "Yes" Or Target = "yes" Then
Target.Offset(1, 0) = Date
Else:
Target.Offset(1, 0) = ""
End If
End Sub

Hope that helps.
 

melissapalmer

New Member
Joined
Apr 16, 2013
Messages
8
Thank you for replying Snakehips! I appreciate your help. I apologize for this but I have not had to use VB really before. I have the developer ribbon and have pasted your code in, then clicked save, closed and reopened the spreadsheet, enabled macros but nothing is any different. Am I missing a step to this? Again, forgive me for my lack of VB knowledge.
 

jai9

Active Member
Joined
Jul 20, 2012
Messages
325
Hi,

Try in B6,

=IF(B5="yes",TODAY(),"")

I am not clear about the B12 part though.

Jai
 

melissapalmer

New Member
Joined
Apr 16, 2013
Messages
8

ADVERTISEMENT

Thank you jai9, however B6 wont be "today's" date...it will be a date the user will have to input. I hope that helps explain it better.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
melissapalmer,

Open the file with macros enabled.
Right Click on the sheet tab of the appropriate sheet and click View Code.
That will take you to the VB editor and the required sheet module.
That is where the code should be. If it is then the code should run and react to the input in cell B5.
If I have interpreted your post correctly then it should do as you wish.

Try that and keep me informed.
 

melissapalmer

New Member
Joined
Apr 16, 2013
Messages
8

ADVERTISEMENT

Snakehips:
Thank you for your assistnace. I have followed your instructions above multiple times and it isnt doing as I wanted. Let me try explaining it again and possibly that is why.
Here is an example from the spreadsheet:
Column A Open cells for user to fill out
Name of Associate B3
Date of employment B4
Has contract been issued? B5
Date on contract B6
Is additional clearance needed? B12

Ok, so the user will put answers in B3, B4 and B5. If they answer B5 with "yes" then it is required that they put a date in B6. If the answer to B5 is no then B6 is not required. Then I need B12 to not provided the answer until we know all of the above. So maybe this is the problem that wasnt well explained before: I need B6 to be required if B5 is "yes" and I need B12 to not show the answer until the issue with b5 and B6 is addressed.

THANK YOU so much for all your help!!! When I took this project on I thought I could do it all with Excel formulas. I did not realize I would have to get into VB which I have little knowledge of. :(
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
mellisapalmer,

Does this non vba approach do what you want?

B3 B4 B5 B6 take user input.

Ensure that B4 & B6 are formatted as Date.
Use data validation for B5 so it is limited to Yes, No , Blank
Use formula in B12 so that its 'Answer' is not displayed unless B5 = Yes and B6 = a date.

NB there is not an excel formula that will check, directly, for a date. So, with the cell B6 formatted as a date it should be ok just to test that B4 is greater than 20000 (year1954!) and that B6 is greater than or equal to B4. ie same or later date.

Excel 2007
B
3Tom Thumb
406/07/2012
5yes
606/08/2012
7
8
9
10
11
12Only Show if B5 = Yes & B6 = Date

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B12=IF(AND(B5 = "Yes",(B4)>20000,B6>=B4),"Only Show if B5 = Yes & B6 = Date","")

<tbody>
</tbody>

<tbody>
</tbody>

Obviously, edit the "Only Show......" to reflect your 'Answer'

Hope that helps. Let me know either way.
 
Last edited:

melissapalmer

New Member
Joined
Apr 16, 2013
Messages
8
Doing this without using VB would be AMAZING! I have B4 and B6 set as a date and B5 using data validation already. Glad we are on the same page with that! ( :
So, when you say edit the "Only Show......" to reflect your 'Answer' - are you indicating to copy my current formula from B12 into this? Here is my B12 formula currently that dictates the answer in B12.

=IF(COUNTBLANK(B3:B5)>0,"",IF(OR(E3="Yes",G3="Yes",I3="Yes"),"Yes","No"))

There are obviously hidden cells that also go in to answering B12 based on the answers as well.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Yes, exactly that.


Worksheet Formulas
CellFormula
B12=IF(AND(B5 = "Yes",(B4)>20000,B6>=B4),IF(COUNTBLANK(B3:B5)>0,"",IF(OR(E3="Yes",G3="Yes",I3="Yes"),"Yes","No")),"")

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top