Indirect & substitute

[Forestq]

HI,

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

Into Legend I have:

Category (columnA)	Non Game (columnB)	Training (columnC)
Sport	Value1	Training1
Non Game	Value2	Training2
Training	Value3	Training3
Project	Value4	...
Admin	...	..

<tbody>
</tbody>

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):

ColumnC	ColumnD
=Category	=INDIRECT(SUBSTITUTE(C2," ",""))
=Category	=INDIRECT(SUBSTITUTE(C3," ",""))
=Category	=INDIRECT(SUBSTITUTE(C4," ",""))
=Category	=INDIRECT(SUBSTITUTE(C5," ",""))

<tbody>
</tbody>

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)

ColumnC	ColumnD
Sport
Non Game	Value2
Non Game	Value3
Non Game	Value1
Training	Training2

<tbody>
</tbody>

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!