CODEWITHSUNDEEP
excelexcel-formula
Doidel
Doidel

Reputation: 319

excel dropdown with address as result

A normal data validation dropdown in Excel results in the selected value being put into the cell. In my case though, I am referencing another list in my sheet whose elements can change. My goal is to make those changes apply to already selected dropdown items.

Example: Referenced list in dropdown (sheet "List"):

User selects A from the dropdown in sheet "Selection":

Now the user changes A to Y in sheet "List":

The user's selection in sheet "Selection" still shows A, but it should now show Y:

Is this possible in any way? Can I e.g. make the dropdown result in an address to the value, instead of the value itself?

Thanks!

Upvotes: 1

Views: 82

Answers (2)

JvdV
JvdV

Reputation: 75990

There unfortunately isn't any way to do this with a formula or build-in function (that I'm aware of)

Here is something simple you could apply and work with:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target(1, 1), Range("A1:A3")) Is Nothing Then
        ActiveWorkbook.Sheets("Selection").Range("A1").Value = Target(1, 1)
    End If
End Sub

Assuming Range("A1:A3") is the list you are refering to. Paste this under your List sheet.

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54983

Drop Down feat. Worksheet Change Event

  • To 'copy' your setup, in worksheet List I have created a name Drop1 which refers to the column range containing the values. Then I have created a Validation Drop Down in B2 in worksheet Selection and chose the name (Drop1) as the list.
  • Change the constants (Const) to fit your needs.

Module1

Option Explicit

Public strListSheet As String
Public strListRange As String
Public vntList As Variant

Sub Drop(rngList As Range)

    Const cDropSheet As String = "Selection"
    Const cDropRange As String = "B2"

    Dim rng As Range
    Dim vntNew As Variant
    Dim vntVal As Variant
    Dim Nor As Long
    Dim i As Long

    Set rng = ThisWorkbook.Worksheets(cDropSheet).Range(cDropRange)
    vntVal = rng
    vntNew = rngList
    Nor = UBound(vntList)

    For i = 1 To Nor
        If vntList(i, 1) = vntVal Then
            If vntVal <> vntNew(i, 1) Then
                rng = vntNew(i, 1)
            End If
            Exit For
        End If
    Next

    vntList = vntNew

End Sub

Sub Initialize()

    Const strDrop as string = "Drop1"

    Dim str1 As String
    Dim lngInStr As Long

    ' Prepare
    str1 = Names(strDrop).RefersTo
    lngInStr = InStr(1, str1, "!")

    ' Write Public Variables
    strListRange = Right(str1, Len(str1) - lngInStr)
    strListSheet = WorksheetFunction.Substitute(WorksheetFunction _
            .Substitute(Left(str1, lngInStr - 1), "=", ""), "'", "")
    vntList = Worksheets(strListSheet).Range(strListRange)

End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    Initialize
End Sub

List (Worksheet)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrInit
    If Target.Cells.Count = 1 Then
        Dim rngList As Range
        Set rngList = ThisWorkbook.Worksheets(strListSheet) _
                .Range(strListRange)
        If Not Intersect(Target, rngList) Is Nothing Then
            Drop rngList
        End If
    End If
Exit Sub

ErrInit:
    MsgBox "An unexpected error occurred. Error '" & Err.Number & "':" _
            & Err.Description, vbCritical, "Error"
    On Error GoTo 0
    Initialize

End Sub

Upvotes: 1

Related Questions