Work sheet names to a range

[Buyer123]

I have a worksheet that uses the INDIRECT formula to select data from another worksheet dependent on the "value" typed as text into a cell: = INDIRECT(""&A1&"!A1")
Is there a way to create a dynamic range in column A that adds names (text) as I add a sheet? EXCEL Preferred, VBA OK

I am new to this so any life vest is welcome -- thanks much

 

[jarjarbingie]

sample please?

 

[Buyer123]

sample please?

OK Below is some VBA Code I have pieced together to try to solve my original problem: I have a workbook with multiple sheets. I need a list of names of all sheets sent to a range on Sheet "Summary" which is a Dynamic Named Range using Offset ("PONumber"). From this range I will pull out specific cell values according to their Sheet names. The code I have gets the list of names (I verified in Immediate Window) but when I try to send to the named range on "Summarry" Sheet I get only the name of the sheet to the right "AB" in all of the cells of the range. Thanks, I may re post -- I hope I am clear enough:

Sub AddSheet()
Dim SheetNames() As String
Dim i As Long
Dim SheetCount As Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Range("PONumber").Value = SheetNames(i)

Next i

End Sub