Linking multiple cells between workbooks

[Guest]

Hi,
Can anyone help me with a problem linking multiple cells in a row in one
worksheet to the same number of cells but in a column range in another
worksheet within a different workbook.

The destination worksheet is a summary worksheet which is summarising the
totals from numerous other worksbooks that contain numerous worksheets. In my
summary (destination) worksheet I want to link cells a1 to e1 with cells a150
to a154 in my source worksheet. The source values are formulaes in the source
worksheet that sum the values in the column. I have tried selecting the cells
in the destination worksheet then pressing = and then selecting the source
cells and pressing both Ctrl and enter and also ctrl/shift/enter. Ctrl enter
returns 0 values for all cells where the source cells have a value and the
Ctrl/sift/enter returns an error.

Hope someone can help me. I am using Excel 2003.

 

[Guest]

This is simple to set up, a bit difficult to explain how. So I'll just give
you the answer up front and explain later. In A1 put this formula (assuming
other book's name is SourceBook.xls and the sheet name is 'source sheet'
=OFFSET('[SourceBook.xls]source sheet'!$A$150,COLUMN()-1,ROW()-1)
then fill that formula across to E1.

If you look at Help for OFFSET you'll find that it needs 3 arguments:
Reference address, rows to offset, columns to offset.
To get it to do the transpositioning we just use column values as rows, and
row values as columns. You set up the first formula so that the result of
the math for calculating the row and column offsets both equal zero. If
you'd wanted your formulas to be echoed beginning at B3 (row 3, column 2)
instead of A1, then the formula would have been:

=OFFSET([TestIt.xls]Sheet1!$A$150,COLUMN()-2,ROW()-3)

The ROW and COLUMN functions return the row/column of the cell that they are
in when used as ROW() and COLUMN().

 

[Guest]

Great thank you very much - this has worked !
--
Ruth

This is simple to set up, a bit difficult to explain how. So I'll just give
you the answer up front and explain later. In A1 put this formula (assuming
other book's name is SourceBook.xls and the sheet name is 'source sheet'
=OFFSET('[SourceBook.xls]source sheet'!$A$150,COLUMN()-1,ROW()-1)
then fill that formula across to E1.

If you look at Help for OFFSET you'll find that it needs 3 arguments:
Reference address, rows to offset, columns to offset.
To get it to do the transpositioning we just use column values as rows, and
row values as columns. You set up the first formula so that the result of
the math for calculating the row and column offsets both equal zero. If
you'd wanted your formulas to be echoed beginning at B3 (row 3, column 2)
instead of A1, then the formula would have been:

=OFFSET([TestIt.xls]Sheet1!$A$150,COLUMN()-2,ROW()-3)

The ROW and COLUMN functions return the row/column of the cell that they are
in when used as ROW() and COLUMN().

Hi,
Can anyone help me with a problem linking multiple cells in a row in one
worksheet to the same number of cells but in a column range in another
worksheet within a different workbook.

The destination worksheet is a summary worksheet which is summarising the
totals from numerous other worksbooks that contain numerous worksheets. In my
summary (destination) worksheet I want to link cells a1 to e1 with cells a150
to a154 in my source worksheet. The source values are formulaes in the source
worksheet that sum the values in the column. I have tried selecting the cells
in the destination worksheet then pressing = and then selecting the source
cells and pressing both Ctrl and enter and also ctrl/shift/enter. Ctrl enter
returns 0 values for all cells where the source cells have a value and the
Ctrl/sift/enter returns an error.

Hope someone can help me. I am using Excel 2003.