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

  • Thread starter Thread starter lhkittle
  • Start date Start date
L

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
 
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)
Click to expand...

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


Regards
Claus B.
 
if I understand you then try in N8:

="s"&P8





Regards

Claus B.
Click to expand...

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
 
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.
Click to expand...

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.
 
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
Click to expand...


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

Thanks, Claus.

Regards,
Howard
 
Back
Top