Formula to take amounts from other sheet with no duplicate amounts

Xx7, I'm open to correction, but I don't think you're going to get a formula to operate across multiple sheets like that. Is copying/pasting the data from each of the Column As to the Summary sheet, then using Excel's functions to remove the duplicates a runner?

Run this code in your workbook.

The code with an ' is not running (and are not neccessary for your question).

Sub integratie_Oeldere()

'worksheetname in column A

'Worksheets.Add().Name = "Consolidated"

With Sheets("Summary")
    .UsedRange.ClearContents
    .Range("A1").Value = Array("Number")

Sheets("Summary").Range("a2:d15000").ClearContents


  For Each sh In Sheets
  With sh
If .Name <> "Summary" Then
     Rng = .Cells(Rows.Count, 1).End(xlUp).Row - 1
'    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Rng) = .Name
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Rng, 2) = .Range("A1").Resize(Rng, 2).Value
   End If
  End With
 Next

Columns("A:A").Select
    ActiveSheet.Range("$A$1:$A$11").RemoveDuplicates Columns:=1, Header:=xlYes
    Range("A1").Select
    
Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

For me personally I would use VBA since who knows you might have more than 200 tabs

Sub SUmmary()
Dim ws As Worksheet, wsSummary As Worksheet
Dim col As Collection: Set col = New Collection
Dim i As Long, LR As Long, j As Long

Set wsSummary = ThisWorkbook.Worksheets("Summary")

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        With ws
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
            For i = 1 To LR
                On Error Resume Next
                col.Add .Cells(i, 1).Value, CStr(.Cells(i, 1).Value)
            Next i
        End With
    End If
Next ws

With wsSummary
    .Select
    .Columns("A").ClearContents
    For j = 1 To col.Count
        .Cells(j, 1).Value = col(j)
    Next j
End With

End Sub

You could change the red text in e.g.

ActiveSheet.Range("$A$1:$A$2500").RemoveDuplicates