Copying functions from one cell to other cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a question I was hoping someone could help me answer. I have the
function =IF(I22>M20-1,-M20,IF(J22>M21-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(I23>M20-1,-M20,IF(J23>M21-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?
 
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!
=====================================================

I have a question I was hoping someone could help me answer. I have the
function =IF(I22>M20-1,-M20,IF(J22>M21-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(I23>M20-1,-M20,IF(J23>M21-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?
 
aaronwexler said:
I have a question I was hoping someone could help me answer. I hav
the
function =IF(I22>M20-1,-M20,IF(J22>M21-1,M21,N22)) in cell M22. Now
would
like to find a quick way to copy this to cells M23,M24,M25.....an
beyond.
The problem is I want the some of the function to change when I copy i
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(I23>M20-1,-M20,IF(J23>M21-1,M21,N23)). When I tr
and
copy the cell using the drag and drop in the lower right corner of th
cell
it moves all cells in the function down one which is what I want excep
for
the two parts I mentioned above. If there anyway to lock those tw
parts
into place so I can drag and drop without moving those parts of th
function
down?
Click to expand...

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 i
met.

Regards
 
Back
Top