Execution of text as formula in another cell

[Happyleptic]

Hi experts,

I would like to create a summary Excel-sheet which refers to several other Excel-sheet A_1 to Excel sheet A_100.

e.g. 
in A1 of summary: =C:\Test\[A_1.xlsx]Sheet1'!$A$1 
to
A100 of summary: =C:\Test\[A_100.xlsx]Sheet1'!$A$1

The referencing works well, but I don`t want to define each reference cell by cell.
With concetenate I can create the text for the formulas with the increasing file number easily. 
Is there a way to execute this text in another cell as formula to  create the references?

Any other idea to solve this is very much welcome. Thanks in advance for your help.

[schauan]

Hello,

With concetenate I can create the text for the formulas with the increasing file number easily.

Create the formulas p.e. with # (and not =)

Use Search & Replace to replace # with = and the formulas work.

Another way is to use a vba-makro.

[Happyleptic]

Nice workaround. Thanks for the great idea!
While replacing # by = a window pops up prompting a user input to select the file, if the referenced file is not open (in my case the reference file is not yet existing and will be created in an iterative process). Closing the window still updates the value, so it works.

However I wonder if there is a more elegant way to avoid manually closing the pop up window. Becomes quite tediuos if the file contains x referenced fields. 
Does a vba macro allow programming while avoiding this issue?

[schauan]

Hello,

a window pops up prompting a user input to select the file

replace the # in your formulas after creating the referenced files.

If you create new formulas to non existing files, the popup-window comes every time. But when you open the file next time, you can reject the actualisation of connections.


with vba, you can look for the files and set new formulas only, when the referenced files exists.

[Happyleptic]

Thank you for your support. Indeed super helpful. Issue resolved.