Google Sheets – Display Named Ranges with Spaces

formulasgoogle sheetsgoogle-sheets-data-validationgoogle-sheets-named-ranges

Sample spreadsheet here.

In the spreadsheet, there is a dependent drop-down list in the first column, which automatically populates a data validation for the second column using the named range from the first column. This works fine, but any named range with space (e.g. Eating Out) has to be displayed as (Eating_Out or Eating.Out) since named ranges do not accept spaces. Is there any sort of workaround for this?

Best Answer

No, there is no workaround for getting a named range with space.

Range names:

  • Can contain only letters, numbers, and underscores.
  • Can't start with a number, or the words "true" or "false."
  • Can't contain any spaces or punctuation.
  • Must be 1–250 characters.
  • Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give your range a name like "A1:B2" or "R1C1:R2C2."

But, there is a workaround for it with auxiliary column and formula:

=IF(A2="Eating Out", {F2:F},
 IF(A2="Misc",       {G2:G},
 IF(A2="Groceries",  {H2:H}, )))

0

Related Topic