HELP! Nesting IF Statements/Dependent Dropdown List

[Thomas Peters]

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

..:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

..:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status†is default text that the use can see)
-Submitted
-Not Returned

..:Conditional Results Anticipated:.
If D4=“Select Statusâ€, then E4=â€â€
If D4="Submitted", then E4=display the date & time
If D4="Not Returnedâ€, Then E4=â€-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()†function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

..:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

..:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=â€â€ (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

 

[Ron Rosenfeld]

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

.:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

.:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status” is default text that the use can see)
-Submitted
-Not Returned

.:Conditional Results Anticipated:.
If D4=“Select Status”, then E4=””
If D4="Submitted", then E4=display the date & time
If D4="Not Returned”, Then E4=”-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()” function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

.:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

.:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=”” (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

I believe that in order to do what you are describing, you'll need to use a VBA
event-triggered macro. Probably a Change event.

This should get you started:

1. Set up your dropdown in D4 as you have describe.
2. Right click on the worksheet tab and select View Code.
3. Paste the code below into the window that opens.

=======================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range

Set AOI = [D4]

If Not Intersect(Target, AOI) Is Nothing Then
Select Case AOI.Text
Case Is = "Submitted"
AOI.Offset(0, 1).Value = Now
Case Is = "Not Returned"
AOI.Offset(0, 1).Value = "-"
Case Else
AOI.Offset(0, 1).ClearContents
End Select

End If
End Sub
=========================

Have fun!


--ron

 

[Thomas Peters]

Ron, Thank you for providing a response as well as a VB solution, but I was
hoping that I could see and example of a nested IF statement based on the
conditions I provided. In the VB code you provided regarding the first case
Is = "Submitted"? Will this continually update each time the workbook is
opened? It's important that the "NOW()" function either be converted to
static text or a solution developed preventing the date & time from ever
being recalculated regardless how many times the workbook is openned. This
function is part of a status report tracking spreadsheet. As reports are sent
to me (about 12 ) for analysis I select "Submitted" to denote when I received
a report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

.:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

.:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status†is default text that the use can see)
-Submitted
-Not Returned

.:Conditional Results Anticipated:.
If D4=“Select Statusâ€, then E4=â€â€
If D4="Submitted", then E4=display the date & time
If D4="Not Returnedâ€, Then E4=â€-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()†function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

.:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

.:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=â€â€ (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

I believe that in order to do what you are describing, you'll need to use a VBA
event-triggered macro. Probably a Change event.

This should get you started:

1. Set up your dropdown in D4 as you have describe.
2. Right click on the worksheet tab and select View Code.
3. Paste the code below into the window that opens.

=======================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range

Set AOI = [D4]

If Not Intersect(Target, AOI) Is Nothing Then
Select Case AOI.Text
Case Is = "Submitted"
AOI.Offset(0, 1).Value = Now
Case Is = "Not Returned"
AOI.Offset(0, 1).Value = "-"
Case Else
AOI.Offset(0, 1).ClearContents
End Select

End If
End Sub
=========================

Have fun!


--ron

 

[Ron Rosenfeld]

Thomas,

You need a VBA solution because of the issue you discovered with NOW() being a
volatile function.

I do not think that your Version 3.0 does do what you think it does. First of
all, a function cannot change the contents of another cell.

If I put your version 3.0 in E4, (and also set the Tools/Options/Calculation
Iterations to 1 to avoid the circular reference issue), then it will show a
volatile time if D4 <> "submitted", and FALSE if D4="submitted". The time code
will change whenever the sheet recalculates. (The same behavior ensues if the
formula is in a different cell, except, of course, the results of the formula
can be seen only that different cell; and could return TRUE if E4 is empty).

This is not what I understood you to want. And there is no point in using
worksheet functions if you require a non-volatile time that you wish to have
entered automatically.

Your other questions suggest that you have not yet tried the code I proposed.
Please post back with any problems you encounter after you've tried it so we
can fine tune things, or let me know that it works!

==============================================

Ron, Thank you for providing a response as well as a VB solution, but I was
hoping that I could see and example of a nested IF statement based on the
conditions I provided. In the VB code you provided regarding the first case
Is = "Submitted"? Will this continually update each time the workbook is
opened? It's important that the "NOW()" function either be converted to
static text or a solution developed preventing the date & time from ever
being recalculated regardless how many times the workbook is openned. This
function is part of a status report tracking spreadsheet. As reports are sent
to me (about 12 ) for analysis I select "Submitted" to denote when I received
a report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

.:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

.:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status” is default text that the use can see)
-Submitted
-Not Returned

.:Conditional Results Anticipated:.
If D4=“Select Status”, then E4=””
If D4="Submitted", then E4=display the date & time
If D4="Not Returned”, Then E4=”-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()” function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

.:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

.:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=”” (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

I believe that in order to do what you are describing, you'll need to use a VBA
event-triggered macro. Probably a Change event.

This should get you started:

1. Set up your dropdown in D4 as you have describe.
2. Right click on the worksheet tab and select View Code.
3. Paste the code below into the window that opens.

=======================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range

Set AOI = [D4]

If Not Intersect(Target, AOI) Is Nothing Then
Select Case AOI.Text
Case Is = "Submitted"
AOI.Offset(0, 1).Value = Now
Case Is = "Not Returned"
AOI.Offset(0, 1).Value = "-"
Case Else
AOI.Offset(0, 1).ClearContents
End Select

End If
End Sub
=========================

Have fun!


--ron

--ron

 

[Thomas Peters]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi Ron,

All I can say is…Thank You! After I put in your code everything worked just
as I explained. I guess I was being a little stubborn because originally I
was more-or-less not willing to accept your solution even though I knew deep
down what you had purposed would work after I read your reply. So again...I
thank you for your patients, support, and willingness to put up with my
reluctance.

Sincerely

itechxxiv
Thomas
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thomas,

You need a VBA solution because of the issue you discovered with NOW() being a
volatile function.

I do not think that your Version 3.0 does do what you think it does. First of
all, a function cannot change the contents of another cell.

If I put your version 3.0 in E4, (and also set the Tools/Options/Calculation
Iterations to 1 to avoid the circular reference issue), then it will show a
volatile time if D4 <> "submitted", and FALSE if D4="submitted". The time code
will change whenever the sheet recalculates. (The same behavior ensues if the
formula is in a different cell, except, of course, the results of the formula
can be seen only that different cell; and could return TRUE if E4 is empty).

This is not what I understood you to want. And there is no point in using
worksheet functions if you require a non-volatile time that you wish to have
entered automatically.

Your other questions suggest that you have not yet tried the code I proposed.
Please post back with any problems you encounter after you've tried it so we
can fine tune things, or let me know that it works!

==============================================

Ron, Thank you for providing a response as well as a VB solution, but I was
hoping that I could see and example of a nested IF statement based on the
conditions I provided. In the VB code you provided regarding the first case
Is = "Submitted"? Will this continually update each time the workbook is
opened? It's important that the "NOW()" function either be converted to
static text or a solution developed preventing the date & time from ever
being recalculated regardless how many times the workbook is openned. This
function is part of a status report tracking spreadsheet. As reports are sent
to me (about 12 ) for analysis I select "Submitted" to denote when I received
a report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I am having difficulty understanding how to formulate multiple nested IF
statements in order to produce the precise result based on the conditions. My
first stumbling block is knowing where to place the parentheses to establish
the precedence of the conditions to be evaluated i.e. if the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

.:Rationale:.
The purpose of this function is to display a value (i.e. string of text,
date/time, or NULL) in the adjacent cell when the user selects one of the
items in the list. In this case, the drop-down list resides in cell D4 and
generates a value in cell E4.

.:Version 1.0:.
In cell D4 there is a drop-down list containing 3 elements:
-Select Status (“Select Status†is default text that the use can see)
-Submitted
-Not Returned

.:Conditional Results Anticipated:.
If D4=“Select Statusâ€, then E4=â€â€
If D4="Submitted", then E4=display the date & time
If D4="Not Returnedâ€, Then E4=â€-“

The IF statement below resides within the cell E4 and simply displays the
results based upon which item in the drop-down list in cell D4 was selected.
However, the problem with using the “NOW() or TODAY()†function is that they
both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select
Status","")))

.:Version 2.0:.
In version 2 I tried a work around by inserting a static date & time
manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the
time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

.:Version 3.0:.
I was able to convert the NOW() function result into text and only update if
E4=â€â€ (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the
drop-down list and can not get it to work. Here are a few examples I have
tried and don’t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not
Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy
h:mm AM/PM"))

Please explain how to nest IF statements or provide any URL’s etc, as I have
searched the web high & low and had no luck in finding any meaningful
information. Oh…don’t forget to show me the right way to compile this
formula. Thanks.

I believe that in order to do what you are describing, you'll need to use a VBA
event-triggered macro. Probably a Change event.

This should get you started:

1. Set up your dropdown in D4 as you have describe.
2. Right click on the worksheet tab and select View Code.
3. Paste the code below into the window that opens.

=======================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range

Set AOI = [D4]

If Not Intersect(Target, AOI) Is Nothing Then
Select Case AOI.Text
Case Is = "Submitted"
AOI.Offset(0, 1).Value = Now
Case Is = "Not Returned"
AOI.Offset(0, 1).Value = "-"
Case Else
AOI.Offset(0, 1).ClearContents
End Select

End If
End Sub
=========================

Have fun!


--ron

--ron