If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying functions from one cell to other cells
I have a question I was hoping someone could help me answer. I have the
function =IF(I22M20-1,-M20,IF(J22M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23M20-1,-M20,IF(J23M21-1,M21,N23)). When I try and copy the cell using the drag and drop in the lower right corner of the cell it moves all cells in the function down one which is what I want except for the two parts I mentioned above. If there anyway to lock those two parts into place so I can drag and drop without moving those parts of the function down? |
#2
|
|||
|
|||
Copying functions from one cell to other cells
=IF(I22M$20-1,-M$20,IF(J22M$21-1,M$21,N22))
-- Kind regards, Niek Otten "aaronwexler" wrote in message ... I have a question I was hoping someone could help me answer. I have the function =IF(I22M20-1,-M20,IF(J22M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23M20-1,-M20,IF(J23M21-1,M21,N23)). When I try and copy the cell using the drag and drop in the lower right corner of the cell it moves all cells in the function down one which is what I want except for the two parts I mentioned above. If there anyway to lock those two parts into place so I can drag and drop without moving those parts of the function down? |
#3
|
|||
|
|||
Copying functions from one cell to other cells
Use the dollar sign ($).
It's called "absolute reference" - no change while copying And "relative reference" - change while copying. You can look them up in the Help files. They can refer to the column and/or the row. =IF(I22$M$20-1,-$M$20,IF(J22$M$21-1,$M$21,N22)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "aaronwexler" wrote in message ... I have a question I was hoping someone could help me answer. I have the function =IF(I22M20-1,-M20,IF(J22M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23M20-1,-M20,IF(J23M21-1,M21,N23)). When I try and copy the cell using the drag and drop in the lower right corner of the cell it moves all cells in the function down one which is what I want except for the two parts I mentioned above. If there anyway to lock those two parts into place so I can drag and drop without moving those parts of the function down? |
#4
|
|||
|
|||
Copying functions from one cell to other cells
aaronwexler Wrote: I have a question I was hoping someone could help me answer. I have the function =IF(I22M20-1,-M20,IF(J22M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23M20-1,-M20,IF(J23M21-1,M21,N23)). When I try and copy the cell using the drag and drop in the lower right corner of the cell it moves all cells in the function down one which is what I want except for the two parts I mentioned above. If there anyway to lock those two parts into place so I can drag and drop without moving those parts of the function down? In Cell M22, do the following: 1. Go to your formula and click on the first M20 and press F4 2. Click on the next M20 and press F4 again 3. Do the same for the M21's You can now drag down your formula until your range requirement is met. Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=491097 |
#5
|
|||
|
|||
Copying functions from one cell to other cells
Thanks, that did the trick
"Niek Otten" wrote: =IF(I22M$20-1,-M$20,IF(J22M$21-1,M$21,N22)) -- Kind regards, Niek Otten "aaronwexler" wrote in message ... I have a question I was hoping someone could help me answer. I have the function =IF(I22M20-1,-M20,IF(J22M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23M20-1,-M20,IF(J23M21-1,M21,N23)). When I try and copy the cell using the drag and drop in the lower right corner of the cell it moves all cells in the function down one which is what I want except for the two parts I mentioned above. If there anyway to lock those two parts into place so I can drag and drop without moving those parts of the function down? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Transfer Cell Formatting for linked cells | Scott | General Discussion | 2 | November 23rd, 2005 11:04 PM |
How to insert a value in one cell into many cells | Thomas | Worksheet Functions | 17 | October 18th, 2005 11:03 AM |
Use functions when cells are merged | Amanda | Worksheet Functions | 3 | September 12th, 2005 06:08 PM |
Multiple "IF" functions one one cell returned from different cells | KMAPRO | New Users | 4 | June 9th, 2005 11:38 PM |
copying content of cell automatically to multiple cells | PAUL GRAZIDE | Worksheet Functions | 1 | June 4th, 2005 06:52 PM |