Linking sheet between two files

[jpreman]

Thanks for reading this post.

I have two files named X and Y. I want to link column A in file X sheet 1 to
row 1 in file Y sheet 2. eg. File X sheet 1 cell A1 should be linker to file
Y sheet 2 cell P10; File X sheet 1 cell A2 to be linked to file Y sheet 2
cell Q10, similarly cell A3 to cell R10 and so on. How can this be done
easily without linking the cells individually? linking columns to columns or
row to row is simple, just enter the formula in one cell and drag it down or
across as required. Is there any such trick to link a column to a row as
explained above?

Preman

 

[Sheeloo]

If you enter this in P10 (on the same sheet) and copy right you will get what
you want
=INDIRECT("A"&(COLUMN()-15))

You need to replace "A" with "[FileName]SheetName!A" after putting your file
name and sheet name apprpriately.

 

[Max]

With Y.xls simultaneously open (this is required)
In X.xls, in Sheet1,
In A1: =OFFSET(INDIRECT("'[Y.xls]Sheet2'!P10"),,ROWS($1:1)-1)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[jpreman]

Thanks a lot to both of you, Sheeloo and Max.

That's exactly what I was looking for.

With Y.xls simultaneously open (this is required)
In X.xls, in Sheet1,
In A1: =OFFSET(INDIRECT("'[Y.xls]Sheet2'!P10"),,ROWS($1:1)-1)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

Thanks for reading this post.

I have two files named X and Y. I want to link column A in file X sheet 1 to
row 1 in file Y sheet 2. eg. File X sheet 1 cell A1 should be linker to file
Y sheet 2 cell P10; File X sheet 1 cell A2 to be linked to file Y sheet 2
cell Q10, similarly cell A3 to cell R10 and so on. How can this be done
easily without linking the cells individually? linking columns to columns or
row to row is simple, just enter the formula in one cell and drag it down or
across as required. Is there any such trick to link a column to a row as
explained above?

Preman

 

[Max]

Good to hear. Thanks for rating responses and feedback.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik