Answer to question 2)
Assume we have Book1.xls with "CellOne" in A1 and "CellTwo" in B1. In
Book2.xls, in cell A1 if you type an equal to sign, and select A1 from
Book1.xls, the formula entered is:
=[Book1]Sheet1!$A$1

If you observe this, cell A1 is referenced as $A$1. The $ symbols indicate
that this uses absolute references. Absolute referencing means that even if
the cell formula is copied using drag-drop, it points to the same cell (i.e.
A1). The opposite of this is relative references, which does not have $
symbols.

What you want is that if you drag A1's formula to B1 in Book2.xls, the
formula should refer to B1 of Book1.xls.

To do this, you must first convert the absolute reference to a relative
reference. In Book2.xls, cell A1, change the formula to:
=[Book1]Sheet1!A1
(i.e. remove the $ signs before A and 1). Then, if you drag this formula
from A1 to B1, the formula will automagically refer to Book1's B1 cell.

In my previous answer to your first question, you'll notice I have
references to $B2. This means that the column name uses absolute addressing,
while the row number uses relative addressing.

When are mixed references useful? Assume you have 1, 2, 3, 4, 5 in cells B1
to F1 and 1, 2, 3, 4, 5 in cells A2 to A6. If you want to generate the
product of these numbers in the range B2:F6, enter the formula:
=$A2*B$1
in B2, and drag this formula from B2 until F2. Then, select B2:F2 and drag
this formula down till row 6 (i.e. to cover cells B2:F6). Now, each cell
(column x, row y) is the product of the value in (column A, row y) and
(column x, row 1).

I trust this helps you, and gives you a useful understanding of cell
references.

"spacerocket" wrote:

Hi,
I have 2 questions:

1. I have 2 columns "Order Date" and "Received Date" in my spreadsheet that
tracks the delivery date of my order. I want the cell in Order Date to turn
Red when the Order Date exceeds 14 days and the cell in Received Date is
still not entered. How do I put the formula to allow for this?

2. I created a formula which references a cell in another spreadsheet. How
do I drag the formula from one cell to another, with the formula
automatically updating the cell address? What happened was, eg. Cell B1 has a
CF that references to Cell A1. When I drag the cell B1 to cell B2, the B2
cell still references the cell A1 instead of A2.

Pls help! Thanks a lot!