| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
