Data Validation List.

[lukus2005]

Thanks to the help of some in here, I've been able to create some
dependent dropdown lists however, I didn't anticipate the following
problem when I posted my question weeks ago.

I have 4 dependent dropdowns (4 columns). However, the 4th dropdown
may not be required in some instances depending on what option is
selected.

How do I make it so that if option X is selected, it won't activate
the 4th pulldown but instead, perform the VLOOKUP function.

Also, is it possible to have the pulldown always visible (just the
arrow, not the list itself) and not just when you click on that cell?
Even better, is if option X is selected, the 4th pulldown would
disappear.

I know nothing about macros so if this can't be accomplished with
regular menu options and formulas, then I'll need a workaround.

TIA

 

[D8.Consultor]

You could use IF or CHOOSE within validation list origin field in
order to achieve this.

I used a formula like this within the cell you want to make optional,
first select Validation->List. In the Origin field type something like
"=IF(optionX="X"; OptionsList; single)"

where "single" is an external range of one cell with the following
formula =IF(optionX="X"; VLOOKUPcalculation,0)

Where "VLOOKUPcalculation" is the formula you want the cell to
calculate when option X is selected.

In fact, by using the described method you could have a single cell
where the validation list changes according to different logical
checks done in the model

 

[hall.jeff]

Thanks to the help of some in here, I've been able to create some
dependent dropdown lists however, I didn't anticipate the following
problem when I posted my question weeks ago.

I have 4 dependent dropdowns (4 columns). However, the 4th dropdown
may not be required in some instances depending on what option is
selected.

How do I make it so that if option X is selected, it won't activate
the 4th pulldown but instead, perform the VLOOKUP function.

Also, is it possible to have the pulldown always visible (just the
arrow, not the list itself) and not just when you click on that cell?
Even better, is if option X is selected, the 4th pulldown would
disappear.

I know nothing about macros so if this can't be accomplished with
regular menu options and formulas, then I'll need a workaround.

TIA

Let me answer your second question first... no... to my knowledge
there is no way to make the arrow always show up

As for the first, the best you can do without some crazy macros that
fire everytime the sheet changes is to do something like this

define name:
dynamic_range = if(condition,validationrange1,validationrange2)
then make your list range dynamic_range rather than A1:A4
you put the vlookup in validationrange1 and then validationrange2 can
be A1:A4

note: using defined names will let you put the validation lists on
other tabs (something you can't normally do)