Variable workbook name in a formula “indirect”

AldighaithirMM there's no "good" way of doing this without VBA. Here is a helpful link to tell you about how to do this:

https://exceloffthegrid.com/getting-values-from-a-closed-excel-workbook/

Here is some code to do what you want. Change the values to your book and sheet name, and range desired. Data will be pulled into an array (single value if a single cell passed). There are other methods but I've found this to be the fastest.

Option Explicit


Sub Test_ExtractDataFromClosedFile()
    Dim Values As Variant
    Values = ExtractDataFromClosedFile("C:\Users\Zack\Desktop\Test.xlsx", "Sheet1", "A1:B10")
    Stop
End Sub


Public Function ExtractDataFromClosedFile( _
    ByVal FilePath As String, _
    ByVal SheetName As String, _
    ByVal RangeAddress As String _
    ) As Variant

    Dim Book As Workbook
    Dim Sheet As Worksheet
    Dim Range As Range
    Dim BookOpen As Boolean
    Dim BookName As String
    Dim Values As Variant
    
    BookName = GetNameFromPath(FilePath)
    
    If IsWorkbookOpen(BookName) Then
        BookOpen = True
        Set Book = Workbooks(BookName)
    Else
        Set Book = Workbooks.Open(FilePath, False)
    End If
    
    If WorksheetExists(SheetName, Book) Then
        ExtractDataFromClosedFile = Book.Worksheets(SheetName).Range(RangeAddress).Value
    End If
    
    If Not BookOpen Then
        Book.Close False
    End If

End Function


Public Function GetNameFromPath( _
       ByVal Path As String _
    ) As String

    Dim Position As Long

    Position = InStrRev(Path, GetPathSeparatorSafe)
    If Position > 0 Then
        GetNameFromPath = Mid(Path, Position + 1)
    Else
        GetNameFromPath = Path
    End If

End Function


Public Function GetPathSeparatorSafe()

    Dim EnableEvents As Boolean

    EnableEvents = Application.EnableEvents
    Application.EnableEvents = False
    GetPathSeparatorSafe = Application.PathSeparator
    Application.EnableEvents = EnableEvents

End Function


Function IsWorkbookOpen( _
         ByVal WorkbookName As String _
         ) As Boolean

    On Error Resume Next
    IsWorkbookOpen = CBool(Len(Workbooks(WorkbookName).Name) <> 0)
    On Error GoTo 0

End Function


Function WorksheetExists( _
         ByVal SheetName As String, _
         Optional TargetBook As Workbook _
         ) As Boolean

    If TargetBook Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set TargetBook = ActiveWorkbook
    End If
    On Error Resume Next
    WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
    On Error GoTo 0

End Function

HTH