Name a range with BLANK

Occasional Visitor

Hi there,

Does anyone know a way to name a range as blank- i.e. with nothing? It seems excel doesn't allow this syntax for naming a range of cells, but I thought there might be a work around using some special characters or built-in excel formula?

For context:

I am making a spreadsheet where users select inputs from three drop down lists; each filtered by the previous selection. I have done this successfully by following the instructions here:

However, I would like to add blank cells as an input option for all three lists.

This is easily done for the first cell; by simply extending the named range to include a blank cell.

However, for the second and third tiers, it would require a named range to be named blank.


1 Reply

@emilymolloy You don't really need to add a blank to your validation lists. Just make sure that you have the box "Ignore blanks" checked in the Data Validation window. 

What you would want to select next if a user leaves a cell blank (or actively select a blank option). Following the example from the link you included. Say, you select Europe in the first cell, then leave Country blank in the second, would you then want the user to be able to select from all Cities in Europe, irrespective of the Country in the third? If so, please have a look at the attached file. It contains two options. On using modern Excel functions and another more along the lines of the article in the link you shared.