Nested If Workaround in Data Validation

g0atface

New Member
Joined
Oct 11, 2006
Messages
5
Hi

I have this sheet set up - im currently using data validation on the values shown. The column B holds the selection optoins the 1st being section list, which when clicked gives 3 options, then the next list has a validation based on the 3 ranges, STROPT, PIPOPT, MEIOPT

Basically im trying to go down the next step where the next validation cell will bring up the correct list when a list is chosen.

For example chosing A- Preliminaries will show the options for SCTA and so on.

i can do it with Nested IF up to section G but after this the 7 limitation kicks in and i can do no more - is there a way round this??

EDIT: Removed HTML - Image is too big for the page - Moderator
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
The 7 nested IFs problem has been stated and looked at many times in this forum, and it has been concluded that it is usually not practical to go beyond the 8 total (7 nested + 1st IF), in which case VBA is recommended.

However, if you must use many IFs, just consider this:
1. Use the first 7 IFs (1st If + 6 nested IFS), then, make the last nested IF return a 0 or a "", if the first 7 have failed. If you need more IFs, concatenate with the & sign, and
2. Use a second group of up to 7 IFs, with the last nested IF returning a 0 or "" if the previous 7 IFs failed. If required, concatenate again, and enter the 3rd group of 8 IFs.
3. And so on, with up to 30 groups of 8 IFs!

Of course, you might run into the limit on the number of characters that can be entered into a cell.
 

g0atface

New Member
Joined
Oct 11, 2006
Messages
5
well id be more than happy to use a different way around the problem as i totally agree that 7 nested ifs is a cumbersome method.

this is what i ended up with (didnt work either!) CL = defined name for B8

=IF(CL=D3,SCTA,(IF(CL=D4,SCTB,(IF(CL=D5,SCTC,(IF(CL=D6,SCTD,(IF(CL=D7,SCTE,(IF(CL=D8,SCTF,(IF(CL=D9,SCTG,0)))))))))))))&IF(CL=D10,SCTM,(IF(CL=D11,SCTN,(IF(CL=D12,SCTR,(IF(CL=D13,SCTX,(IF(CL=D14,SCTY,(IF(CL=D15,SCTZ,(IF(CL=D16,SCTMisc,0)))))))))))))

Im not exactly clued up in VBA though - is it possible to use it in a similar manner i.e for a combo box -

If (Cell=whatever, RANGEA, do next if)

so im saying if for example "structures" is selected, then the combo box will know to use the STROPT range, and if pipework is selected it will use the PIPOPT range

etc.

Thanks for your help
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Well, let's look at a few of your IFs:
=IF(CL=D3,SCTA,(IF(CL=D4,SCTB,(IF(CL=D5,SCTC,(IF(CL=D6,...
I'm not sure of your use of CL, but, SCTA is seen by Excel as a value, in this case, a 0, since it contains no value. You must use text, that is, "SCTA", in each of your IFs. Give it another try.

As to VBA, I'm not into it, so, it will have to be some other person.
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

Nested If Workaround in Data Validation with VBA

Nested If Workaround in Data Validation with VBA

I have taken your displayed data, and modified the wookbook slightly.

I was able, with the displayed information, to create multiple data validation lists with VBA.
Structures, E - EARTHWORKS, E7 - Filling ancillaries

EDIT: Removed HTML - Images are too big for the page - Moderator
 
Joined
Jul 30, 2006
Messages
3,656
Part 2 Nested If Workaround in Data Validation with VBA

Part 2 Nested If Workaround in Data Validation with VBA

Manually create the first validation list in cell B1:
Click in cell B1
Data, Validation, List, Source: =$F$2:$F$6


Copy the below code:

Code:
'-------------------Start of Code---------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'
' The following code was created/modified on 10/21/2006 by Stanley D. Grom, Jr.
'   The original code was posted to Mr. Excel
'     by rajibdas, Aug 31, 2006 9:19 am    Post subject:

    If Intersect(Target, Range("B1")) Is Nothing Then 'cell where you'll have the inital dropdown list
        
        If Intersect(Target, Range("C1")) Is Nothing Then
            Exit Sub
    
        ElseIf Range("C1").Value = "" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
            End With
            Range("C1").Select
            Exit Sub
    
        ElseIf Range("C1").Value = "A - PRELIMINARIES" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$J$2:$J$9"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub
        
        ElseIf Range("C1").Value = "B - GROUND INVESTIGATION" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$K$2:$K$12"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        ElseIf Range("C1").Value = "C - GEOTECHNICAL AND OTHER SERVICES" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$L$2:$L$4"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        ElseIf Range("C1").Value = "D - DEMOLITION AND SITE CLEARANCE" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$M$2:$M$4"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        ElseIf Range("C1").Value = "E - EARTHWORKS" Then
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$N$2:$N$9"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        ' Setup code for the following selections (see above example)
        'F - IN SITU CONCRETE
        'G - CONCRETE ANCILLIARIES
        'M - STRUCTURAL METALWORK
        'N - MISCELLANEOUS METALWORK
        'R - ROADS AND PAVINGS
        'X - MISCELLANEOUS WORK
        'Y - SEWER AND WATER MAIN RENOVATION AND ANCILLIARY WORKS
        'Z - CLASS Z: BUILDING WORKS
        'Miscellaneous

        End If
        Exit Sub
    
    ElseIf Range("B1").Value = "" Then ' if cell B1 blank, blank C1
        Range("C1").Select
        Range("C1").ClearContents
        With Selection.Validation ' create the second dropdown on cells C1
            .Delete
        End With
        Range("B1").Select
        Exit Sub
    
    ElseIf Range("B1").Value = "Structures" Then ' if cell B1 is the name you select
        Range("C1").Select
        Range("C1").ClearContents
        With Selection.Validation ' create the second dropdown on cells C1
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$G$2:$G$17"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Exit Sub

    ElseIf Range("B1").Value = "Pipework" Then
        Range("C1").Select
        Range("C1").ClearContents
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$H$2:$H$11"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Exit Sub
    
    ElseIf Range("B1").Value = "MEICA" Then
        Range("C1").Select
        Range("C1").ClearContents
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$I$2:$I$4"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Exit Sub
    End If

End Sub
'-------------------End of Code---------------------


Press ALT F11, and paste the above code into the
VBA Project, Microsoft Excel Objects, Sheet1(Sheet1)


Click in cell B1, and make your first choice.

Click in cell C1, and make your second choice.

Click in cell D1, and make your third choice.


I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395

ADVERTISEMENT

Perhaps I am wrong, but do you wish to make a selection from one validation list and the choices in the next validation list will depend on the selection you made? If that is what you are trying to do then you might want to check out this link along with the ones at the bottom of the page. http://www.contextures.com/xlDataVal13.html#Depend
 
Joined
Jul 30, 2006
Messages
3,656
Part 1: Three Linked Data Validation Lists with VBA

Part 1: Three Linked Data Validation Lists with VBA

I have taken your displayed data, and modified the wookbook slightly.

I was able, with the displayed information, to create multiple data validation lists with VBA.
Structures, E - EARTHWORKS, E7 - Filling ancillaries
Data Validation x 3 - g0atface - Nested If Workaround in Data Validation.xls
BCDE
1StructuresE-EARTHWORKSE7-Fillingancillaries
2
3
Sheet1
 
Joined
Jul 30, 2006
Messages
3,656
Part 2: Three Linked Data Validation Lists with VBA

Part 2: Three Linked Data Validation Lists with VBA

More screenshots:
Data Validation x 3 - g0atface - Nested If Workaround in Data Validation.xls
FGHI
1SectionListSTROPTPIPOPTMEIOPT
2
3StructuresA-PRELIMINARIESI-PIPEWORK-PIPESME1-PlantandEquipment
4PipeworkB-GROUNDINVESTIGATIONJ-PIPEWORK-FITTINGSANDVALVES
5MEICAC-GEOTECHNICALANDOTHERSERVICESK-PIPEWORK-MANHOLESANDPIPEWORKANCILLARIES
6D-DEMOLITIONANDSITECLEARANCEL-PIPEWORK-SUPPORTS&PROTECTION,ANCILLIARIESTOLAYING&EXCAVATION
7E-EARTHWORKSI-PIPEWORK-PIPES(ROAD)
8F-INSITUCONCRETEJ-PIPEWORK-FITTINGSANDVALVES(ROAD)
9G-CONCRETEANCILLIARIESK-PIPEWORK-MANHOLESANDPIPEWORKANCILLARIES(ROAD)
10M-STRUCTURALMETALWORKL-PIPEWORK-SUPPORTS&PROTECTION,ANCILLARIESTOLAYING&EXCAVATION(ROAD)
11N-MISCELLANEOUSMETALWORK
12R-ROADSANDPAVINGS
13X-MISCELLANEOUSWORK
14Y-SEWERANDWATERMAINRENOVATIONANDANCILLIARYWORKS
15Z-CLASSZ:BUILDINGWORKS
16Miscellaneous
17
Sheet1
 
Joined
Jul 30, 2006
Messages
3,656
Part 3: Three Linked Data Validation Lists with VBA

Part 3: Three Linked Data Validation Lists with VBA

More screenshots:
Data Validation x 3 - g0atface - Nested If Workaround in Data Validation.xls
HIJK
1PIPOPTMEIOPTSCTASCTB
2
3I-PIPEWORK-PIPESME1-PlantandEquipmentA0-PreliminarieselementsnotstatedB0-Typeofworknotstated
4J-PIPEWORK-FITTINGSANDVALVESA1-ContracturalRequirementsB1-Trialpitsandtrenches
5K-PIPEWORK-MANHOLESANDPIPEWORKANCILLARIESA2-SpecifiedrequirementsB2-Lightpercussiveboreholes
6L-PIPEWORK-SUPPORTS&PROTECTION, ANCILLIARIESTOLAYING&EXCAVATIONA3-MethodrelatedchargesB3-Rotarydrilledboreholes
7I-PIPEWORK-PIPES(ROAD)A4-ProvisionalsumsB4-Samples
8J-PIPEWORK-FITTINGSANDVALVES(ROAD)A5-NominiatedsubcontractorsB5-Sitetestsandobservations
9K-PIPEWORK-MANHOLESANDPIPEWORK ANCILLARIES(ROAD)B6-Instrumentedobservations
10L-PIPEWORK-SUPPORTS&PROTECTION, ANCILLARIESTOLAYING&EXCAVATION(ROAD)B7-Laboratorytests
11B8-Professionalservices
Sheet1
 

Forum statistics

Threads
1,137,154
Messages
5,679,911
Members
419,862
Latest member
Bluewings666

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