CODEWITHSUNDEEP
excel
trenccan
trenccan

Reputation: 720

Automatic copy in excel

if i have in first three rows of column A data

A1 = 1

A2 = 2

A3 = 3

and drag it down in A4 will be 4, A5 = 5 and etc.

I want to do the same but with connection from another workbook. If i have in A1 = 1 from source ='C:\Users\chrast_m\Desktop\Kalibrácia[001.xlsx]Hárok1'!$AA$1

and in A2 = 2, from source ='C:\Users\chrast_m\Desktop\Kalibrácia[002.xlsx]Hárok1'!$AA$1

after i drag it i want:

A1 = C:\Users\chrast_m\Desktop\Kalibrácia[001.xlsx]Hárok1'!$AA$1

A2 = C:\Users\chrast_m\Desktop\Kalibrácia[002.xlsx]Hárok1'!$AA$1

A3 = C:\Users\chrast_m\Desktop\Kalibrácia[003.xlsx]Hárok1'!$AA$1

A4 = C:\Users\chrast_m\Desktop\Kalibrácia[004.xlsx]Hárok1'!$AA$1

A5 = etc.

Thank you for your help!

Upvotes: 1

Views: 123

Answers (1)

DeanOC
DeanOC

Reputation: 7282

You can use the INDIRECT function to achieve this. In your main spreadsheet:

  • Enter '001 in Cell A1 (the apostrophe will stop Excel from converting to number)
  • Enter ="C:\Users\chrast_m\Desktop\Kalibrácia[" & A1 & ".xlsx]Hárok1'!$AA$1"
  • Enter =INDIRECT(B1) in Cell C1

Now if you select cells A1:C1 and drag them down, you will create references to 002.xlsx, 003.xlsx etc.

Upvotes: 1

Related Questions