Linking Data of a cell in the same worksheet from another excel sheet

[Mathew123]

Hello Sirs,

I have an excel file as drawing register showing the details of drawings received - such as Reference Number, Attachments details & Drawing Titles etc. (Say the file name name as "Register1").

I wanted to make transmittal for each item in another excel file (say file name as "Transmittal1").

So once i linked a cell in "Transmittal1" file =[Register1.xlsx]Sheet1!$A$1, i need to update next two cells of "Transmittal1" file with the corresponding data from "Register1" file.

If i copy paste manually it will be =[Register1.xlsx]Sheet1!$B$1 & =[Register1.xlsx]Sheet1!$C$1.

But is there a way to have these two data automatically while i link the first cell?

Thank you,
Mathew

 

[AxonFantaxon]

Hi, if you remove the $ you should be able to just drag it across and it'll pull through the relevant cells (assuming they're in the right order). Could you send an example maybe in case I've misunderstood?

 

[Mathew123]

Axon, No. the cells are not in right order and not aligned to drag. I prefer to have it automatically appeared since more than 1000 docs to prepared in the same way. See below tables.

Register1[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Doc No[/TD]
[TD]Attachment[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]PIC-P2B-OGE-KME-TS-0375[/TD]
[TD]1[/TD]
[TD]Water Supply Layout[/TD]
[/TR]
</tbody>[/TABLE]


Transmittal1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Doc No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PIC-P2B-OGE-KME-TS-0375
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Attachment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Water Supply Layout[/TD]
[/TR]
</tbody>[/TABLE]

 

[AxonFantaxon]

Hi, if you look on the below you can achieve it with the OFFSET formula but not sure if this is what you're wanting. Sorry I couldn't help more.

[TABLE="width: 554"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Register1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doc No[/TD]
[TD]Attachment[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]=Transmittal1!A3[/TD]
[TD]=OFFSET(Transmittal1!A3,2,3)[/TD]
[TD]=OFFSET(Transmittal1!A3,3,4)
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 259"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Register1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doc No[/TD]
[TD]Attachment[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]PIC-P2B-OGE-KME-TS-0375[/TD]
[TD="align: right"]1[/TD]
[TD]Water Supply Layout[/TD]
[/TR]
</tbody>[/TABLE]