Formula for cell references from other worksheet

  Hobblenobble 13:53 14 Nov 09
Locked

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

=INDIRECT("'[Test"&ROW()&".xls]Sheet1'!$A$2")

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

  Hobblenobble 15:05 14 Nov 09

VoG,

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'.

Stumpy.

  VoG II 15:07 14 Nov 09

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

=INDIRECT("'[Test"&ROW()-1&".xls]Sheet1'!$A$2")

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.

Stumpy.

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

The updated 'Corel Painter inside Photoshop' plugin ParticleShop offers new brushes

Best running headphones | Best sport & fitness headphones: 4 brilliant pairs of wireless…