increment cells in repeating formulas by something other than 1

[Retrodog]

i want to write a formula that when copied will increment the cell pointer by
something other 1.

example
column A contains the numbers 1 in A1 and 2 in A2 and 3 in A3 and so on. in
B1 i want the formula (=A1) in B2 i want the formula (=A3) in B3 i want the
formula (=A5) and so on.

the example tries to compress the data from column A into column B by only
extracting the odd cell numbers. i currently know no way to increment the
cell pointer number by anything other than 1.

i know this is not proper syntax but maybe this makes sense as a fictional
example.

column A still has my numbers 1,2,3,4.... etc
in B1 i have this formula [=a(1+2)]
when i copy this formula into B2 it would then point to A3 instead of A2.
from then on the cells would always point to every odd row. i also dont want
to stop at just a +2 increment but would like to increment the pointing cell
by any value.

 

[Max]

One way
In B2: =INDIRECT("A"&ROWS($1:1)*2-1)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[Ashish Mathur]

Hi,

Please try this formula in cell B2

OFFSET(A1,ROW(A1)+1,0,1,1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Here's a non-volatile method:

Entered in B1:

=INDEX(A$1:A$100,ROWS(B$1:B1)*2-1)

Copy down as needed. Adjust for the correct end of the range.

 

[Retrodog]

Thanks, this worked perfectly.

One way
In B2: =INDIRECT("A"&ROWS($1:1)*2-1)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

i want to write a formula that when copied will increment the cell pointer by
something other 1.

example
column A contains the numbers 1 in A1 and 2 in A2 and 3 in A3 and so on. in
B1 i want the formula (=A1) in B2 i want the formula (=A3) in B3 i want the
formula (=A5) and so on.

the example tries to compress the data from column A into column B by only
extracting the odd cell numbers. i currently know no way to increment the
cell pointer number by anything other than 1.

i know this is not proper syntax but maybe this makes sense as a fictional
example.

column A still has my numbers 1,2,3,4.... etc
in B1 i have this formula [=a(1+2)]
when i copy this formula into B2 it would then point to A3 instead of A2.
from then on the cells would always point to every odd row. i also dont want
to stop at just a +2 increment but would like to increment the pointing cell
by any value.

 

[Retrodog]

Thank you, this worked great.

Hi,

Please try this formula in cell B2

OFFSET(A1,ROW(A1)+1,0,1,1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

i want to write a formula that when copied will increment the cell pointer
by
something other 1.

example
column A contains the numbers 1 in A1 and 2 in A2 and 3 in A3 and so on.
in
B1 i want the formula (=A1) in B2 i want the formula (=A3) in B3 i want
the
formula (=A5) and so on.

the example tries to compress the data from column A into column B by only
extracting the odd cell numbers. i currently know no way to increment the
cell pointer number by anything other than 1.

i know this is not proper syntax but maybe this makes sense as a fictional
example.

column A still has my numbers 1,2,3,4.... etc
in B1 i have this formula [=a(1+2)]
when i copy this formula into B2 it would then point to A3 instead of A2.
from then on the cells would always point to every odd row. i also dont
want
to stop at just a +2 increment but would like to increment the pointing
cell
by any value.

 

[Retrodog]

Thank you, this worked great. Its amazing how 3 very differently written
formulas all allowed me to accomplish the same thing.

Here's a non-volatile method:

Entered in B1:

=INDEX(A$1:A$100,ROWS(B$1:B1)*2-1)

Copy down as needed. Adjust for the correct end of the range.

--
Biff
Microsoft Excel MVP

i want to write a formula that when copied will increment the cell pointer
by
something other 1.

example
column A contains the numbers 1 in A1 and 2 in A2 and 3 in A3 and so on.
in
B1 i want the formula (=A1) in B2 i want the formula (=A3) in B3 i want
the
formula (=A5) and so on.

the example tries to compress the data from column A into column B by only
extracting the odd cell numbers. i currently know no way to increment the
cell pointer number by anything other than 1.

i know this is not proper syntax but maybe this makes sense as a fictional
example.

column A still has my numbers 1,2,3,4.... etc
in B1 i have this formula [=a(1+2)]
when i copy this formula into B2 it would then point to A3 instead of A2.
from then on the cells would always point to every odd row. i also dont
want
to stop at just a +2 increment but would like to increment the pointing
cell
by any value.

 

[Max]

Welcome, glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you, this worked great. Its amazing how 3 very differently written
formulas all allowed me to accomplish the same thing.

Here's a non-volatile method:

Entered in B1:

=INDEX(A$1:A$100,ROWS(B$1:B1)*2-1)

Copy down as needed. Adjust for the correct end of the range.

--
Biff
Microsoft Excel MVP

i want to write a formula that when copied will increment the cell
pointer
by
something other 1.

example
column A contains the numbers 1 in A1 and 2 in A2 and 3 in A3 and so
on.
in
B1 i want the formula (=A1) in B2 i want the formula (=A3) in B3 i want
the
formula (=A5) and so on.

the example tries to compress the data from column A into column B by
only
extracting the odd cell numbers. i currently know no way to increment
the
cell pointer number by anything other than 1.

i know this is not proper syntax but maybe this makes sense as a
fictional
example.

column A still has my numbers 1,2,3,4.... etc
in B1 i have this formula [=a(1+2)]
when i copy this formula into B2 it would then point to A3 instead of
A2.
from then on the cells would always point to every odd row. i also
dont
want
to stop at just a +2 increment but would like to increment the pointing
cell
by any value.