Multiple Required Fields - some based on another field answer

E

Erosita

New Member
Joined
Oct 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello - I have a workbook where several fields need to be required. I have figured some of them out but Im running into issue figuring out how to make the fields that are dependent on the answer in another field to be required.

If B10 = Retail or Both, I need B11 to be a required field
If B18 = Yes, B19:B22 should be required
If B24 = Yes, B25:B28 should be required
If B30 = Yes, B31:B34 should be required
If B36 = Yes, B37:B40 should be required
If B42 = Yes, B43:B46 should be required
If B48 = Yes, B49:B52 should be required
If B48 = Yes, B49:B52 should be required
This goes down to the end where if Add Another Drug = Yes, the next 4 fields should be required.

DO Inquiry Submission Form 10042022.xlsm
AB
1 DO Inquiry Submission Form
2DO Name
3Market Name
4Zip code
5LOB (MAPD or PDP)
6Does the member receive extra help?
7 Premium Subsidy Level
8 Copay Level
9Pharmacy Name
10Retail, Mail Order, or Both?
11 Retail store # or address street/city/zip code address
12Drug List
13Drug name
14Dosing
15Quantity
16Frequency
17Free form text box:
18Add Another Drug?Yes
19Drug Name
20Dosing
21Quantity
22Frequency
23Free Form Box
24Add Another Drug?Yes
25Drug Name
26Dosing
27Quantity
28Frequency
29Free Form Box
30Add Another Drug?Yes
31Drug Name
32Dosing
33Quantity
34Frequency
35Free Form Box
36Add Another Drug?Yes
37Drug Name
38Dosing
39Quantity
40Frequency
41Free Form Box
42Add Another Drug?Yes
43Drug Name
44Dosing
45Quantity
46Frequency
47Free Form Box
48Add Another Drug?Yes
49Drug Name
50Dosing
51Quantity
52Frequency
53Free Form Box
54Add Another Drug?Yes
55Drug Name
56Dosing
57Quantity
58Frequency
59Free Form Box
60Add Another Drug?Yes
61Drug Name
62Dosing
63Quantity
64Frequency
65Free Form Box
66Add Another Drug?Yes
67Drug Name
68Dosing
69Quantity
70Frequency
71Free Form Box
72Add Another Drug?Yes
73Drug Name
74Dosing
75Quantity
76Frequency
77Free Form Box
DO Inquiry Submission Form
Cell Formulas
RangeFormula
A19,A25,A31,A37,A43,A49,A55,A61,A67,A73A19=IF(B18="Yes", "Drug Name","")
A20,A26,A32,A38,A44,A50,A56,A62,A68,A74A20=IF(B18="Yes", "Dosing","")
A21,A27,A33,A39,A45,A51,A57,A63,A69,A75A21=IF(B18="Yes", "Quantity","")
A22,A28,A34,A40,A46,A52,A58,A64,A70,A76A22=IF(B18="Yes", "Frequency","")
A23,A29,A35,A41,A47,A53,A59,A65,A71,A77A23=IF(B18="Yes", "Free Form Box","")
A24,A30,A36,A42,A48,A54,A60,A66,A72A24=IF(B18="Yes","Add Another Drug?","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B76Expression=IF($B$16="",TRUE,FALSE)textNO
B75Expression=IF($B$15="",TRUE,FALSE)textNO
B70Expression=IF($B$16="",TRUE,FALSE)textNO
B69Expression=IF($B$15="",TRUE,FALSE)textNO
B64Expression=IF($B$16="",TRUE,FALSE)textNO
B63Expression=IF($B$15="",TRUE,FALSE)textNO
B58Expression=IF($B$16="",TRUE,FALSE)textNO
B57Expression=IF($B$15="",TRUE,FALSE)textNO
B52Expression=IF($B$16="",TRUE,FALSE)textNO
B51Expression=IF($B$15="",TRUE,FALSE)textNO
B46Expression=IF($B$16="",TRUE,FALSE)textNO
B45Expression=IF($B$15="",TRUE,FALSE)textNO
B40Expression=IF($B$16="",TRUE,FALSE)textNO
B39Expression=IF($B$15="",TRUE,FALSE)textNO
B34Expression=IF($B$16="",TRUE,FALSE)textNO
B33Expression=IF($B$15="",TRUE,FALSE)textNO
B28Expression=IF($B$16="",TRUE,FALSE)textNO
B27Expression=IF($B$15="",TRUE,FALSE)textNO
B22Expression=IF($B$16="",TRUE,FALSE)textNO
B21Expression=IF($B$15="",TRUE,FALSE)textNO
A46Expression=IF(AND(B42="Yes",B46=""),TRUE,FALSE)textNO
A45Expression=IF(AND(B42="Yes",B45=""),TRUE,FALSE)textNO
A44Expression=IF(AND(B42="Yes",B44=""),TRUE,FALSE)textNO
A43Expression=IF(AND(B42="Yes",B43=""),TRUE,FALSE)textNO
A76Expression=IF(AND(B72="Yes",B76=""),TRUE,FALSE)textNO
A75Expression=IF(AND(B72="Yes",B75=""),TRUE,FALSE)textNO
A74:B74Expression=IF(AND(B72="Yes",B74=""),TRUE,FALSE)textNO
A73:B73Expression=IF(AND(B72="Yes",B73=""),TRUE,FALSE)textNO
A70Expression=IF(AND(B66="Yes",B70=""),TRUE,FALSE)textNO
A69Expression=IF(AND(B66="Yes",B69=""),TRUE,FALSE)textNO
A68:B68Expression=IF(AND(B66="Yes",B68=""),TRUE,FALSE)textNO
A67:B67Expression=IF(AND(B66="Yes",B67=""),TRUE,FALSE)textNO
A64Expression=IF(AND(B60="Yes",B64=""),TRUE,FALSE)textNO
A63Expression=IF(AND(B60="Yes",B63=""),TRUE,FALSE)textNO
A62:B62Expression=IF(AND(B60="Yes",B62=""),TRUE,FALSE)textNO
A61:B61Expression=IF(AND(B60="Yes",B61=""),TRUE,FALSE)textNO
A58Expression=IF(AND(B54="Yes",B58=""),TRUE,FALSE)textNO
A57Expression=IF(AND(B54="Yes",B57=""),TRUE,FALSE)textNO
A56:B56Expression=IF(AND(B54="Yes",B56=""),TRUE,FALSE)textNO
A55:B55Expression=IF(AND(B54="Yes",B55=""),TRUE,FALSE)textNO
A52Expression=IF(AND(B48="Yes",B52=""),TRUE,FALSE)textNO
A51Expression=IF(AND(B48="Yes",B51=""),TRUE,FALSE)textNO
A50:B50Expression=IF(AND(B48="Yes",B50=""),TRUE,FALSE)textNO
A49:B49Expression=IF(AND(B48="Yes",B49=""),TRUE,FALSE)textNO
B44Expression=IF(AND(C42="Yes",C44=""),TRUE,FALSE)textNO
B43Expression=IF(AND(C42="Yes",C43=""),TRUE,FALSE)textNO
A40Expression=IF(AND(B36="Yes",B40=""),TRUE,FALSE)textNO
A39Expression=IF(AND(B36="Yes",B39=""),TRUE,FALSE)textNO
A38:B38Expression=IF(AND(B36="Yes",B38=""),TRUE,FALSE)textNO
A37:B37Expression=IF(AND(B36="Yes",B37=""),TRUE,FALSE)textNO
A34Expression=IF(AND(B30="Yes",B34=""),TRUE,FALSE)textNO
A33Expression=IF(AND(B30="Yes",B33=""),TRUE,FALSE)textNO
A32:B32Expression=IF(AND(B30="Yes",B32=""),TRUE,FALSE)textNO
A31:B31Expression=IF(AND(B30="Yes",B31=""),TRUE,FALSE)textNO
A28Expression=IF(AND(B24="Yes",B28=""),TRUE,FALSE)textNO
A27Expression=IF(AND(B24="Yes",B27=""),TRUE,FALSE)textNO
A26:B26Expression=IF(AND(B24="Yes",B26=""),TRUE,FALSE)textNO
A25:B25Expression=IF(AND(B24="Yes",B25=""),TRUE,FALSE)textNO
A22Expression=IF(AND(B18="Yes",B22=""),TRUE,FALSE)textNO
A21Expression=IF(AND(B18="Yes",B21=""),TRUE,FALSE)textNO
A20:B20Expression=IF(AND(B18="Yes",B20=""),TRUE,FALSE)textNO
A19:B19Expression=IF(AND(B18="Yes",B19=""),TRUE,FALSE)textNO
A16:B16Expression=IF($B$16="",TRUE,FALSE)textNO
A15:B15Expression=IF($B$15="",TRUE,FALSE)textNO
A14:B14Expression=IF($B$14="",TRUE,FALSE)textNO
A10:B10Expression=IF($B$10="",TRUE,FALSE)textNO
A11:B11Expression=IF(OR(B10="Retail",B10="Both")*AND(B11=""),TRUE,FALSE)textNO
A5:B5Expression=IF($B$5="",TRUE,FALSE)textNO
A4:B4Expression=IF($B$4="",TRUE,FALSE)textNO
A3:B3Expression=IF($B$3="",TRUE,FALSE)textNO
A2:B2Expression=IF($B$2="",TRUE,FALSE)textNO
A13:B13Expression=IF($B$13="",TRUE,FALSE)textNO
A9:B9Expression=IF($B$9="",TRUE,FALSE)textNO
Cells with Data Validation
CellAllowCriteria
B18List=Sheet2!$C$1:$C$2
B24List=Sheet2!$C$1:$C$2
B30List=Sheet2!$C$1:$C$2
B36List=Sheet2!$C$1:$C$2
B42List=Sheet2!$C$1:$C$2
B48List=Sheet2!$C$1:$C$2
B54List=Sheet2!$C$1:$C$2
B60List=Sheet2!$C$1:$C$2
B66List=Sheet2!$C$1:$C$2
B72List=Sheet2!$C$1:$C$2
B3List=Sheet2!$A$2:$A$21
B4Text length=5
B5List=Sheet2!$B$1:$B$2
B6List=Sheet2!$C$1:$C$2
B7List=Sheet2!$D$1:$D$4
B8List=Sheet2!$D$5:$D$8
B10List=Sheet2!$D$16:$D$18
B15List=Sheet2!$D$9:$D$12
B16List=Sheet2!$D$13:$D$15
B21List=Sheet2!$D$9:$D$12
B22List=Sheet2!$D$13:$D$15
B27List=Sheet2!$D$9:$D$12
B28List=Sheet2!$D$13:$D$15
B33List=Sheet2!$D$9:$D$12
B34List=Sheet2!$D$13:$D$15
B39List=Sheet2!$D$9:$D$12
B40List=Sheet2!$D$13:$D$15
B45List=Sheet2!$D$9:$D$12
B46List=Sheet2!$D$13:$D$15
B51List=Sheet2!$D$9:$D$12
B52List=Sheet2!$D$13:$D$15
B57List=Sheet2!$D$9:$D$12
B58List=Sheet2!$D$13:$D$15
B63List=Sheet2!$D$9:$D$12
B64List=Sheet2!$D$13:$D$15
B69List=Sheet2!$D$9:$D$12
B70List=Sheet2!$D$13:$D$15
B75List=Sheet2!$D$9:$D$12
B76List=Sheet2!$D$13:$D$15
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
This is what I have so far

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Set ws = Worksheets("DO Inquiry Submission Form")

If ws.Application.WorksheetFunction.CountBlank(ws.Range("B2:B5")) > 0 Or ws.Application.WorksheetFunction.CountBlank(ws.Range("B9:B10")) > 0 Or ws.Application.WorksheetFunction.CountBlank(ws.Range("B13:B16")) > 0 Then

MsgBox ("Please fill in the required cells")
Cancel = True
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
242
birdman15
B
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
364
Peter_SSs
Peter_SSs
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
283
offthelip
O
D
  • Solved
Search for exact word in comma separated string
Replies
4
Views
405
DarkSmile
D
Z
  • Solved
Formula to validate the right manager within manager levels in multiple rows
Replies
5
Views
82
Fluff
Fluff

Forum statistics

Threads
1,214,951
Messages
6,122,449
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top