Depedent Dropdown and INDIRECT($P$8) with a twist

[lhkittle]

Three data validation drop downs, N8, P8, R8.

P8 List Source is =Long_Def_Subjects, which will give choices of Math, Science and Reading etc.

R8 List source is =INDIRECT($P$8) and when a selection is say, MATH in R8 it produces something like:

CCSS.Math.Content.5.OA.A.1 Use parentheses, brackets, or braces in numerical expressions, and evaluate expressions with these symbols.

(Hence the term Long_Def_Subjects for P8)

I have a range named Short_Def_Subjects which will give choices of sMath, sScience and aReading etc. If sMatch were choosen somewhere it would show a list like this.

CCSS.Math.Content.5.OA.A.1
CCSS.Math.Content.5.OA.A.2
CCSS.Math.Content.5.OA.B.3

(Hence the term Short_Def_Subjects)

So if P8 has MATH selected, R8 is working fine using the =INDIRECT($P$8) formula, is there a formula I can use in N8 something like:

="s"&INDIRECT($P$8)
=INDIRECT("s"&$P$8)

to bring sMath to N8. (Neither work)

Both N8 and R8 need to key off the selection in P8 to bring up the proper corresponding selection for both.

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 16 Dec 2013 00:34:09 -0800 (PST) schrieb (e-mail address removed):

So if P8 has MATH selected, R8 is working fine using the =INDIRECT($P$8) formula, is there a formula I can use in N8 something like:

="s"&INDIRECT($P$8)
=INDIRECT("s"&$P$8)

to bring sMath to N8. (Neither work)

if I understand you then try in N8:
="s"&P8


Regards
Claus B.

 

[lhkittle]

if I understand you then try in N8:

="s"&P8





Regards

Claus B.

Works in a normal cell, but in the source window of the drop down, I get an error List source must be a delimited list or a reference to a single row or column.

If I could get it to say the same in the source window as a sheet cell I believe it would fly, maybe not.

I tried other items in the Allow window, Text Length and Custom don't error but don't work either.

I may have to think of some other way.

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 16 Dec 2013 01:30:10 -0800 (PST) schrieb (e-mail address removed):

Works in a normal cell, but in the source window of the drop down, I get an error List source must be a delimited list or a reference to a single row or column.

you can't refer with INDIRECT to 2 different ranges.
=INDIRECT(P8) creates the list for R8
For N8 you have to do it in the cell or with VBA.


Regards
Claus B.

 

[lhkittle]

Hi Howard,



Am Mon, 16 Dec 2013 01:30:10 -0800 (PST) schrieb (e-mail address removed):






you can't refer with INDIRECT to 2 different ranges.

=INDIRECT(P8) creates the list for R8

For N8 you have to do it in the cell or with VBA.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Okay, good to know that. I think I have a solution using the LEFT function.

Thanks, Claus.

Regards,
Howard