SOLVED

Numbering of a Cell reference in Excel between different files

%3CLINGO-SUB%20id%3D%22lingo-sub-1255087%22%20slang%3D%22de-DE%22%3ENumbering%20of%20a%20Cell%20reference%20in%20Excel%20between%20different%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1255087%22%20slang%3D%22de-DE%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3EHey%20guys%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3EI%20have%20following%20problem%3A%20I%20want%20to%20display%20selected%20data%20from%20many%20Excel%20documents%20(file%20names%3A%2010000%2C%2010001%2C%2010002%20...)%20in%20a%20new%20file%20as%20a%20table.%20%3CBR%20%2F%3EI%20simply%20use%20the%3CBR%20%2F%3Efollowing%20formula%3A%20%3D%20%5B10000.xlsx%5DTable1!%24B'8%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20now%20like%20to%20output%20the%20data%20from%20the%3CBR%20%2F%3Eother%20documents%20in%20consecutive%20order%20in%20the%20new%20table%3CBR%20%2F%3E%3CBR%20%2F%3Eso%20that%20the%20following%20formula%20is%20required%3A%20%3D%20%5B10001.xlsx%5DTable1!%24B'8%20Since%20I%20would%20like%20to%20create%20a%20reference%20to%20hundreds%20of%20files%2C%20I%20would%20like%20to%20reproduce%20the%20formula%20with%20a%20function%20counting.%20%3CBR%20%2F%3EBy%20simply%20pulling%20down%20the%20formula%2C%20it%20is%20computed%201%3A1%20and%20the%20file%20name%20%2210000%22%20is%20not%20continued%20in%20%2210001%22%2C%20%2210002%22%20...%20%3CBR%20%2F%3E%3CBR%20%2F%3EAlternatively%2C%20it%20would%20work%20if%20I%20had%20the%20numbering%20in%20another%20column%20of%20the%20table%20and%20I%20refer%20to%20it%20in%20the%20formula.%20I%20imagine%20it%3CBR%20%2F%3Elike%20this%3A%20%3D%20%5B'A1'.xlsx%5DTable1!%24B'8%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20cell%20'A1'%20would%20then%20contain%20the%20name%20of%20the%20file%20I%20want%20to%20refer%20to.%20So%20my%20theory.%20Of%20course%2C%20this%20does%20not%20work%20either%2C%20because%20the%20formula%20then%20searches%20for%20the%20file%20'A1'.xlsx%20.%20%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20you%20can%20help%20me%20somehow.%20%3CBR%20%2F%3EAnd%20sorry%20for%20my%20partly%20incomprehensible%20english%2C%20I'm%20not%20a%20native%20english%20speaker.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1255087%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1255180%22%20slang%3D%22en-US%22%3ERe%3A%20Numbering%20of%20a%20Cell%20reference%20in%20Excel%20between%20different%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1255180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F596042%22%20target%3D%22_blank%22%3E%40TM-41%3C%2FA%3E%26nbsp%3BA%20lot%20of%20things%20like%20this%20can%20be%20done%20with%20INDIRECT%2C%20however%20that%20function%20doesn't%20work%20well%20with%20cross-workbook%20references.%26nbsp%3B%20I%20think%20you%20are%20likely%20going%20to%20need%20a%20macro%20or%20Power%20Query%20to%20get%20this%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1258617%22%20slang%3D%22de-DE%22%3ERe%3A%20Numbering%20of%20a%20Cell%20reference%20in%20Excel%20between%20different%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1258617%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EThanks%20for%20your%20quick%20reply.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EI%20tried%20both%2C%20but%20unfortunately%20I%20couldn't%20solve%20my%20problem%20with%20that%20either.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EI%20have%20now%20resigned%20myself%20to%20the%20variant%20of%20generating%20the%20formula%20from%20other%20cell%20contents%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3D%3CFONT%20color%3D%22%23FF0000%22%3E%5B%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3E10000%3C%2FFONT%3E%3CFONT%20color%3D%22%23008000%22%3E.xlsx%5DTable1!%24B%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22gt-baf-term-text%22%3E%3CSPAN%20class%3D%22gt-baf-cell%20gt-baf-word-clickable%22%3Ecorrespond%20to%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3D%3CFONT%20color%3D%22%23FF0000%22%3EB-1%3C%2FFONT%3E%26amp%3B%3CFONT%20color%3D%22%230000FF%22%3E%24A15%3C%2FFONT%3E%26amp%3B%3CFONT%20color%3D%22%23008000%22%3EB-2%3C%2FFONT%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESo%20I%20can%20reproduce%20the%20formula%20as%20desired%20and%20then%20convert%20the%20formulas%20as%20values.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIt's%20not%20very%20elegant%2C%20but%20it%20works.%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
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 Replies
Highlighted
Best Response confirmed by TM-41 (New Contributor)
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.

Highlighted

@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.