use a string to reference a sheet

hello

Thanks again to everyone for all their help. The atatched sheet should show what I'm looking for pretty well, but here's my problem:

I have a summary sheet that has a column called "sheet name" for about 5000 sheets, then another colum for the "total" cell in each of those sheets. I would like to do a standard ='sheet'!D5 type reference, except use the value in the name column as the sheet name in my reference.

Something like

=' & A2 & '!D5
=' & A3 & '!D5
=' & A4 & '!D5
=' & A5 & '!D5
=' & A6 & '!D5

Reason is: I could just do this formula once, and then fill-down, and everything would be set. Excell would change the A$ part each time.

This brings me to another problem: Could I get the D5 part to be constant while filling down, while the A$ part changes?

Thanks! That works great!

Dim i As Long
Dim i2 As Long
i = 3
'Check to see if this is fresh.
Do While ThisWorkbook.Sheets("SUMMARY").Cells(i, 1).Value <> ""
i = i + 1
Loop
i2 = i
'Set's the starting row for summation
Dim ws As Worksheet
    For Each ws In Worksheets
    If ws.Name <> "SUMMARY" Then
    ThisWorkbook.Sheets("SUMMARY").Cells(i, 1).Value = ws.Name
    ThisWorkbook.Sheets("SUMMARY").Cells(i, 2).Formula = "='" & ws.Name & "'!D5"
    i = i + 1
    Else
    End If
    Next
    
ThisWorkbook.Sheets("SUMMARY").Cells(i, 1).Value = "TOTAL:"
ThisWorkbook.Sheets("SUMMARY").Cells(i, 2).Formula = "=sum(" & Cells(i2, 2).Address & ":" & Cells(i - 1, 2).Address & ")"

This will will check all the worksheets in your workbook, and put the reference