Autopopulate a List based on tab names

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):

Please Login or Register  to view this content.

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!!!

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.