Indirect & substitute

[Forestq]

HI,

I have sheet1(called "Timesheet") and sheet2("Legend").

Into Legend I have:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Category (columnA)[/TD]
[TD]Non Game (columnB)[/TD]
[TD]Training (columnC)[/TD]
[/TR]
[TR]
[TD]Sport[/TD]
[TD]Value1[/TD]
[TD]Training1[/TD]
[/TR]
[TR]
[TD]Non Game[/TD]
[TD]Value2[/TD]
[TD]Training2[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]Value3[/TD]
[TD]Training3[/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Value4[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Admin[/TD]
[TD]...[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]

Into Manager Name I have 3 basic list:
Category =Legend!$A$2:$A$7
Non_Game = Legend!$B$2:$B$4
Training = Legend!$C$2:$C$5

Now, into Timesheet into ColumnC & ColumnC I want to have data validation (as list):

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]=Category[/TD]
[TD]=INDIRECT(SUBSTITUTE(C2," ",""))[/TD]
[/TR]
[TR]
[TD]=Category[/TD]
[TD]=INDIRECT(SUBSTITUTE(C3," ",""))[/TD]
[/TR]
[TR]
[TD]=Category[/TD]
[TD]=INDIRECT(SUBSTITUTE(C4," ",""))[/TD]
[/TR]
[TR]
[TD]=Category[/TD]
[TD]=INDIRECT(SUBSTITUTE(C5," ",""))[/TD]
[/TR]
</tbody>[/TABLE]

Result should be as below:
ColumnC -> working OK.
Column D -> donesn't work

If I selected Non-Game, I should got list: Value1, Value2, Value3 or If I selected Trainig I should have (list Training1, Training2, Training3)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD]Sport[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Non Game[/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Non Game[/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Non Game[/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]Training2[/TD]
[/TR]
</tbody>[/TABLE]

Do you know why function =INDIRECT(SUBSTITUTE(C2," ","")) doesn't work correctly?

 

[XOR LX]

Hi.

Works ok for me. Obviously not for Non_Game, though, since your list contains "Non Game" with a space, whereas the Named Range has an underscore.

Regards

 

[Forestq]

thanks!