Formula for cell references from other worksheet

  Hobblenobble 13:53 14 Nov 09

Copying Cell Info

A worksheet saved as 'Register.xls' has a column that has 1000 hyperlinks that opens a template. When the template is opened information is entered into certain cells in the template and is then saved as 'Test2','Test3','Tes4' and so on. To copy cell info from A2 from the 'Test2.xls' in the register would give the formula =[test2.xls]Sheet1!$A$2. How can I copy and paste =[test2.xls]Sheet1!$A$2 so that in the next cell in the register it will copy =[test3.xls]Sheet1!$A$2, =[test4.xls]Sheet1!$A$2 and so on? Both 'Register' and 'Test2' are not in the same workbook.

Thank you.

Thank you.

  VoG II 14:08 14 Nov 09

Assuming that that formula is entered in row 2 of the Register sheet try


You can then drag that formula down rather than copy and paste.

  Hobblenobble 15:05 14 Nov 09


Thank you once again. The only issue now is that if I have Name, Date, Description as headers on the first row in the Register, in A1, A2, A3. If the filenameS are saved as 'Test1','Test2' etc it will not give info for 'Test2' cells.
A2 on the register has =[Test1.xls]Sheet1!$A$2, A3 has =INDIRECT("'[Test"&ROW()&".xls]Sheet1'!$A$2"). It will give cell info from 'Test3' and 'Test4'.


  VoG II 15:07 14 Nov 09

OK, well you should just need to offset the row as required. If I understand your layout


should do it.

  Hobblenobble 15:13 14 Nov 09

Bobby Dazzler. Works an absolute treat. Thank you ONCE again!

If I could buy you a beer I would!!!!

Reply time is phenomenal!

Thank you.


This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

Disney layout supervisor Rob Dressel on the challenges of visualising Moana

Siri vs Google Assistant