cell reference question

[Island Girl]

I'm as new to Excel as can be, so be gentle.

When making reference to a certain cell (say, Cell J60, containing a total),
what's the difference between using =J60 and $J$60. I've read about this in
the Microsoft KB but I don't have enough experience to understand it. Can
you put it in as simple terms as possible for me?

A million Thank You's!

 

[Sandy Mann]

what's the difference between using =J60 and $J$60

$J$60 is what is know as an "Absolute Reference" as opposed to J60 which is
a "Relative Reference".

Enter =J60 in a cell then hover the cursor over the small black square in
the bottom right-hand corner and when the big white cross cursor changes
into black cross-hairs, Left-click and hold pressed while you drag the
formula down the Column. You will see that the formula changes to =J61, J62
etc.

Try the same thing with =$J$60 and you will see that the formula remains the
same.

To create an Absolute Reference enter the reference =J60, (or just = and
click into the cell), then press the function key F4 and you will see that
the Reference cycles through =$J$60, =J$60, =$J60, and =J60. The dollar
sign makes either the Row , the Column or both of them Absolute.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

what's the difference between using =J60 and $J$60

$J$60 is what is know as an "Absolute Reference" as opposed to J60 which is
a "Relative Reference".

Enter =J60 in a cell then hover the cursor over the small black square in
the bottom right-hand corner and when the big white cross cursor changes
into black cross-hairs, Left-click and hold pressed while you drag the
formula down the Column. You will see that the formula changes to =J61, J62
etc.

Try the same thing with =$J$60 and you will see that the formula remains the
same.

To create an Absolute Reference enter the reference =J60, (or just = and
click into the cell), then press the function key F4 and you will see that
the Reference cycles through =$J$60, =J$60, =$J60, and =J60. The dollar
sign makes either the Row , the Column or both of them Absolute.



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Otto Moehrbach]

The difference is relative referencing versus absolute referencing. Clear
as mud?? Do this:
Put in any cell "=J60" without the quotes. With that cell selected, notice
that the cell cursor (the rectangle around the cell) has a small square at
the bottom right of the rectangle. This is called the fill handle.
Place your mouse pointer slowly over that little square until the mouse
pointer changes to a cross ( a plus sign). Now depress your left mouse
button and hold it down and drag that formula down a few cells. Let go of
the mouse button.
Now select the cell again and drag it in the same way, to the right a few
cells.
Now select each of those cells one at a time and notice that the formula is
different in each cell. When you went down, the row number changed. When
you went to the right, the column letter changed.
Now do the same thing in another cell but put a $ in front of either the J
or the 60, not both. Notice that the row number or column letter did not
change, whichever has the $ in front of it.
If you put a $ sign in front of both the J and the 60, neither one will
change.
As you get more into using formulas, you will run into times when you don't
want one or the other to change as the formula is copied. HTH Otto

 

[RagDyer]

To add:
The dollar signs are to prevent the cell addresses from changing as the
formula is *copied* down a column, or across a row.
They *don't* prevent cell addresses from changing due to *inserting or
deleting* columns and/or rows.

As an example of when to use them:
Say you have a purchase order, with quantity in Column A, and unit price in
Column C.
In Column D you create a formula to calculate the total cost of the line
item.

In D2, you enter:
=A2*C2

Since you have say 15 lines on the order sheet, you want to copy this
formula down Column D without having to individually type the formula into
each line (row).

As you copy down the formula, XL increments the "relative" cell addresses in
the formula, so that you have:

=A3*C3
=A4*C4
=A5*C5
.... etc.

But, now you wish to add the tax to every line.

Say you sell to different localities, so the tax rate will vary between
customers.
You therefore designate a cell, say E2, to contain the variable tax rate,
since it might change with each P.O.
Since you're referencing this *single* tax rate cell, you don't want it to
change as you copy down your price formula.

So, you make your formula:
=A2*C2*E$2
Which copies down:
=A3*C3*E$2
=A4*C4*E$2
=A5*C5*E$2
.... etc.

If you were copying across columns, along a row, you could anchor the Column
cell reference by adding the dollar sign in front of the column reference:
$E2.
And of course, you could make both references absolute when copying in 2
directions,
$E$2.

 

[Island Girl]

Sandy, thank you so much for your reply. Since I'm at work today on another
field of endeavor, it will be after work when I can study your reply and
apply it. I have no doubt that it will be the answer I'm looking for.

Thanks again, Sandy!

 

[Island Girl]

Otto, thank you so much for your reply. At the moment I'm at a job that
doesn't not require Excel knowledge, so I'll have time to pore over your
answer and absorb it when I get home.

Thanks so very much. I'll let you know how it goes.

 

[Island Girl]

Hi, RagDyer:

Thanks so very much for your response. I can't wait to apply it when I get
home (I'm at another job at the moment on a different subject). I'll let you
know how well I do.

Thanks again, RagDyer!