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!

Answer for Q1)
Assuming your Order dates are in column B, and received dates are in column
C, then,
select cells B2 until the last cell in column B, and go to Format
Conditional Formatting.

Under condition 1, change "Cell Value Is" to "Formula Is" in the dropdown.
In the text box next to it, paste the following formula:
=AND($B2+14<TODAY(),ISBLANK($C2))

Click the "Format..." button and change the cell formatting to red, and
click OK twice to dismiss the Format Cells and Conditional Formatting dialog
boxes.

Explanation
===========
Although you have selected several cells in column B, you've probably
started with B2 and ended some other row in column B. As a result, the
conditional formatting is defined with respect to B2, and the same rules
apply similarly to cells below B2.
$B2 + 14 < TODAY() checks if the order date is more than 2 weeks before
today. ISBLANK($C2) checks if the received date column for the particular
row (here row 2) is empty. If both these conditions are satisfied, the
formatting specified is applied.

If you select row B23, the formula will appear as:
=AND($B23+14<TODAY(),ISBLANK($C23))
in the Conditional Formatting box.

Hope this helps.

"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!

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!