IF Statement in dropdown list without defaulting to 0?

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi, I haven't quit found the answer I'm looking for, so hope I can get some help here :)

I have a dropdown list that contains the following choices:

Column C (Dropdown list)
Yes,
No,
Partially,
N/A

Column D Has a IF statement that tells the person filling out the form if they must answer that question or not. If not it says NA in this column.

I have an IF statement in Column C with the dropdown list that says: =IF(D9="NA","N/A",)
This works, but then it puts the default in the cell to "0" - I just need it to be blank or default to the If statement of "N/A"

Any ideas?

Thanks :)

Marcy
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
OK, for the initial default population of your "master" document, you can manually run this VBA code, which will look through column D on the sheets you specify, and if the value is "NA", it will put an "N/A" in column C of the same row:
Power Query:
Sub InitialDefaultPopulation()

    Dim wsArr()
    Dim s As Long
    Dim ws As String
    Dim lr As Long
    Dim r As Long
    
'   Enter the name of all sheets to apply this code to
    wsArr = Array("Sheet1", "Sheet3")
    
'   Loop through each sheet
    For s = LBound(wsArr) To UBound(wsArr)
        ws = wsArr(s)
        Sheets(ws).Activate
'       Find last cell in column D with data
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Loop through all rows in column D
        For r = 2 To lr
'           See if column D is "NA"
            If Cells(r, "D") = "NA" Then Cells(r, "C") = "N/A"
        Next r
    Next s
    
End Sub
You will just need to update this array to include the name of all the sheets that you want to apply this code to:
VBA Code:
    wsArr = Array("Sheet1", "Sheet3")

Now, if you want something "dynamic" where if after that, someone manually enters "NA" in column D, it can automatically update column C of the same row with "N/A", then you will need to please this code in each of the sheet modules that you want it to run on.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   If cell not updated in column D, exit sub
    Set rng = Intersect(Target, Columns("D:D"))
    If rng Is Nothing Then Exit Sub
    
'   Check value of cell in column D just updated
    Application.EnableEvents = False
    For Each cell In rng
        If cell = "NA" Then cell.Offset(0, -1) = "N/A"
    Next cell
    Application.EnableEvents = True

End Sub
So if you want this to run on "Sheet1", you need to place it in the "Sheet1" module in the VB Editor.
It will NOT work automatically if you place it in a General/Standard module!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Awesome tha
OK, for the initial default population of your "master" document, you can manually run this VBA code, which will look through column D on the sheets you specify, and if the value is "NA", it will put an "N/A" in column C of the same row:
Power Query:
Sub InitialDefaultPopulation()

    Dim wsArr()
    Dim s As Long
    Dim ws As String
    Dim lr As Long
    Dim r As Long
   
'   Enter the name of all sheets to apply this code to
    wsArr = Array("Sheet1", "Sheet3")
   
'   Loop through each sheet
    For s = LBound(wsArr) To UBound(wsArr)
        ws = wsArr(s)
        Sheets(ws).Activate
'       Find last cell in column D with data
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Loop through all rows in column D
        For r = 2 To lr
'           See if column D is "NA"
            If Cells(r, "D") = "NA" Then Cells(r, "C") = "N/A"
        Next r
    Next s
   
End Sub
You will just need to update this array to include the name of all the sheets that you want to apply this code to:
VBA Code:
    wsArr = Array("Sheet1", "Sheet3")

Now, if you want something "dynamic" where if after that, someone manually enters "NA" in column D, it can automatically update column C of the same row with "N/A", then you will need to please this code in each of the sheet modules that you want it to run on.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   If cell not updated in column D, exit sub
    Set rng = Intersect(Target, Columns("D:D"))
    If rng Is Nothing Then Exit Sub
   
'   Check value of cell in column D just updated
    Application.EnableEvents = False
    For Each cell In rng
        If cell = "NA" Then cell.Offset(0, -1) = "N/A"
    Next cell
    Application.EnableEvents = True

End Sub
So if you want this to run on "Sheet1", you need to place it in the "Sheet1" module in the VB Editor.
It will NOT work automatically if you place it in a General/Standard module!
Awesome thank you! I will try it out and let you know how it goes :) Thanks again!
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
OK, for the initial default population of your "master" document, you can manually run this VBA code, which will look through column D on the sheets you specify, and if the value is "NA", it will put an "N/A" in column C of the same row:
Power Query:
Sub InitialDefaultPopulation()

    Dim wsArr()
    Dim s As Long
    Dim ws As String
    Dim lr As Long
    Dim r As Long
   
'   Enter the name of all sheets to apply this code to
    wsArr = Array("Sheet1", "Sheet3")
   
'   Loop through each sheet
    For s = LBound(wsArr) To UBound(wsArr)
        ws = wsArr(s)
        Sheets(ws).Activate
'       Find last cell in column D with data
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Loop through all rows in column D
        For r = 2 To lr
'           See if column D is "NA"
            If Cells(r, "D") = "NA" Then Cells(r, "C") = "N/A"
        Next r
    Next s
   
End Sub
You will just need to update this array to include the name of all the sheets that you want to apply this code to:
VBA Code:
    wsArr = Array("Sheet1", "Sheet3")

Now, if you want something "dynamic" where if after that, someone manually enters "NA" in column D, it can automatically update column C of the same row with "N/A", then you will need to please this code in each of the sheet modules that you want it to run on.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   If cell not updated in column D, exit sub
    Set rng = Intersect(Target, Columns("D:D"))
    If rng Is Nothing Then Exit Sub
   
'   Check value of cell in column D just updated
    Application.EnableEvents = False
    For Each cell In rng
        If cell = "NA" Then cell.Offset(0, -1) = "N/A"
    Next cell
    Application.EnableEvents = True

End Sub
So if you want this to run on "Sheet1", you need to place it in the "Sheet1" module in the VB Editor.
It will NOT work automatically if you place it in a General/Standard module!
Question? So I want to just run the first one, and I will change the array to include all the sheets I want it to inlcude, IF I do that do I still need to put it on each sheet?
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
OK, for the initial default population of your "master" document, you can manually run this VBA code, which will look through column D on the sheets you specify, and if the value is "NA", it will put an "N/A" in column C of the same row:
Power Query:
Sub InitialDefaultPopulation()

    Dim wsArr()
    Dim s As Long
    Dim ws As String
    Dim lr As Long
    Dim r As Long
  
'   Enter the name of all sheets to apply this code to
    wsArr = Array("Sheet1", "Sheet3")
  
'   Loop through each sheet
    For s = LBound(wsArr) To UBound(wsArr)
        ws = wsArr(s)
        Sheets(ws).Activate
'       Find last cell in column D with data
        lr = Cells(Rows.Count, "D").End(xlUp).Row
'       Loop through all rows in column D
        For r = 2 To lr
'           See if column D is "NA"
            If Cells(r, "D") = "NA" Then Cells(r, "C") = "N/A"
        Next r
    Next s
  
End Sub
You will just need to update this array to include the name of all the sheets that you want to apply this code to:
VBA Code:
    wsArr = Array("Sheet1", "Sheet3")

Now, if you want something "dynamic" where if after that, someone manually enters "NA" in column D, it can automatically update column C of the same row with "N/A", then you will need to please this code in each of the sheet modules that you want it to run on.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
  
'   If cell not updated in column D, exit sub
    Set rng = Intersect(Target, Columns("D:D"))
    If rng Is Nothing Then Exit Sub
  
'   Check value of cell in column D just updated
    Application.EnableEvents = False
    For Each cell In rng
        If cell = "NA" Then cell.Offset(0, -1) = "N/A"
    Next cell
    Application.EnableEvents = True

End Sub
So if you want this to run on "Sheet1", you need to place it in the "Sheet1" module in the VB Editor.
It will NOT work automatically if you place it in a General/Standard module!
The first code works for me if i put it on each sheet- Thank you so much! But when I open the worksheeet I have to tell the Macro to run for it to work. Can I use "Sub Auto open()" at the beginning to get it to run when it is opened? So far I cant get that to work for me. I have it as the beginning sub / end sub.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Question? So I want to just run the first one, and I will change the array to include all the sheets I want it to inlcude, IF I do that do I still need to put it on each sheet?
No, the first one is the one that you run manually that hits all sheets at once. You have to put that is a standard/general module, like Module1.
The second is the dynamic one that needs to be in the Sheet module of whatever sheet you want to run it against.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
If you wanted the first one to work upon opening the workbook, you would use a Workbook_Open event which MUST be in the "ThisWorkbook" module.
See: Automatically run a macro when opening a workbook

Note that all automated VBA code (aka "Event Procedure Code") MUST be in the proper, already present modules (i.e. "ThisWorkbook" and the various "Sheet" module).
Manual code usually goes in a General Module (one you add yourself by right-clicking in VB Explorer, and selecting Insert -> Module).
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
If you wanted the first one to work upon opening the workbook, you would use a Workbook_Open event which MUST be in the "ThisWorkbook" module.
See: Automatically run a macro when opening a workbook

Note that all automated VBA code (aka "Event Procedure Code") MUST be in the proper, already present modules (i.e. "ThisWorkbook" and the various "Sheet" module).
Manual code usually goes in a General Module (one you add yourself by right-clicking in VB Explorer, and selecting Insert -> Module).
Great, thanks so much for all your help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

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