increment cells in repeating formulas by something other than 1

R

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

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

Retrodog

Thanks, this worked perfectly.

Max said:
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
---
Retrodog said:
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.
Click to expand...
Click to expand...
 
R

Retrodog

Thank you, this worked great.

Ashish Mathur said:
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

Retrodog said:
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.
Click to expand...
Click to expand...
 
R

Retrodog

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

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


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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

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


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

Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Formula to Increment by Letter not Number 6
Excluding Repeating Numbers Help!1 5
Incrementing by 1 in every other column 2
Formula results display only after pressing Enter key 1
Copying formulas across columns 2
Using two Vlookup in one Cell 1
formulas 5

Top