microsoft excel - How to make a Custom List with sheet cross reference?

At the end of the month i get a excel file with a variable number of sheets.

Every sheet has a value in the same cell: A1

I want to create a new sheet and display all the values of A1 on every sheet and the sum.

I know how to get the value of a cross sheet cell =Sheet1!A1 but if i put in a new sheet =Sheet1!A1 and try to extent as a series it will fill the formula with =Sheet1!B1 instead of =Sheet2!A1 which is the desire result.

How can I make a custom list with sheet cross reference?

Answer

Without knowing how you'd like to do this, a simple way would be to use an indirect

So on your summary, if you start in row 1 -

=INDIRECT("Sheet"&ROW()&"!$A$1")

That will show A1 on Sheet1

Row 2 will show A1 on Sheet2

etc.

---

Another way to approach it would be with a macro

Sub gather()
Dim wssrc As Worksheet

 'if this stays as activesheet, only run this when summary sheet is active
 'Otherwise, just change the activesheet to the summary sheet like -
 'Set wssrc = ThisWorkbook.Sheets("Sheet4") where "Sheet4" is whatever your summary sheet is
 Set wssrc = ActiveSheet
 Dim i As Integer
 i = 1

   For Each sh In ThisWorkbook.Sheets
      If Not sh.Name = wssrc.Name Then
       wssrc.Cells(i, 1) = sh.Cells(1, 1)

       i = i + 1
      End If
   Next
End Sub

This iterates through all of the sheets in the workbook, checks to make sure they aren't your summary sheet, and copy their A1 value to the summary sheet in column A.