Linked workbooks, how to with arrays?

[Rick Wetzel]

I want values in an array in a source workbook to appear in an array in the destination workbook (similar to a paste values operation). Seems simple but I haven't been able to figure it out. I can link one cell but not an array of cells.

Thanks

 

[Rick Wetzel]

Nevermind, the solution is obvious. Set the upper left sell in the distination array = to the corresponding cell in the source workbook. Remove the $ signs from the resulting formula and the drag the handle to fill the destination array with value from the source array.

I didn't notice that the default was an absolute cell reference before I post the question.

 

[Max]

Try this ..

Assume a source array in A1:C8
in Sheet1 in Book1.xls
(source array is a 8R x 3C grid)

With Book1 open ..

In any sheet in Book2.xls
-------------------------------
Select an identical 8R x 3C grid
say B18 as the destination array

Put in the *formula bar*:
=[Book1]Sheet1!A1:C8

Array-enter the link formula
i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

Excel will wrap curly braces { } around the formula:
{=[Book1]Sheet1!A1:C8}

(don't enter the curly braces!)

The same formula will appear in each cell in B18

For a neater look, suppress zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

I want values in an array in a source workbook to appear in an array in

the destination workbook (similar to a paste values operation). Seems simple
but I haven't been able to figure it out. I can link one cell but not an
array of cells.