Nested IF Statements

Excel Excel 2007
February 18, 2010 at 14:06:15
Specs: Windows XP
Background: I have two validation lists with possible values in Excel. The first list has a number of values. I'm only interested in one value- X. The second list also has a number of possible values-I'm interested in values 1, 2, 3, 4, 5, 6.

I need to write a formula to flag a selection when a combination of X and 1, or X and 2, etc... is chosen.

I need the formula to perform the following:

1) Look to check if value X is selected in a range of cells (i.e. D21:D31)
2) If value X is selected and 1 or 2 or 3 or 4 or 5 or 6 are selected in another range of cells (i.e. D32:D40), a message pops up.
3) If X and 1, or X and 2, etc are chose, flag this selection.


See More: Nested IF Statements

Report •


#1
February 18, 2010 at 17:30:53
Are you asking for help or just telling us what you need?

Report •

#2
February 19, 2010 at 08:12:38
I'm asking for help. I need to write this type of formula. Can you please help. I need to complete this for a project I'm working on and can't figure it out.

Report •

#3
February 19, 2010 at 08:34:02
How are these 2 cells being selected at the same time?

Is the user the Ctrl key to select 2 cells?

Is it being done via a macro?


Report •

Related Solutions

#4
February 19, 2010 at 09:46:46
I work as a SAP Analyst for a large consumer good company. The document is a form that a end user uses to select access in a SAP BW system.

The form has two sections. One section gives access to queries, another gives access to responsibility areas (China, India, etc...)-where the users can run the query for.

Each section of the form has drop downs (validation lists) that the user selects from. The access is "built up" from two sections.There are 10 or so rows for each section. When the user selects value "X" in the first drop down (any of the 10 rows) and then selects value 1, 2, 3, 4, 5, 6 in the second drop down (second set of 10 rows), I need a warning message to come up-Special Approval required.

I'll send you some of our product if you can help! I'm sure will like it. http://www.5gum.com/


Report •

#5
February 19, 2010 at 10:36:04
Is the "form" an actual UserForm, or is it a spreadsheet designed as a form with in-cell Drop Downs?

If you are using in-cell Drop Downs, something like this Worksheet_Change macro should work.

Assuming your Drop Downs are in A1 and B1, this will flag combinations of X and 1 - 6. It was written based on the example data you provided.

Obviously, the code will need to be changed to match your actual data.

Private Sub Worksheet_Change(ByVal Target As Range)
'Was A1 or B1 changed?
 If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
'If yes, does A1 contain "X"?
  If Range("A1") = ("X") Then
'If yes, does B1 contain a "restricted" value?
   Select Case Range("B1")
    Case 1 To 6
     MsgBox Title:="Special Approval Needed", _
        Prompt:="Please Call Extension 54623 For Approval"
   End Select
  End If
 End If
End Sub


Report •

#6
February 19, 2010 at 11:16:40
The form is an Excel spreadsheet that was designed as a form. I will apply this Macro to my form and let you know. Thank you!

Report •

#7
February 19, 2010 at 11:46:55
I understand the code up to the point of "Case 1 To 6"
The cases that I need to identify are named as such (these are the values from the second drop down list that I care about):

Y_DATA_RESP_ZCHINAGZ
Y_DATA_RESP_ZCNDOMGZ
Y_DATA_RESP_ZCHINASH
Y_DATA_RESP_ZPHFACT
Y_DATA_RESP_ZSHFACT
Y_DATA_RESP_ZCNDOMSH
Y_DATA_RESP_ZCNEXPGZ
Y_DATA_RESP_ZCNDOMSH

How do I declare this?

Sorry for the elementary questions, but I have no experience with VB.


Report •

#8
February 19, 2010 at 14:42:02
Place your cursor on the word Case in the code and hit F1.

That will open the Help file for Case. Read the Help file, including the Example, to get a better understand of why I used Case 1 To 6.

For a brute force method based on your actual data you can use the code below which checks for each individual "restricted value".

Private Sub Worksheet_Change(ByVal Target As Range)
'Was A1 or B1 changed?
 If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
'If yes, does A1 contain "X"?
  If Range("A1") = ("X") Then
'If yes, does B1 contain a "restricted" value?
   Select Case Range("B1")
    Case "Y_DATA_RESP_ZCHINAGZ", _
         "Y_DATA_RESP_ZCNDOMGZ", _
         "Y_DATA_RESP_ZCHINASH", _
         "Y_DATA_RESP_ZPHFACT", _
         "Y_DATA_RESP_ZSHFACT", _
         "Y_DATA_RESP_ZCNDOMSH", _
         "Y_DATA_RESP_ZCNEXPGZ", _
         "Y_DATA_RESP_ZCNDOMSH"
     MsgBox Title:="Special Approval Needed", _
        Prompt:="Please Call Extension 54623 For Approval"
   End Select
  End If
 End If
End Sub  


It's possible that it could be streamlined if I knew more about the other values in your list. For example, if the restricted values have something in common with each other, but not in common with the non-restricted values, then the code might be different.

Let's say only the restricted values start with a Y. The Select Case section of code might look like this, since the only thing it would need to check for would be the first letter of the value in B1.

Select Case Left(Range("B1"), 1)
    Case "Y"
     MsgBox Title:="Special Approval Needed", _
        Prompt:="Please Call Extension 54623 For Approval"


Report •

Ask Question