False if statement offering a dropdown list

August 22, 2011 at 12:15:59
Specs: Windows 7
I need to have a formula check the value of a cell. If the value is "N/A", then I want that cell to display N/A. If there is anything else in that cell, I want the cell to offer a dropdown list for the user to choose from. I would prefer not to use VBA, but I understand that may not be possible.

Thanks in advance!

See More: False if statement offering a dropdown list

Report •

August 22, 2011 at 13:24:54
To do exactly what you asked, you will need VBA - a very simple bit of VBA, but VBA nonetheless.

However, if you would be willing to have a drop that only offered N/A when N/A was in the cell (e.g. A1) and a more extensive list when anything else was in A1, that is possible without VBA.

The problem is that changing A1 to N/A will not clear what is displayed in the cell with the drop down. It will only reduce the list of choices to N/A.

You could change the font of the cell with the drop down to be white when A1 contain N/A so that the "old" value was not visible and have N/A be only choice in the drop down.

Anything else - such as actually putting N/A in the drop down cell will require a small piece of VBA code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 24, 2011 at 06:13:06
Thanks DerbyDad03! What would the formula be to have the choices reduced? Sorry, I am pretty basic Excel user....

Report •

August 24, 2011 at 12:46:30
You can change any of these cells to whatever you want, these are just examples to show you the concepts. You can put the "extra" cells anywhere you want, even on another sheet.

1 - A1 will be the cell that will contain either N/A or the "anything else " you mentioned.
2 - B1:B4 will be the range that contains your "long" list of choices for the Drop Down.
- Select B1:B4 and name the range myRng
3 - C1 will contain the text N/A
- Select C1 and name the cell NA
4 - D1 will contain this formula:


5 - E1 will contain your Drop Down
- Set up the Data Validation using List and enter this in the Source field:


How this will work is as follows:

When A1 contains N/A, D1 will display NA. The INDIRECT formula for the Data Validation List will use the Named Range NA to populate the list. Since the range named NA consists only of C1, only N/A will appear in the Drop Down.

When anything else is in A1, D1 will display myRng and the Drop Down will be populated with the data from the range named myRng (B1:B4).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

August 26, 2011 at 06:10:03
That works great! Thanks DerbyDad03!

Report •

Ask Question