Repeat Function By Changing One Variable

[abbani]

I have a function
(='C:\Trip\[Job1.xlsx]sheet2'!$M$2)
I would like to repeat this function in around a 1000 rows by increasing the value of the sheet
from Job1 to Job2, Job3, and so on
Please advice.
Thanks.

[xor]

="(='C:\Trip\[Job"&ROWS($1:1)&".xlsx]sheet2'!$M$2)"

[abbani]

The results are correctly displayed but it is coming as text not as a function

[Debaser]

You can't do that with a native formula. You would have to use INDIRECT but that won't work with a closed workbook. So you will need the MOREFUNC add-in to use INDIRECT.EXT, or use code to populate the formulas in the first place, or do a manual copy/paste then replace = with = to convert the results of the formulas above to actual formulas.