copy formula from first row to next row but changing the file name in the formula

[aravindhan_31]

Hi,
I have more than 100 files in one folder and one master file.
In the master file I have all the file names listed in Column A from Row 10.
Example
Store_11
Store_22
Store_33 etc
in column B Row 10 I have many formulas like this
=[Store_1.xlsx]Sheet1'!$A$1], =[Store_1.xlsx]Sheet2'!$D$45,] ,=[Store_1.xlsx]Sheet5'!$U$45,] etc.. all the values which are there on the Store_1 file.
in the next row

=[Store_2.xlsx]Sheet1'!$A$1], =[Store_2.xlsx]Sheet2'!$D$45,] ,=[Store_2.xlsx]Sheet5'!$U$45,] etc.. all the values which are there on the Store_1 file.

formulas are same for all the rows except the file names such as Store_1, Store2 etc.
If there are few files then I can enter the formulas manually. But there are more than 100 files. So I Have to type the formulas in all the 100 rows in master sheet.

I need a macro which copies the same formula in all the row by changing only the file names which are there on the folder.

thanks in advance..

The same question has been posted here: http://www.mrexcel.com/forum/showthread.php?t=333925

[mikerickson]

VBA may not be needed.
Have you looked at the INDIRECT function?

[VBA Noob]

aravindhan_31,

I suggest you do read the forum rules below. I asked you in your other post to add the link to the cross post so you decided to break another rule and create a duplicate post.

VBA Noob

[aravindhan_31]

Hi,

I am bit confused with this indirect funtion,Could you pleas guide me to do this. If you could explain the formula for few rows, I can pick it up

I have attached my sample excel file.

[aravindhan_31]

aravindhan_31,

I suggest you do read the forum rules below. I asked you in your other post to add the link to the cross post so you decided to break another rule and create a duplicate post.

VBA Noob

Hi,

I apologise, I will do correctly henceforth.