I think I have it. Created 2 UDFs to be used.
One, GatherValues(), pulls the values from A1 on sheets between First and
Last sheets with a space between each value found. If A1 on a sheet was
empty, it returns 0 for that sheet to hold a place in the list of values
returned.
Then, so you can pull the individual pieces out of that mess and place them
around on the Summary sheet I wrote ExtractValue(Cell,WhichSheet) which is
currently set up to return a numeric value. Cell is the cell to get the list
of numbers from, and WhichSheet is the position number of the sheet that you
want info from relative to First. That is, if you wanted value in A1 on 1st
sheet after First, use 1, if you want value for 4th sheet after First, use 4.
Negative or 0 used for this will return 0, and if you ask for 10 and there
are only 9, then it will return 0.
Code segments:
Public Function GatherValues() As String
Dim anySheet As Worksheet
Dim FoundFirstFlag As Boolean
Application.Volatile
For Each anySheet In ThisWorkbook.Worksheets
'find sheet named "First"
If UCase(Trim(anySheet.Name)) = "LAST" Then
Exit Function 'all done
End If
If FoundFirstFlag Then
'prevent creating circular reference
'if Summary sheet got stuck between
'First and Last sheets
If UCase(Trim(anySheet.Name)) <> "SUMMARY" Then
If IsEmpty(anySheet.Range("A1")) Then
GatherValues = GatherValues & " 0"
Else
GatherValues = GatherValues & " " & anySheet.Range("A1")
End If
End If
End If
If UCase(Trim(anySheet.Name)) = "FIRST" Then
'keep working thru sheets, but now
'we save values in A1 on any sheet until
'sheet "Last" is encountered earlier
'once found, flag stays set and
'If ahead of this will be catching
'A1 value on sheets until sheet Last is found
FoundFirstFlag = True
End If
Next
End Function
Public Function ExtractValue(SourceData As Range, WhichGroup As Integer)
Dim RawData As String
Dim NumberOfGroups As Integer
Dim GroupToReturn As Integer
Dim LoopCounter As Integer
Dim EndBlankPosition As Integer
'will be one space for each group
ExtractValue = 0 ' set default for invalid conditions
If Len(SourceData.Text) = 0 Or WhichGroup < 1 Then
Exit Function ' returns 0
Else
RawData = SourceData.Text
End If
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
NumberOfGroups = NumberOfGroups + 1
End If
Next
If NumberOfGroups < WhichGroup Then
Exit Function ' returns 0
End If
Do Until GroupToReturn = WhichGroup
For LoopCounter = 1 To Len(RawData)
If Mid(RawData, LoopCounter, 1) = " " Then
GroupToReturn = GroupToReturn + 1
If GroupToReturn = WhichGroup Then
Exit Do
End If
End If
Next
Loop
'GroupToReturn points to the space in front of group we need
'if not last group, then we get everything from here to next
'space, otherwise we get what's left of the SourceData
EndBlankPosition = InStr(LoopCounter + 1, RawData, " ")
If EndBlankPosition = 0 Then
'last group
ExtractValue = Right(RawData, Len(RawData) - LoopCounter)
Else
ExtractValue = Mid(RawData, LoopCounter + 1, _
EndBlankPosition - LoopCounter)
End If
'if you want to return numeric info vs text as it is now then
ExtractValue = Val(ExtractValue)
End Function
Working demo of this at:
http://www.jlathamsite.com/uploads/for_shail.xls