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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Depedent Dropdown and INDIRECT($P$8) with a twist (https://www.excelbanter.com/excel-worksheet-functions/449618-depedent-dropdown-indirect-%24p%248-twist.html)

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

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

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

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

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