How to add a formula as a filename in a formula

[Vimalan101]

Hello everyone,

I have been working on a document where data from 163 files will be pulled into a single master file.

Given there are a 163, its very tedious to create new line for each document so what did was try to add the filename as a formula. And it doesn't work.

So this is the first line formula : [1.xlsx]Units$A$5

I have file 2 to 163 listed in column H where column H is the concatenation Column F & G. Column F is 2 and column G is .xlsx so the concatenation will be 2.xlsx

So to just simply fasten the process, the formula for file 2 is : [=H2]Units$A$5.

Is there a way to include filename as a formula into another formula? And if it cannot be done, what other easy ways do I have?

[6StringJazzer]

I have read your post and reviewed your file and I still don't understand what you want to do.

For the data in the file you attached, what is the result you are trying to get? Are you trying to create a formula that links to a cell in the other files? Not following what "Units$A$5" is supposed to mean.

[TMS]

Does this help:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Vimalan101]

For the data in the file you attached, what is the result you are trying to get?

Are you trying to create a formula that links to a cell in the other files? Yes thats correct. The values are to be pulled from same cell for all files

I am attaching 1.xlsx for your further review

[TMS]

See post #3