I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

Use the $ symbol in front of any number or letter you do not want to
increment.

Row(A1) Relative reference
Row($A$1) Absolute Reference
Row($A1) combination
Row(A$1) combination
--
HTH...

Jim Thomlinson


"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

Hello,

There's a few ways of doing what you want. When you create the numbers in
the first place, if you enter 1 in the first cell and 2 in an adjacent cell,
you can highlight both and drag the fill handle to create incrementing
numbers. If you are using the row function you need to highlight the numbers
and copy them, choose a cell to paste to and righ-click (or from the Edit
menu), select Paste Special and choose Values.
--
Hope this helps,
MarkN


"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

I understand. I want a relative reference when pulling down, and an absolute
reference when copying and pasting. My question is whether or not this is
possible.

Thanks,

"Jim Thomlinson" wrote:

Use the $ symbol in front of any number or letter you do not want to
increment.

Row(A1) Relative reference
Row($A$1) Absolute Reference
Row($A1) combination
Row(A$1) combination
--
HTH...

Jim Thomlinson


"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

The problem is, the 1 and 2... are behind and = sign. The drag does not have
the desired effect.

"MarkN" wrote:

Hello,

There's a few ways of doing what you want. When you create the numbers in
the first place, if you enter 1 in the first cell and 2 in an adjacent cell,
you can highlight both and drag the fill handle to create incrementing
numbers. If you are using the row function you need to highlight the numbers
and copy them, choose a cell to paste to and righ-click (or from the Edit
menu), select Paste Special and choose Values.
--
Hope this helps,
MarkN


"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

I re-thought this, and I think this is what I really need:

I need the result of part of my formula to be recognized as a reference by
the rest of the formula. The inside part of formula looks like this:
=(FLOOR(((ROW()-1)/27),1)+1)*27-26

Essentially, I want E1 for 27 rows, E28 for 27 rows, E55 for 27 rows...

So, I want =$E1, but instead of the "1" part, I want
(FLOOR(((ROW()-1)/27),1)+1)*27-26

This would help alot.

Thanks,

"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,

Found it: =INDIRECT("E"&(FLOOR(((ROW()-1)/27),1)+1)*27-26)
Unfortunantly, I have to be careful not to delete or insert any rows, or I
have to update the 27s and 26 in the formula. Plus it looks like it is
volatile, which I'm told slows down calculations. Is that true? Before I
change everything, it takes around 2 seconds to calculate, which is slow
enough already...I wouldn't want to make it worse.

Thanks,

"sb1920alk" wrote:

I re-thought this, and I think this is what I really need:

I need the result of part of my formula to be recognized as a reference by
the rest of the formula. The inside part of formula looks like this:
=(FLOOR(((ROW()-1)/27),1)+1)*27-26

Essentially, I want E1 for 27 rows, E28 for 27 rows, E55 for 27 rows...

So, I want =$E1, but instead of the "1" part, I want
(FLOOR(((ROW()-1)/27),1)+1)*27-26

This would help alot.

Thanks,

"sb1920alk" wrote:

I have a formula that has the number 1 in it. When I copy down (pull) I would
like the 1 to increase to 2, 3... with each row I pull it down, but if I copy
and paste I would like it to remain 1 regardless of where I paste it. Is
there a way to do this?

ROW(A1) works for copying (pulling) down but it also adjusts when I copy and
paste and I don't want it to.

Thanks,