Dependent drop-down

**jpresigjr** · 02-16-2012, 04:24 PM

Hey all,

Second post here. The first one was solved quite nicely so I'm back for more!

I have a cell that I MIGHT want to populate from a drop down menu but only if another cell has a value within a specified range. That is to say...

Given drop down menu in A1 selected to equal 5, Cell B1 should automatically display a value determined by a VLOOKUP function.
If cell A1 has a value entered that is not from the drop down menu, cell B1 should then present its own different drop-down list.

Not sure it is possible but if anyone knows, I'll find them here.

Thanks in advance.

-JR

**JBeaucaire** · 02-16-2012, 05:20 PM

That's actually two different things going on at once. For the first you construct a formula for that cell:

=IF(A1=5, MyVloopupFormulaHere, "")

I'm not sure I follow all the parameters of the drop down test, but you could use Data Validation>List in that cell to conditionally present a dropdown.

Data Validation>List> Source: =IF(A1<>5, MyList)

**jpresigjr** · 02-16-2012, 06:08 PM

Thanks for the response, JBeaucaire. Essentially I am trying to have the option of either the list and the vlookup in the same cell (if that is at all possible). I've attached a VERY simple example of what I want to do. The problem is that I can't figure out how to get it into a single cell.

In row two I have entered in custom text and manually selected from a drop down list. In row three I selected from a drop down and had column B populate through the vlookup function. I'd like to be able to have column B auto-populate if row a is in the vlookup matrix and have it provide a drop-down menu if it is not in the vlookup matrix.

I guess I should also find out...is it possible to enter custom text into a cell that has a list enabled? That might throw another wrench into the works for me.

Thanks again for any help.

-JR

Sample.xlsx

**JBeaucaire** · 02-16-2012, 07:55 PM

You need to go to the ERROR tab on your DV and turn off the errors so that you can have/edit formulas in a cell with DV on it.

So, column A I turned off the errors, so you can use the drop down or type your custom text. If you use the drop down, the formula in column B will try to give you a result, which it should succeed at doing. If you type something unique, though, column B will remain blank and as long as the formula is returning a blank value, the data validation drop down will have its choices.

Keep in mind this symbiosis comes at a price. The error checking is off on the data validation, and if you ever use the drop down in a cell in column B, the formula that was in that cell will be gone forever, or until you copy over the the cell to restore it from another cell.

**jpresigjr** · 02-17-2012, 10:59 AM

JBeaucaire,

Thanks so much. I couldn't quite get exactly what you showed to work every time (even though yours did). For some reason in my spreadsheet, the dropdown in column B wasn't available when custom text was entered into column A. So, I just put an identical VLOOKUP evaluation in two separate cells (one in column B and one to be hidden) and had the data validation reference the hidden cell rather than itself. It isn't quite as elegant but it will work for me.

Thanks again for all of your help.

JR

**JBeaucaire** · 02-17-2012, 03:58 PM

You can post your file and we'll look at what you tried, see what is wrong. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.