Copying or linking of data in multiple sheets with one formula

[sureshmandadpu]

Hi every one. I have a doubt in excel. I have about 100 sheets in my excel file and every sheet has the kind of formating and information other than amounts. Amounts change from sheet to sheet. Name of the each sheet is name of the each employee. And now I have one more excel file, which has other information. Now I want excel to copy amounts from second excel file to first excel file. Say, I have 4 sheets in excel, namely a, b, c and d. In second excel file, i have data like for a amount is 4, for b amount is 5, for c amount is 6, for D amount is 10.
Name of the employee Amount
a 4
b 5
c 6
d 10

Now, i want excel to copy these figure in cell "B36" of each sheet of first file.

 

[lohithsriram]

Hi,

Assuming that both Test1 and Test2 Excel files are open. Select/Group all the sheets on Test1 file and add this formula to the cell B36.

=VLOOKUP(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)),[Test2.xlsx]Sheet1!$B$4:$C$7,2,FALSE)

 

[sureshmandadpu]

Hi Mr. Lohith,
Nice formula. Thanks for your reply. You made my job easy.

Regards
M. Suresh

 

[lohithsriram]

Hi Suresh,

Glad it solved your issue.