You cannot drag a formula containing a sheet reference. You do not state what column and row you wish to begin entering your formulas in.
Here, again, is an easy way to do as you ask. You only have to modify two criteria in the macro below:
Change the reference "A4:A" in line 3 to reflect the column letter you wish the formulas entered in and the beginning row number. For example, if you want the column to be 'K' and the beginning row to be 12, change the reference to "K12:K"
In lines 11, 12, and 13 change the beginning row reference and the column letter reference. For example, if you want the column to be 'K' and the beginning row to be 12, change all three references to:
Cells(i + 12, "K")
Then copy the modified macro to the clipboard:
Sub Populate_Summary()
Dim ShtNames() As String, rng As Range
Set rng = Sheets("Monthly Summary").Range("A4:A" _
& Sheets.Count + 2)
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i
For i = 1 To Sheets.Count
If ShtNames(i) <> "Monthly Summary" Then
Cells(i + 4, "A") = ShtNames(i)
Cells(i + 4, "A").Formula = "=" & _
Cells(i + 4, "A").Value & "!G38"
End If
Next i
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the editing area to the right.
Close the VBE and return to the worksheet.
Press ALT + F8
When the Macros window opens, select the macro and click 'RUN'.