Data Validation Cell Based on another Data Validation Cell, using Table

[lhannah]

I have the following table A1:B7.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64 height=17>Orange</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Green</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>O</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">G</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>R</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">R</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>A</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>N</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>G</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">N</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>E</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD></TR></TBODY></TABLE>

Cell D1 has a data validation list that shows either "Orange" or "Green". (A1:B1)

I want cell E1 to have a data validaiton list showing "O". "R", "A", "N", "G", "E". if D1 is "Orange". Or "G", "R", "E", "E", "N" if D1 is "Green". How do I do this?

Thank you!

 

[Aladin Akyurek]

Select A2:A7 and name it _Orange via the Name box and B2:B7 _Green.

Data validate E1 as List, with

=INDIRECT("_"&$D$1)

as Source.

 

[lhannah]

That worked perfectly. Thank you!

 

[T. Valko]

That worked perfectly. Thank you!

Just so you know, you don't need those underscores.

Just adds more obfuscation.

 

[Aladin Akyurek]

That worked perfectly. Thank you!

You are welcome. Thanks for providing feedback.