Need help in using INDIRECT, SUBSTITUTE, and OR in a formula fordependent dropdown list

[PeeGee]

Hi,

I am using the Substitute function together with Indirect function when doing a dependent dropdown list. The formula "as is" works fine (see below sample data) where I am replacing a space with "_" so the selection items matches with the named range.

=INDIRECT(SUBSTITUTE(A14," ","_"))

However, one of the items in the first dropdown list should actually have the character "/" (see Source2 below).

I tried using the function OR in the formula (is this possible?) it did not work. Here is the formula that I tried: =INDIRECT(SUBSTITUTE(OR((A11," ","_"),(A11," "/"_")))).

I would appreciate any help.

Thanks,
PeeGee

Sample Data (I wanted to attach a sample file but I did not see an option):

Source 1

Color (range is named as color):
Blue Green
Yellow Orange
Red Purple

Blue Green (range is named as blue_green):
Atlantic Blue
Royal Blue
Sea Green

Yellow Orange (range is named as yellow_orange):
Sun Yellow
Neptune Orange
Moon Orange

Red Purple (range is named as red_purple):
Red Apple
Purple Yam

Source 2 (for use with the "/")

Color2 (range is named as color2):
Blue Green
Yellow Orange
Red/Peach Purple

Blue Green (range is named as blue_green):
Atlantic Blue
Royal Blue
Sea Green

Yellow Orange (range is named as yellow_orange):
Sun Yellow
Neptune Orange
Moon Orange

Red Peach Purple (range is named as red_peach_purple):
Red Apple
Purple Yam

 

[Claus Busch]

Hi,

Am Thu, 5 Feb 2015 10:38:46 -0800 (PST) schrieb PeeGee:

=INDIRECT(SUBSTITUTE(A14," ","_"))

However, one of the items in the first dropdown list should actually have the character "/" (see Source2 below).

try:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(A1," ","_"),"/","_"))


Regards
Claus B.

 

[PeeGee]

Claus,

Brilliant. Works perfectly.

Thank you so much!
PeeGee