Tying a Named Range to a Value Instead of a Cell

In his data, Larry has a named range that refers to cell C7. If he sorts the data, the value that was in cell C7 is now in cell C15. Larry would like the named range, after the sort, to refer to cell C15, but it still refers to cell C7. He wonders if there is a way to make a named range refer to the value in a cell (so it travels with the value) rather than to the cell itself.

No, there is no way to do this in Excel. When a name refers to a range (thus, a "named range"), then it always refers to that range, even after sorting. The range hasn't change, after all—just the values within the range have changed.

There are ways around this, however. The key is to remember that Excel allows the naming of more things than just cell ranges. Names can refer to ranges, values, or formulas. It is telling that Larry is using a named range to refer to a single cell, not a multi-cell range. Since Larry wants to always refer to the value that is originally within cell C7, he can use a name to refer to the value instead of to the cell containing the value.

Let's say that cell C7 contains a static value, such as 5 or 23 or 123.45 or "apple" or "blue." In that case, you can simply assign the value to a name, in the following manner:

1. Display the Formulas tab of the ribbon.
2. Click the Define Name tool in the Defined Names group. Excel displays the New Name dialog box. (See Figure 1.)



Figure 1. The New Name dialog box.

3. In the Name box, enter a descriptive name, such as MyValue. (Note that there can be no spaces in the name.)
4. In the Refers To box, enter the value, such as 5 or 23 or 123.45 or "apple" or "blue."
5. Click OK to add the name and close the dialog box.

Now you can use the name anywhere in the worksheet that you want. For instance, let's say that in step 4 you entered the value 23. With the name defined, you could use the following formula anywhere you want to get the result of 37:

=MyValue + 14

If you later need to change the value associated with the MyValue name, then you can use the Name Manager (within the Defined Names group of the Formulas tab of the ribbon) to modify the value. Of course, if you are doing that, and especially if you need to modify the value often, then it makes sense to rethink the logic of your worksheet. In that case, it would make sense to define a static data entry area. Then, you can refer to the cells in that data entry area in your main data. Sort the main data, and the data entry area is not affected. This approach bypasses using named ranges (or named values) completely.

If you want to stick with names, remember that they can also be associated with formulas. For instance, Larry indicated that the value he wants to always have associated with the named range is in column C. (Larry says it starts out at cell C7.) Further, let's assume that the value within cell C7 is 123. Using the steps already defined above, in step 4, place the following into the Refers To box:

=INDEX($C:$C,MATCH(123,$C:$C, 0))

Now whatever name you created (for instance, MyValue) will return the value 123, provided the value appears in column C. If the value is not in the column, then MyValue returns #N/A. Since sorting simply reorders the values within column C, then the value that started at cell C7 (123) can now be at a different location (perhaps cell C15), but the MyValue name will always return 123 because the value is still within column C.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10252) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.