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?

=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?

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?

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

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?