Absolute Statements in Excel

[Beth]

Hello out there,

I have just recently learned Absolute Statements and am having some trouble
grasping the following. I understand that you can lock the row and the
column by using the dollar sign - hitting short cut key F4. (Ex: =B24*$B$27).
If I continue to hit F4 I can then just lock the row or the column. I would
like to know what is reason for locking just a row or a column? As well if I
lock a row does that mean that any other information that is in that row is
locked as well?

Thanks

 

[Bob Phillips]

Beth,

An example, trite, but hopefully useful

Say we have data like so

A B C D E
F G H
1 24-Apr 25-APr 26-Apr 27-Apr 28-Apr
29-Apr 30-Apr
2 Bill
3 Laura
4 Judy

Suppose you want to show the name from column A under the weekday dates. If
you put this formula in B2

=IF(AND(WEEKDAY(B1)<>1,WEEKDAY(B1)<>7),A2,"")

will blank out B2 as required, as 24-Apr is a Sunday (Weekday 1). But if you
copy down to B4, and across to H4, see what you get. Lots of #VALUEs.

Now add this formula to B2

=IF(AND(WEEKDAY(B$1)<>1,WEEKDAY(B$1)<>7),$A2,"")

Copy this down to B4 and across to H4, and see what you get. The names are
filled as required.

What is happening is that copying the first B2 down to B3, and it changes
the formula to

=IF(AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7),A3,"")

which is then testing fro a date in B2, when it is still in B1.

Similarly with the name in column A.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

Don't get the "locked" thing wrong. All it does is NOT change the row number
/ column letterwhen you copy / fill the formulas to other cells. The cell
itself is not affected or locked and it doesn't care. So it's a help for
setting up a spreadsheet and nothing more. See if this makes sense:

Let's create a multiplication table. In Cell A2, enter 1. In cell A3 enter
2. Continue or fill down so it says 1 to 10 in A rows 2 to 11. In B1 enter
1. In C1 enter 2. Continue or fill right so it says 1 to 10 in row 1 columns
B to K. Decorate if you are that kind of person. I won't tell anyone.

Now we'll make a table that multiplies the number to the far right with the
number on the very top. So we will create a single formula that's locked on
A and on 1. Enter in B2 formula
=$A2*B$1
which says "don't change A and don't change 1 on copy". Fill or copy right
and down and you have all the 10*10 working formulas without entering all
100.

This is what it does, and this is all it does, users can destroy your
formulas just as bad as ever.

HTH. Best wishes Harald