Change the increment step of a formula.
vi
-
Jack -
Jack -
Hello,
I'm coming back to you for another issue:
I want to change the increment step in Excel within a spreadsheet. Let me explain:
I have a first sheet with data, and a second sheet where I want to copy only some data from the first (these data being spaced, that is, every 10 cells in a column I need to copy the data). So how can I copy this data into the second sheet in such a way that from one cell to another (vertically) I have a step of 10?
Configuration: Windows XP / Firefox 21.0
I'm coming back to you for another issue:
I want to change the increment step in Excel within a spreadsheet. Let me explain:
I have a first sheet with data, and a second sheet where I want to copy only some data from the first (these data being spaced, that is, every 10 cells in a column I need to copy the data). So how can I copy this data into the second sheet in such a way that from one cell to another (vertically) I have a step of 10?
Configuration: Windows XP / Firefox 21.0
2 answers
-
The formula is supposed to be written in A1 and the first cell to copy from sheet 1 is A1
The function OFFSET(ref; rows; cols; height;width)
with
- ref is the reference cell here A1 (from sheet 1)
- rows is the vertical offset from the row of ref here 10*(ROW()-1) where ROW() is the row number in which the formula is written
- cols is the horizontal offset from the column of ref (here 0 - we stay in the column of ref)
- height is the number of rows taken (here 1 row)
- width is the number of columns taken (here 1 column)
in short, the formula written in Axx (xx is the row number) selects in sheet 1 the cell located 10*(xx-1) rows below (than the cell $A$1 of sheet 1)
good luck -
Hello
If your copy starts from line 1, otherwise you need to adapt
=OFFSET(Sheet1!A$1;10*(ROW()-1);0;1;1)
Have a nice day