Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I reference every "n" cell in a column in Excel?
I wish to enter a formula at the top of a column "Y" that references a column
"X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
#2
|
|||
|
|||
on cell B1 type
=offset(a1,row()*2-2,0) and copy it down "Alma" wrote in message ... I wish to enter a formula at the top of a column "Y" that references a column "X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
#3
|
|||
|
|||
In B1, put 3.
in b2, put... =INDIRECT("A"&(($B$1*ROW())-($B$1+1))) where... A is the column with the original values B is the column with the every other 'n' row B1 is where you put the number ('n') of rows to be skipped The '1' at the end is the number of rows in the header. For example, if you start your formulas at B2, there is only one row above it. If you start your formulas at B5, there are 4 rows above it and therefore the '1' should be replaced with a '4'. HTH, Gary Brown "Alma" wrote: I wish to enter a formula at the top of a column "Y" that references a column "X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a variable in a cell reference? | Excel Discussion (Misc queries) | |||
Value of last non-blank cell in a column | Excel Worksheet Functions | |||
dynamic external cell reference | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions | |||
cell reference show cell name (ie. D45) and not cell value | Excel Worksheet Functions |