J

#### jkrons

=A1&";"&A2&";"&A3

Now I want to copy it down in the B-column, but instead of being

change to

=A2&";"&A3&";"&A4

I would like the formula, by copying, to change to

=A4&";"&A5&";"&A6

=A7&";"&A8&";"&A9

and so on.

Can this be done?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

J

=A1&";"&A2&";"&A3

Now I want to copy it down in the B-column, but instead of being

change to

=A2&";"&A3&";"&A4

I would like the formula, by copying, to change to

=A4&";"&A5&";"&A6

=A7&";"&A8&";"&A9

and so on.

Can this be done?

J

=A1&";"&A2&";"&A3

and another in B2:

=INDIRECT("A"&ROW(A1)+H2)&";"&INDIRECT("A"&ROW(A2)+H2)&";"&INDIRECT

("A"&ROW(A3)+H2)

In column H from 2 and down I have the values 3,5,7,9,11....

And now I can copy the formula in B2 down - but can it be done in an

esier way?

Jan

S

Try using $ to make either the column absolute - $A1, the row absolute -

A$1, or column add row absolute - $A$1

J

to add three to the relative part (row) of the reference in stead of 1,

making $A1 become $A4 instead of $A2, which normally happens when you copy

one down.

Jan

D

I use a formula to build a string that looks like a formula.

Then I drag down the range.

Then I convert it values (so it's not a formula).

Then I convert them to formulas.

This is the formula that I put in B1:

="=A"&(ROW()-1)*3+1&"&"";""&a"&(ROW()-1)*3+2&"&"";""&a"&(ROW()-1)*3+3

It evaluates to:

=A1&";"&a2&";"&a3

Then I drag down the column as far as I need.

Then with that range selected

Edit|copy followed by edit|paste special values

Then with that range still selected

Data|text to columns

Choose Fixed width, but don't have any delimiters.

Excel will see the results as those nice straight-forward formulas.

===============

Another way is to use something like:

=INDEX(A:A,(ROW()-1)*3+1)&";"

&INDEX(A:A,(ROW()-1)*3+2)&";"

&INDEX(A:A,(ROW()-1)*3+3)

And drag down as far as you need.

It really depends on what's important to you.

Is it just getting the data there or do you like the straight-forward formulas?

J

Jan

Dave said:

I use a formula to build a string that looks like a formula.

Then I drag down the range.

Then I convert it values (so it's not a formula).

Then I convert them to formulas.

This is the formula that I put in B1:

="=A"&(ROW()-1)*3+1&"&"";""&a"&(ROW()-1)*3+2&"&"";""&a"&(ROW()-1)*3+3

It evaluates to:

=A1&";"&a2&";"&a3

Then I drag down the column as far as I need.

Then with that range selected

Edit|copy followed by edit|paste special values

Then with that range still selected

Data|text to columns

Choose Fixed width, but don't have any delimiters.

Excel will see the results as those nice straight-forward formulas.

===============

Another way is to use something like:

=INDEX(A:A,(ROW()-1)*3+1)&";"

&INDEX(A:A,(ROW()-1)*3+2)&";"

&INDEX(A:A,(ROW()-1)*3+3)

And drag down as far as you need.

It really depends on what's important to you.

Is it just getting the data there or do you like the straight-forward

formulas?