Numbering of a Cell reference in Excel between different files

[TM-41]

Hey guys,

I have following problem: I want to display selected data from many Excel documents (file names: 10000, 10001, 10002 ...) in a new file as a table.
I simply use the following formula:
= [10000.xlsx]Table1!$B$8

I would now like to output the data from the other documents in consecutive order in the new table so that the following formula is required:
= [10001.xlsx]Table1!$B$8

Since I would like to create a reference to hundreds of files, I would like to reproduce the formula with a counting function.
By simply pulling down the formula, it is computed 1:1 and the file name "10000" is not continued in "10001", "10002" ...

Alternatively, it would work if I had the numbering in another column of the table and I refer to it in the formula. I imagine it like this:
= ['A1'.xlsx]Table1!$B$8

The cell 'A1' would then contain the name of the file I want to refer to. So my theory. Of course, this doesn't work either, because the formula then searches for the file 'A1'.xlsx .

I hope you can help me somehow.
And sorry for my partly incomprehensible english, I'm not a native english speaker.

[#2 · Accepted solution]

@TM-41 A lot of things like this can be done with INDIRECT, however that function doesn't work well with cross-workbook references.  I think you are likely going to need a macro or Power Query to get this done.

[#3]

@SaviaThanks for your quick reply. I tried both, but unfortunately I couldn't solve my problem with that either. I have now resigned myself to the variant of generating the formula from other cell contents:

=[10000.xlsx]Table1!$B$8

correspond to

=B$1&$A15&B$2

So I can reproduce the formula as desired and then convert the formulas as values. It's not very elegant, but it works.