Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Absolute Statements in Excel
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 |
#2
|
|||
|
|||
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) "Beth" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Beth" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
Pivot tables Excel 2003 absolute references | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
How do I copy a formula in excel where part remains absolute the . | Excel Discussion (Misc queries) |