Create cell name from contents of another cell

[Gatito]

I'm looking for a way to create cell names dynamically. I can find more complex answers to more complex questions, but not this one.

For example: I'm trying to name cell B1 based on the contents of cell A1. And so on: the name of each cell in column B is the contents of each cell immediately to its left (in column A). For example, if A1 contains 'Bananas' then cell B1 takes the name 'Bananas'.

I can see how this can be done in a static fashion - but the key here is that I need a dynamic method. i.e. if the contents of A1 change to 'Apples' then cell B1 is now named 'Apples'.

A wrinkle is that I'm using Excel 2011 (Mac). I will upgrade if need be.

 

[cooper645]

Place this first code in the sheet code you wish for it to work on.{Right click the sheet, view code, and paste it in}

Private Sub Worksheet_Change(ByVal Target As Range)    Dim Rng As Range, LastRow As Long


LastRow = Cells(Rows.Count, "A").End(xlUp).Row


    Set Rng = Range("A1:A" & LastRow)
    
    If Not Application.Intersect(Rng, Range(Target.Address)) _
           Is Nothing Then
                Call DelAndRenameRanges
    End If
End Sub

Place the following code in a normal module

Option Explicit

Sub DelAndRenameRanges()


Dim LastRow As Double, DefName As Name, stName As Variant


stName = ActiveSheet.Name


For Each DefName In Application.ActiveWorkbook.Names
    If InStr(1, DefName, stName) Then
        DefName.Delete
    End If
Next


LastRow = Cells(Rows.Count, "A").End(xlUp).Row


ActiveSheet.Range("A1:B" & LastRow).CreateNames Left:=True


End Sub

I cannot attest to if it will work with excel for Mac 2011, but it does in my copy of excel for Mac 2016

 

[Gatito]

Thank you! I've pasted into sheet code and a normal module respectively. Not doing anything for me yet in Excel 2011, but I'll persevere. Snappy response greatly appreciated. I'll let you know how I get on.

 

[cooper645]

If you wish, I can make the help file I created available to you here

 

[Gatito]

Well, it works perfectly. I'll eventually figure out what I did wrong (I have barely dabbled with VB/macros) but with the answer in hand I am now off to the races - and mighty grateful. Thank you, thank you, thank you.

 

[cooper645]

no problem. glad to help. good luck at the races