Worksheet Names in cells

[mniedert]

I want to create a Validation list which uses all of the Worksheet names in my spreadsheet. So for example, if I have 12 worksheets I would have the 12 worksheet names listed in order from A1 to A12. Is there a formula that would allow me to do that?

 

[RCBricker]

I want to create a Validation list which uses all of the Worksheet names in my spreadsheet. So for example, if I have 12 worksheets I would have the 12 worksheet names listed in order from A1 to A12. Is there a formula that would allow me to do that?

you can with VBA

you will need to paste the following code into the "thisworkbook" module of your workbook.

steps to do this

1. press ALT+F11 (this will take you to the VBE window
2. Double click the 'ThisWorkBook' module (upper left window section will have a list under 'Microsoft Excel Objects' it will be the last one in the list)
3. Paste the code
4. save

This should do the trick.

Private Sub Workbook_Open()
Dim wsLIST As Worksheet, ws As Worksheet
Dim i As Integer
Dim lngROW As Long
    Set wsLIST = Sheets("Sheet1") '****Change the name in quotes to suit your needs
    wsLIST.Select
    With wsLIST
        lngROW = Range("A" & .ROWS.Count).End(xlUp).Row
        Range(.Cells(1, 1), .Cells(lngROW, 1)).ClearContents
        i = 1
        For Each ws In Worksheets
            Range("A" & i).Value = ws.Name
            i = i + 1
        Next ws
    End With
End Sub

One change on your end...

Change this line so that the name of the sheet inside the quotes reflects the name of the worksheet you want the list to be on

Set wsLIST = Sheets("Sheet1") '****Change the name in quotes to suit your needs

 

[AliGW]

Here's another way by using a new tab (worksheet) for the list:

1. In Formulas | Name Manager create a new name (SheetNames) and assign it this value: =GET.WORKBOOK(1)&T(NOW())
2. In the first cell where you want your list to appear, type this: =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)),"")
3. Drag down as far as you need to.
4. You can now use this as the source for your validation list. The workbook needs to be saved as macro-enabled.

 

[RCBricker]

Here's another way by using a new tab (worksheet) for the list:

1. In Formulas | Name Manager create a new name (SheetNames) and assign it this value: =GET.WORKBOOK(1)&T(NOW())
2. In the first cell where you want your list to appear, type this: =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)),"")
3. Drag down as far as you need to.
4. You can now use this as the source for your validation list. The workbook needs to be saved as macro-enabled.

I really like this.

Learn something new everyday.

The OP just needs to decide if this is a list they want to maintain or have it dynamically maintained each time the work book opens.