get value from cell value 4 rows above in the same column
 

i have a index value in each cell ranging from cell A24 - A280. Every 4th
row.
A24,A32,A36.........A280.

What i want to be able to do is if i delete some rows(always 4 rows at a
time) the next cell value will always be 1 greater than the cell value above
it.

How can i do this by formula or code.
eg.
A24 = 1
A32 = 2
A36 = 3
A40 = 4
A44 = 5
A48 = 6

If say i need to delete the rows that contain the A40 cell(A40 -
A43){4rows},
then the value in A44 WAS 5, but now it needs to be a value of 4(1 more than
the value above it).

How can i do this?

Corey....

get value from cell value 4 rows above in the same column
 

There may be a better way but this works:

Enter this is A24 and copy down as needed:

=IF(SUMPRODUCT(--(MOD(ROW(),4))),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Biff

"Corey" wrote in message
...
i have a index value in each cell ranging from cell A24 - A280. Every 4th
row.
A24,A32,A36.........A280.

What i want to be able to do is if i delete some rows(always 4 rows at a
time) the next cell value will always be 1 greater than the cell value
above it.

How can i do this by formula or code.
eg.
A24 = 1
A32 = 2
A36 = 3
A40 = 4
A44 = 5
A48 = 6

If say i need to delete the rows that contain the A40 cell(A40 -
A43){4rows},
then the value in A44 WAS 5, but now it needs to be a value of 4(1 more
than the value above it).

How can i do this?

Corey....

get value from cell value 4 rows above in the same column
 

You don't need the first call to sumproduct:

=IF(MOD(ROW(),4),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Don't know why I had that in there!

Biff

"T. Valko" wrote in message
...
There may be a better way but this works:

Enter this is A24 and copy down as needed:

=IF(SUMPRODUCT(--(MOD(ROW(),4))),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Biff

"Corey" wrote in message
...
i have a index value in each cell ranging from cell A24 - A280. Every 4th
row.
A24,A32,A36.........A280.

What i want to be able to do is if i delete some rows(always 4 rows at a
time) the next cell value will always be 1 greater than the cell value
above it.

How can i do this by formula or code.
eg.
A24 = 1
A32 = 2
A36 = 3
A40 = 4
A44 = 5
A48 = 6

If say i need to delete the rows that contain the A40 cell(A40 -
A43){4rows},
then the value in A44 WAS 5, but now it needs to be a value of 4(1 more
than the value above it).

How can i do this?

Corey....

get value from cell value 4 rows above in the same column
 

thank you worked a treat.

"T. Valko" wrote in message
...
You don't need the first call to sumproduct:

=IF(MOD(ROW(),4),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Don't know why I had that in there!

Biff

"T. Valko" wrote in message
...
There may be a better way but this works:

Enter this is A24 and copy down as needed:

=IF(SUMPRODUCT(--(MOD(ROW(),4))),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Biff

"Corey" wrote in message
...
i have a index value in each cell ranging from cell A24 - A280. Every 4th
row.
A24,A32,A36.........A280.

What i want to be able to do is if i delete some rows(always 4 rows at a
time) the next cell value will always be 1 greater than the cell value
above it.

How can i do this by formula or code.
eg.
A24 = 1
A32 = 2
A36 = 3
A40 = 4
A44 = 5
A48 = 6

If say i need to delete the rows that contain the A40 cell(A40 -
A43){4rows},
then the value in A44 WAS 5, but now it needs to be a value of 4(1 more
than the value above it).

How can i do this?

Corey....

get value from cell value 4 rows above in the same column
 

You're welcome. Thanks for the feedback!

Biff

"Corey" wrote in message
...
thank you worked a treat.

"T. Valko" wrote in message
...
You don't need the first call to sumproduct:

=IF(MOD(ROW(),4),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Don't know why I had that in there!

Biff

"T. Valko" wrote in message
...
There may be a better way but this works:

Enter this is A24 and copy down as needed:

=IF(SUMPRODUCT(--(MOD(ROW(),4))),"",SUMPRODUCT(--(MOD(ROW(A$24:A24),4)=0)))

Biff

"Corey" wrote in message
...
i have a index value in each cell ranging from cell A24 - A280. Every
4th row.
A24,A32,A36.........A280.

What i want to be able to do is if i delete some rows(always 4 rows at
a time) the next cell value will always be 1 greater than the cell
value above it.

How can i do this by formula or code.
eg.
A24 = 1
A32 = 2
A36 = 3
A40 = 4
A44 = 5
A48 = 6

If say i need to delete the rows that contain the A40 cell(A40 -
A43){4rows},
then the value in A44 WAS 5, but now it needs to be a value of 4(1 more
than the value above it).

How can i do this?

Corey....