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
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I know it does that, which is why I said

Locking the cell will make no difference.
Sorry I didn't see that part- I was too excited that it worked :D I will try another way and post my issue in another topic, since it will be a different formula

Thanks for your help I appreciate you ;)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
A cell can either contain a formula or a hard-coded value, but never both at the same time, and it has no memory of what was in it before.

If you want to do something that can handle both, that would require VBA code.
Ok thanks, I was afraid of that. Will you know what the VBA code would be? Would I be able to post it in multiple sheets?

Thanks
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
A cell can either contain a formula or a hard-coded value, but never both at the same time, and it has no memory of what was in it before.

If you want to do something that can handle both, that would require VBA code.
Maybe...IF VBA code is too much, could I just have NA be the default value in the dropdown list? - So then it wont change unless they pick something else from it? So no blanks...just NA or something else in the list?
 

Joe4

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

ADVERTISEMENT

How/when is column D being populated?
Is someone manually populating the values of "NA" in column D?

I think in order for us to give you the best answer, we need to know:
1. What does/should column C and D look like in the beginning, before anyone has done anything to it?
2. How are people updating columns C and D, and what should the order be, and what should happen in each situation.

You kind of have given us "bits and pieces" about some of this information, but we really need to understand the "big picture" and the workflow to give you the best suggestion.
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
How/when is column D being populated?
Is someone manually populating the values of "NA" in column D?

I think in order for us to give you the best answer, we need to know:
1. What does/should column C and D look like in the beginning, before anyone has done anything to it?
2. How are people updating columns C and D, and what should the order be, and what should happen in each situation.

You kind of have given us "bits and pieces" about some of this information, but we really need to understand the "big picture" and the workflow to give you the best suggestion.
OK I will upload a sheet that shows what it look like. This is just the part that I need the help with. - Will you let me know how I can upload the mini sheet? I downloaded the free plugin
 

Joe4

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

ADVERTISEMENT

OK I will upload a sheet that shows what it look like. This is just the part that I need the help with. - Will you let me know how I can upload the mini sheet? I downloaded the free plugin
There is a complete write-up right here: XL2BB - Excel Range to BBCode
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
There is a complete write-up right here: XL2BB - Excel Range to BBCode
Thanks. My IT admin has blocked all macros :( Wil this picture help? The D column is prefilled for them, and determines whether they must answer that question or not (NA is they don't have to answer it and it is greyed out, otherwise they must answer by picking from the dropdown list.

Excel.JPG
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
How many sheets are there in your workbook?
Do they all follow this exact same structure?

Is this a template that is being set up once, and then distributed to users (so column D would not be changed after your initial set-up)?
If so, then we only need to worry about pre-populating column C in this initial set-up, right?
Then you wouldn't need to worry about formulas or defaults after initial set-up.

When just need to understand how the timing of everything to determine the best course of action.
 

Data2link

New Member
Joined
Jan 12, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
How many sheets are there in your workbook?
Do they all follow this exact same structure?

Is this a template that is being set up once, and then distributed to users (so column D would not be changed after your initial set-up)?
If so, then we only need to worry about pre-populating column C in this initial set-up, right?
Then you wouldn't need to worry about formulas or defaults after initial set-up.

When just need to understand how the timing of everything to determine the best course of action.
There are 7 sheets -4 that they are required to answer the question (all different) in the same column. Column C contains the dropdown menu on all sheets, and column D is the same on all sheets, but it just might not need to answer it. Column D will either contain an M, C, or NA in it. It will be setup once as a master file, then sent to multiple sites for them to fill out and send back. So I only need the setup to be in the Master file. Column D might change depending on a sheet that they fill out in the beginning.
 

Forum statistics

Threads
1,144,342
Messages
5,723,824
Members
422,518
Latest member
quack_quack

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