Turning Off Names

Neil: An ever-changing range is no reason NOT to use a Defined Name - it just means you need to create a Name that dynamically changes its dimensions to match the underlying data in the target range.

As John suggests, this can be done by converting the data in the matrix to an Excel Table - though I would still create a separate Name that references the target column within the Table and then use that Name in my formulas.

Alternatively, create a Name using functions such as OFFSET and COUNTA in the RefersTo formula (after all, Names are really just named formulas) to dynamically adjust the range it references. Search the web for "Excel dynamic range name" to discover how to achieve this.

I've gotten to whenever possible I actually convert my data to a table. This way as the rows in the table increase (or decrease) using the table name+column as reference keeps all things working.

One reason to not use named ranges is when you have a continually growing list that is named and the list outgrows the limits of the named range, potentially leading to data being missing from calculations. This condition is more apparent and easier to see when the range is shown right in the formula rather than having to look in the Name Manager. This weakness can be managed by making the range longer, if possible, than you can imagine ever needing.

This tip immediately brings up in my mind a bigger question. Why would you ever want to NOT use names? Named Ranges/cells is one of the most underutilized power tools in Excel. I've got one particular workbook that uses Names and all the formulas can easily be read like any standard math equation. With cell references they're just so much gobbledygook.

There is a solution for this using VBA. The trick is to replace the range names in the formulas by the range addresses before deleting the names. The macro could look something like this:

Sub RemoveNames()

Dim Rng As Variant 'a named range
Dim Nme As String 'the name of the range
Dim Addr As String 'the range address to be used in the formula
Dim Sh As Variant 'a sheet
Dim Sheetnm As String 'the sheet name

For Each Rng In ActiveWorkbook.Names
Nme = Rng.Name
For Each Sh In ActiveWorkbook.Sheets
Sheetnm = Sh.Name
Addr = Trim(Mid(Rng, Len(Sheetnm) + 3, 50))
Sheets(Sheetnm).Cells.Replace What:=Nme, Replacement:=Addr, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next Sh
ActiveWorkbook.Names(Nme).Delete
Next Rng

End Sub

Before deleting the name, look up the address for the defined name in the name manager. Use search and replace to replace the name of the range in the formulas that use it. Then delete the defined name

Thank you