Defind a Named Range based on a cell value

[Trachr]

My gut is saying this is impossible, but I figured I could ask since it will make my life easier if it can be achieved


Say I have a column B10-B20 and the user enters some value into some or all of these cells

Now is it possible to have a named range that the value would =B10 but also that the name itself of that item would also be dynamic so say someone enters "widget" into B10 the named range would be something like: =CONCATENATE(something,B10)

Like I said I doubt its feasible but would be really nice if it can be managed.



Thanks.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Trachr]

Sure, Basically in this example I will have Columns B,C,E COlumns D and F are what I would like the named range to be for the cell to its left So If possible I would have C6 have a name of D6 and E7 having a name that is F7

I wont know the names people enter if need be I'll make the names be like APIIDuser1 or APICODEuser2 etc but I'm just trying to see if there is a way to get the contents of Columns B into the named range.

[Trachr]

well I have managed what I want to do after a fashion... I still have a few issues but this is what I've managed so far.

Option Explicit

Dim OldValue As String                                  ' Variable Needed to Remove old Named Ranges


Private Sub Worksheet_Change(ByVal Target As Range)
       
     
    If Target.Address = "$B$11" Then                    ' Overall Range Naming for API Key
     
        If Target.Cells.Count > 1 Then Exit Sub             ' Makes sure there is only 1 cell changing

        If Target <> "" Then                                ' This IF Sets Range Name only if Cell is Not Blank
        
            Dim strName As String                               ' Defines Variable
        
            strName = Replace(Target, " ", "_")                 ' Removes Spaces from Target Value
            Range("C11").Name = "EveAPI3_" & strName            ' Sets Range Name
        
        End If                                              ' End Range Nameing
                
        If OldValue <> "" Then                              ' This If Deletes the Old Range Name, If there was one
            
            Dim oldName As String                               ' More Variables
            Dim oldName2 As String                              ' And More Variables
            
            oldName2 = Replace(OldValue, " ", "_")              ' Removes Spaces from Old Value
            oldName = "EveAPI3_" & OldValue                     ' Sets what the Old Range Name was
            
            On Error Resume Next                                ' Skips over if there is an Error
            ActiveWorkbook.Names(oldName).Delete                ' Deletes the Old Range Name
            
        End If                                              ' End Range Name Deleting
     End If                                             ' Ends API Key Range Naming

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Selection.Cells.Count > 1 Then                       ' Triggers If Statement if more than 1 cell is selected
        MsgBox "Please select only one cell", vbCritical        ' Error Message
        ActiveCell.Select                                       ' Removes Multiple Selection
    End If                                                  ' End If
    
    If Target.Address = "$B$11" Then                        ' If Statement checking value of Cell before Change
        OldValue = Target                                       ' Sets Variable
    End If                                                  ' End If

End Sub

I still need to apply this to more than 1 cell, preferably a range of cells or even a full column