Forum Discussion

707lineman's avatar
707lineman
Copper Contributor
Jun 29, 2025

Named Range help

I need some help with my forumlas as I can't get them to work correctly

 

So on MasterList sheet I have my data which i constantly add to.

First Named Range across the top From A1-N1

Next Named Range goes from B2-B100

I need help with Named Ranges having spaces in them which I need.  for example CLR-Lands Named Range,  I need in the Dropdown on multiple sheets to have CLR Lands with a space, I am using indirect for independent dropdown list which the space breaks that function

 

Excel for web

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 30, 2025

    Use underscores _ in the name of the named range, for example CLR_Lands (hyphens - aren't allowed).

    Let's say you have a drop-down in A2 with a list that contains values such as CLR Lands.

    In a cell next to it, you can use

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

    as source for data validation of type List.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 30, 2025

    A named range can NOT have a space in it.  If you want the user to select from a list of 'names' and those names may have one or more spaces in them then I suggest you do a SUBSTITUTE( X, " ", "_") on that selection and then make sure you use and underscore ( _ ) character in your names in place of the spaces (or pick whatever character you wish)

    so you formula would be like:

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

    and of course I would consider just avoiding the whole INDIRECT() if possible.  for example if the names are columns in the table then you can have a lookup table relating name to column number or use XMATCH for that name in the column header.

Resources