Include a Dropdown List in a Conditional Statement

[default_name]

Hey guys,

Here is an example/breakdown of what I am trying to do.

If C1 says Overtime, then I want D1 to display a dropdown list of the codes in (A1:A4).
If C1 is blank, then I want D1 to be blank as well.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CodeA[/TD]
[TD][/TD]
[TD]Overtime[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CodeB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CodeC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CodeD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this even possible?

Thanks in advance!

 

[C Moore]

Use a dynamic named range. Something like offset(reference [A1],rows [0], columns [if(C1="Overtime",1,0),height [4])])

 

[Eric W]

Select D1, from the Data tab, click Data Validation > Data Validation > Allow: List > Check In-cell Dropdown > and this formula in Source:

=IF(C1="overtime",$A$1:$A$4,"")

 

[default_name]

Thanks for the quick responses.

When I try to apply the formula, I get the following error:

"The list source must be a delimited list, or a reference to single row or column."

What am I missing here? Thanks again!

 

[Eric W]

Which formula are you using? If you're using the DV formula I proffered, then you could get that error if you give a 2-D range in the formula, like:

=IF(C1="overtime",$A$1:$B$4,"")

The range must be either a single column or row.

 

[KolGuyXcel]

Don't know why it's working for me!

I'm also getting the same error when the cell C1 DOES NOT CONTAIN "Overtime".
But when I'm putting "Overtime" in cell C1 and then applying the formula provided earlier in cell D1, it's getting accepted. And then if I delete "Overtime" from cell C1, the drop down list in cell D1 is showing nothing, but it's accepting any value!

 

[default_name]

Which formula are you using? If you're using the DV formula I proffered, then you could get that error if you give a 2-D range in the formula, like:

=IF(C1="overtime",$A$1:$B$4,"")

The range must be either a single column or row.

What does it mean by "a single column or row" though? Just using all of column A?
=IF(C1="overtime",A:A,"")

 

[Eric W]

Very weird. I tested that formula before posting it, and it worked fine. I can't get it to work at all today. You can try this DV formula instead:

=OFFSET($A$1,0,0,IF(C1="overtime",4,0))

When you enter it, you might get this message: "The formula evaluates to an error, do you want to continue?". Just say yes to that. A1 is the top cell containing the values of your drop down list, and the 4 is how many items are in the list.