Is it possible to use variables in a formula?

[Doghouse308]

As the title suggests, I would like to know if it is possible to use a variable in a formula.

The specific application may also have other solutions.

I am trying to bring in data from an external workbook. I am currently using an index/match formula to select the data to import.

example below

=INDEX('[Ternai-Master.xlsm]Master Schedule'!$B$2:$BZ$1017,MATCH($A3,'[Ternai-Master.xlsm]Master Schedule'!$A:$A,0),MATCH(O$2,'[Ternai-Master.xlsm]Master Schedule'!$1:$1,0)-1)

I would like to replace the reference to the other workbook with a file path and name so this same workbook can be used without hard coding the file names.

Thank you in advance

 

[Trevor_S]

You could use the Indirect function, see: https://support.office.com/en-gb/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

For example, if the workbook path/name was in cell A1, you could replace:
'[Ternai-Master.xlsm]Master Schedule'!$B$2:$BZ$1017
with:
INDIRECT("'["&A1&"]Master Schedule'!$B$2:$BZ$1017")

 

[Eric W]

However, with INDIRECT keep in mind that it doesn't work on closed workbooks.

 

[Doghouse308]

Thank you for your suggestions.

I think I may created a function instead.