Generating summary sheet from excel form

A Sample workbook, with a few expected answers, and why they are expected, would definitely help here

It can be done easily by just placing a Workbook sheet change event code

Add the below code in Thisworkbook code window, whenever any change happens in the worsheets it will get the data in summary sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Index < 4 Then Exit Sub

Dim sConc As String, iWsNo As Integer, sBase As String

sBase = "SUMMARY"
iWsNo = Sh.Index

With Sh
    sConc = .Range("C8") & " " & .Range("C10") & " " & .Range("C12") & _
        " " & .Range("C14") & " " & .Range("C16") & " " & .Range("C18") & _
        " " & .Range("C20") & " " & .Range("C23") & " " & .Range("C25") & _
        " " & .Range("C27") & " " & .Range("C34") & " " & .Range("C36") & _
        " " & .Range("C38")
End With

With Sheets(sBase)
    .Cells(iWsNo, "A").Value = Sh.Range("B1").Value  'Last Name
    .Cells(iWsNo, "B").Value = Sh.Range("G1").Value  'First Name
    .Cells(iWsNo, "C").Value = Sh.Range("B3").Value  'Division
    .Cells(iWsNo, "D").Value = sConc 'Desc
    .Cells(iWsNo, "E").Value = Sh.Range("C28").Value 'Total Hours
End With

End Sub

Refer the attached file for details

I changed the workbook event to manual running of code... Please click the button which is there in the summary sheet to get data from all sheets, except the first 3 sheets (Including the hidden sheet).

Sub GetDataFromAllSheets()
Dim Sh As Worksheet, sConc As String, iWsNo As Integer, sBase As String

sBase = "SUMMARY"

For Each Sh In ThisWorkbook.Sheets
    iWsNo = Sh.Index
    If iWsNo > 3 Then
        With Sh
            sConc = .Range("C8") & " " & .Range("C10") & " " & .Range("C12") & _
                " " & .Range("C14") & " " & .Range("C16") & " " & .Range("C18") & _
                " " & .Range("C20") & " " & .Range("C23") & " " & .Range("C25") & _
                " " & .Range("C27") & " " & .Range("C34") & " " & .Range("C36") & _
                " " & .Range("C38")
        End With
        With Sheets(sBase)
            .Cells(iWsNo, "A").Value = Sh.Range("B1").Value  'Last Name
            .Cells(iWsNo, "B").Value = Sh.Range("G1").Value  'First Name
            .Cells(iWsNo, "C").Value = Sh.Range("B3").Value  'Division
            .Cells(iWsNo, "D").Value = sConc 'Desc
            .Cells(iWsNo, "E").Value = Sh.Range("C28").Value 'Total Hours
        End With
    End If
Next Sh

End Sub

Refer the attached file for details

Okay, is the drop down always present at the same spot on each sheeet?
With the sample sheet, what is the expected out put in Column C for those names ?

Okay, I am falling asleep at keyboard ATM, Will look at it in the morning; Or, maybe, someone will come up with an acceptable answer before then !

if you have all the names of the sheets in G column you can apply in C Column
ie
G4 = Flex Form
and C4

=INDIRECT("'"&G4&"'!B3")

attached solution Works if all the sheets are called flex form (number)