How to convert a value in Excel cell on one sheet to show as a dropdown in another

Question

My C# code returns me a value shown in block A below in Sheet1. In sheet2, I need the data to be shown as a dropdown list as shown in block B. Is there an Excel formula to achieve this?

Answer 1

This is a VBA Macro.

It uses cell A1 in Sheet1 and cell B9 in Sheet2

Sub demo()
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Sheet1").Range("A1")
    Set r2 = Sheets("Sheet2").Range("B9")
    With r2.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=r1.Value
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

It is not a function, if the value in A1 changes, the sub must be re-run.