Formula for cell references from other worksheet

  Hobblenobble 13:53 14 Nov 2009

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 2009

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 2009


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 2009

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 2009

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.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Aardman's new YouTube channel to share the work of independent animators

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017