Formel der læser navn X i Fane A og leder efter den nyoprettede fane X

Du skal have noget til at fange, at der sker en ændring og det kan gøres på flere måder, jeg ville nok selv skrive en makro, som kører hvergang du skifter fra et ark og til samlearket og så lade makroen checke om alle ark er med, og hvis ikke... indsætte en formel eller hvor mange det nu er...

Jeg er meget enig med #1 men for konkret at svare på dit spørgsmål bruger man formlen indirekte (indirect).
Indirekte(A1&"!A1") ville hvis der står sheet1 i A1 give dig værdien af Sheet1!A1.
Udfordringen er ofte cellereferencen hvis du skal kopiere flere celler med samme tanke.

Ok nu forvirrer det mig måske lidt at begge er A1 :-)

Lad os sige navnet på arket står i A1 og jeg skal have data fra det nye ark B1?

Ville det så hedde INDIRECT(A1&”!B1”)

Feks - arknavn udråbstegn og celle ref..
=INDIREC("Sheet3!B1")

Hvis arknavnet indeholder mellemrum så skal det skrives i  '  '

Udfordringen er at arknavnet ikke er der til at starte med. Det er meningen at formlerne er tastet ind på forhånd. Når jeg så indsætter et nyt ark så skal en celle med arknavnet referere til arket med samme navn og en specifik celle.

Yes, du er med på formlen.

Du kan f.eks. skrive:
=indirect("'"&celle med arknavn&"'!"&celle der skal refereres til)
Det er svært at læse men nu med ' mellem " for at tage højde for #4s pointe om arknavne med mellemrum.

Hvis det skal være automatisk, så kunne det være nedenstående kode, som gjorde arbejdet, men den skal nok modificeres lidt til at passe til din model, som jeg jo ikke kender.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim wsSamle As Worksheet, lRow As Long
   
    Set wsSamle = Application.Worksheets("Sheet1")
    'MsgBox "Der er nu oprettet arket " & Sh.Name
   
    'Kode som finder næste sted der skal indsættes en formel og indsæt den
    'måske skal formlen være i kolonne A og hente fra nyt ark B5
    lRow = wsSamle.Range("A1").End(xlDown).Row + 1
    wsSamle.Cells(lRow, 1).Formula = "=" & Sh.Name & "!B5"
   
End Sub