Naming a Range with Spaces

Hi Sky188 and welcome to the forum,

I think you have a valid way of replacing a space with underscore. With a small sample worksheet uploaded, perhaps there is a different way to get the second pulldown to work better. It might take some filters or VBA code, but let us look.

To upload a file, click on "Go Advanced" under the message area and then the PaperClip Icon above the message area. This will open a window asking for a file to attach.

I like basketball.

Originally Posted by Sky188

Right now im using the function

=INDIRECT(SUBSTITUTE(C19," ","_",1)

where C19 is where John Stockton would be and I am trying to replace the space (" ") with an underscore, as that is what I have named the hidden data names.

Any ideas on what the correct function would be?

The function above is ok in theory assuming there is only 1 space in C19 (no leading/trailing spaces).

However, assuming single space, the above will still fail to work if the referenced range (John_Stockton) is itself Dynamic - this is a standard limitation of INDIRECT.

If the RefersTo for John_Stockton includes OFFSET, INDIRECT or a range:INDEX approach then I'm afraid you can't use INDIRECT to handle it.

In these instances you will need to either:

a) use an IF or CHOOSE based construct in the Data Validation Source in C

b) use Evaluate (old XLM call) via another Name and reference this new name in C

Attached is a basic example of these methods