Data Validation - Excel Dependant List Doesn't Work

[zanshin777]

I created a dependant list on Exercises B2. However It doesn't work.

How to fix it?

The cells on the sheet Exercises refer to;

C2 = Dependant List Row1

B2 = Dependant List The Data Below The Row1

[BMV]

You try to use option with named range. Every value from c2 must have the range named as this values. for example
Intellectual - and range ='Dependant List'!$D$2:$D$4

You can use =INDIRECT("Table2["&C2&"]") also but many blank rows will be in the list.

The other solution is create dinamic range with OFFSET function that build range depend on C2 value.
but in name

Please Login or Register  to view this content.

and this name to Data validation.

[zanshin777]

I'm trying to understand the formula "_exercDD" you created on the name manager.



1) I don't understand the reference you wrote in the first INDIRECT function at all. Could you explain it in details please?

I mean Using of those characters confused me.

1.1) " ?
1.2) [ and ] ?
1.3) [Category] ?
1.4) & ?

("Table2["&Table3[Category]&"]")

2) After 3 "," 's there is 4th argument of the OFFSET function. According to syntax it is [height]. Could you explain me what it does please?

[dangelor]

See attached...

[BMV]

I'm trying to understand the formula "_exercDD" you created on the name manager.

("Table2["&Table3[Category]&"]")

Surry i made mistake and corrected it. see prev. msg.

Indirect function returns the reference specified by a text string, The text string is "Table2["&Table3[@Category]&"]"
Table3[@Category] - the reference to cell in the same row where formula placed and to column named Category or column C in your case. It is regular smart table syntaxes
if you evaluate it it is "Table2[Guitar]" as example and INDIRECT("Table2[Guitar]") is reverence to column Guitar of Table2.
I change range by offset for only rows with value. COUNTA return this quantity.

[zanshin777]

I understand what you did on the file v2.

Thank you very much dangelor.

I appreciate your help a lot.

[zanshin777]

Thank you very much BMV. I'll check table syntaxes and return that formula again.