Using Named Ranges in Data Validation

A named range is a label you assign to a cell or a group of cells. Instead of writing =$A$2:$A$5 in your validation source, you write =RegionList — and Excel knows exactly what you mean.

Setting Up a Named Range for Regions

Step 1 — Add the list to the Lists sheet:

1. Go to the Lists sheet;
2. In column C, add the following:
   • C1: East
   • C2: West
   • C3: North
   • C4: South

Step 2 — Create the named range:

1. Select cells C1:C4;
2. Click the Name Box (top-left, where the cell address is shown);
3. Type RegionList and press Enter.

That's it — RegionList now points to those four cells.

Step 3 — Use it in Data Validation:

1. Go to the main sheet and select the Region column cells;
2. Open Data Validation → Settings → List;
3. In Source, type: =INDIRECT("RegionList");
4. Click OK.

The dropdown now shows East, West, North, South — pulled from the named range.

The Key Limitation

Here's where named ranges show their weakness. The range RegionList points to exactly $C$1:$C$4 — four fixed cells. If you add a fifth region, say Central, in cell C5, it will not appear in the dropdown.

Try it:

1. Go to the Lists sheet and type Central in C5;
2. Go back to the main sheet and click the Region dropdown;
3. Notice that Central is missing.

To fix it, manually expand the range:

1. Go to Formulas → Name Manager;
2. Find RegionList and click Edit;
3. Change Refers to from =Lists!$C$1:$C$4 to =Lists!$C$1:$C$5;
4. Click OK.

This works, but requires manual intervention every time the list grows.

Naming Rules to Remember

• No spaces — use underscores if needed: Region_List not Region List;
• Can't start with a number;
• Not case-sensitive, but keep it consistent;
• Can't use names that look like cell references (e.g., A1, B2).