Dynamic pulldown of sheet names

[ShadowRider]

I would like to create a dropdown that includes the names of all the sheets in the current workbook except the current one. In my case, I have 4 sheets labeled Sheet 1, Sheet 2, Sheet 3, and Calculations. I'd like to be able to select a sheet in the drop down, and have the value of the cell of the selected sheet put into a cell on the current sheet. I've tried to create examples with xl2bb:

Book1
	A
1	1
Sheet1

Book1
	A
1	2
Sheet2

Book1
	A
1	3
Sheet3

Book1
	A	B
1	Selected Sheet	Value in A1
2
3	In cell A2 of this sheet, I'd like a drop down that contains the names of all sheets except 'Calculations' and I'd like the drop down to be dynamic so that when a new sheet is added, the drop down list will include it.	In B2, I'd like the value of A1 in the sheet that is selected in the cell A2 of this sheet
Calculations

Thanks in advance.

 

[rpaulson]

right click on the sheet that you want the drop-down in. select view code, and paste all the below into the module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
        Call CreateSheetNamesDropDown
    End If
End Sub

Sub CreateSheetNamesDropDown()
    Dim ws As Worksheet
    Dim sheetList As String
    Dim dropDownCell As Range

    Set dropDownCell = ActiveSheet.Range("A2")
    
    For Each ws In ThisWorkbook.Sheets
       If ws.Name <> "Calculations" Then sheetList = sheetList & ws.Name & ","
    Next ws
    
    sheetList = Left(sheetList, Len(sheetList) - 1) ' Remove the trailing comma
    
    ' Apply Data Validation to the chosen cell with the sheet names
    With dropDownCell.Validation
        .Delete ' Remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sheetList
  
    End With
    
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
        Call DisplayMessage
    End If
End Sub

Sub DisplayMessage()
    [B1] = Worksheets([A2].Value).[A1]
End Sub

 

[ShadowRider]

I used most of what you have but I only had the task for b2 as an example of wanting to pull items with indirect. For that, I used
=INDIRECT("'"&A2&"'!A1")
instead which seems to work and I can expand for my obtaining other data on those pages.

Thank you very much.