Autopopulate a List based on tab names

**toddjesus** · 10-03-2012, 04:37 PM

I have a "Dashboard" sheet within a workbook. There are a little more than a hundred tabs in the book. The "Dashboard" is set up to pull information from the "person's name worksheet" (each person's name is the name of the tab for their respected sheets in the book) through an indirect formula. For example, the person's name will be located in Column B of the "Dashboard" and columns C thru L are pulling information from the sheet associated with the person's name through an indirect funtion; the code for that is this (taken from column D as an example):

=IFERROR(INDIRECT("'"&B4&"'!"&D$1),"")

It is set up so that I type in the person's name into column B and the information in the other columns is automatically populated. However, with so many names to type and the list is only going to grow, is there away so that the "Dashboard" sheet's column B will search the workbook for all the tab names and generate the list without me needing to input every single name?

Also, each tab name is the person's name and the tab is linked to cell C6 of their respected sheets...just an fyi - if that even matters

I look forward to receiving some solutions!

PS this site is amazing and so are it's users!!!

**TMS** · 10-03-2012, 05:45 PM

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim shString As String
Dim sh As Worksheet
Dim i As Integer

' restrict monitoring to cell B4
If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub

' build list of sheets
For i = 1 To Worksheets.Count
    shString = shString & _
    Worksheets(i).Name & _
    ","
Next 'i

' remove last comma
shString = Left(shString, Len(shString) - 1)

' set up Data Validation list
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With Target.Validation
    .Delete
    .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=shString
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
On Error GoTo 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Regards, TMS

**toddjesus** · 10-03-2012, 06:08 PM

I think I did not express what I was attempting to do very clearly. This works well for a 1 cell drop down box, so thank you for that. Is there a way for say a range B4:B300 to autopopulate without manually selecting a name? So, B4 would be say Sheet 2's name, B5 would be Sheet 3's name, B6 would be Sheet 4's name and so on.

**TMS** · 10-03-2012, 06:15 PM

Just loop through the tab names as I have and put them in the next cell ... so, tab(2) would go in cell(B4), tab(3) would go in cell(B5),...tab(i) would ho in cell(B(i+2)).

Just depends what you want to trigger the population. Maybe sheet activation? Could be a selection change.

Regards, TMS