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

C

Corey

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....
 
T

T. Valko

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
 
T

T. Valko

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
 
C

Corey

thank you worked a treat.

T. Valko said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to Alter value by adding 1 to value in cell 4 rows above 5
delete row and 3 rows below by range selected 2
Search cell value in same column 3
Function or Formula to show the value from above cell 8
Hiding/Exposing Rows based on a cell value 10
calculate last 4 data points in a column (with sometimes blank rows) 7
hide row based on cell value 2
Match Multiple Criteria and Return values positioned above the zero 6

Top