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 :

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Hi Howard,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, December 16, 2013 3:07:12 AM UTC-8, Claus Busch wrote:
Hi Howard,



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



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.

--

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