copying from one workbook to another

[skypilotpete]

I want to automatically update a range of cells in one workbook with data from another workbook.

An example of what I have tried is this:

I want to automatically update CellsA1-E1 in Book1 with data from CellsA1-E1 in Book2.

I have put:
=[Book2.xls]Sheet1!$A$1
in Book1-Sheet1-A1 and then filled accross to E1 as I would if copying between sheets within the same workbook.

However, the fill command does not seem to work between workbooks as it would when copying between sheets in the same workbook.
It correctly copies the contents of Book2-Sheet1-A1 into Book1-Sheet1-A1, but it does not automatically adjust itself - i.e. B1 to B1, C1 to C1 etc.
All that happens is that the contents of Book2-Sheet2-A1 are copied into each cell accross to E1.

I have to manually copy each cell individually between the different workbooks. This is very time consuming - is there any way around this?

I am using Excel 2003 and Windows XP Home SP2

[mikerickson]

The $ prevents the row or column from adjusting as the formula is dragged or copied.
I'd use this syntax

=[Book2.xls]Sheet1!A1

[skypilotpete]

Thanks! That works brilliantly. Now for a follow up question. Is there any quicker way to enter that function =[Book2.xls]Sheet1!A1 other than manually? If I put = in the cell and then click on the target cell, the function generated automatically is =[Book2.xls]Sheet1!$A$1 . Of course I can simply manually remove the $ signs, but I wonder if there is a quicker way of getting the required formula.

[mikerickson]

I use a keyboard short cut. It cycles the cell reference through the absolute/relative row/column combinations.

$A$1 A$1 $A1 A1

On my Mac, its the combination Cmd+t.

I believe that Windows machines get the same by pressing F4.

Your help system, "keyboard shortcut", would know.

[skypilotpete]

F4 works - thanks for your help.