Removing Spaces from Defined Names So that INDIRECT Still Works

Thanks! Really glad I found this forum
Almost got it working, where would i put the substitute function to have it affect the items in the list drop down?

I've got two sheets; "Sheet 1" and "Lists"
I'm getting the items for the first drop down from "=Lists!$B$3:$N$3" but some of the items have underscores i.e. "Floor_Hours".
Just can't figure out where the SUBSTITUTE function should go to remove the underscores from the items when they appear in the first drop down list haha.. does it need to be added when setting up the data validation?

Apologies for not being able to explain it very well!

Many Thanks

thanks

I mustn't have something set up correctly because it just evaluates in an error every time i try to set it up.

If i have multiple ranges that are being used by the INDIRECT function, and those ranges have names with underscores instead of spaces so that they link to the items in the first list, how should everything be set up?

Example
Blue_Dye <- data range name in cell B3 on the sheet named "Lists"
Dye 01
Dye 02
Dye 03

Red_Dye <- data range name in cell D3 on the sheet named "Lists"
Dye 04
Dye 05
Dye 06

so the first drop-down now shows the two items Blue_Dye and Red_Dye, from the DV of =Lists!$B$3:$D$3, and when i choose one of those the second drop down will allow me to choose from the data range defined as either Blue_Dye or Red_Dye.
Where should I put the SUBSTITUTE function so that the first drop-down displays the data range names without underscores?

Cheers!

Oh!
Learning how to use the forum at the same time haha, thanks!
File attached.
You'll see two sheets in the file, I've set up the defined range names, the drop-down lists and the INDIRECT function.
The drop-down list in Column B (Example_1 to Example_5) is what I would like to remove the underscores from so that the items in the list in Column B are displayed without underscores (with spaces instead)
but still work with the relevant data ranges which require the names to match exactly so that they will appear in the Column B list.

Many Thanks!

Your list in B3:F3 already has the underscores in - do a Find & Replace to change those to spaces. Now you will see "Example 1", "Example 2" and so on in the drop-down for cell B5 in the Example sheet. Click on C5, then pull up the data validation dialogue and change the List Source to:

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

So now you can have your list in B5 showing spaces, but your named ranges have underscores.

Hope this helps.

Pete

Genius!
Thanks so much
I couldn't get my head around where to put the SUB but now it all makes sense.. seems so obvious once it all falls into place haha
I was trying to put the SUB in with the Column B DV but now i see that it has to be in with the INDIRECT so that it doesn't create a circular reference and will work!

Thanks Pete, saved me

In order to remove the underscores from the defined names I understand how to use this formula (as an example): =INDIRECT(SUBSTITUTE($E11," ","_"))

Can someone tell me where to put the SUBSTITUTE formula in this string that was designed to allow the use of asterisks in a list but I also want to remove underscores?
=INDIRECT(VLOOKUP($G2,COM_bLookup,1,0)&"List")

Thanks so much.

@swestin,

The mods are quite strict in applying the Forum Rules (you can read them at the top of the screen), and rule 02 states that you should not hijack someone else's thread - instead, you should start your own thread, and provide a link back to this thread if you think it is relevant.

I would also suggest that you attach a sample Excel workbook in your new thread. To do this, click on Go Advanced (below the Edit Window) while you are composing a post, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Thanks guys. Newbie.