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


P

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
 
Ad

Advertisements

C

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dropdown List Help 4
Indirect formula 2
Help with Xl and dropdown lists 0
Use of Indirect 3
Using INDIRECT 1
Dropdown lists 9
DropDown list 1
Dropdown list 6

Top