Not so simpel copying of a simple formula

  • Thread starter Thread starter jkrons
  • Start date Start date
J

jkrons

I have this formula i B1

=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?
 
I sloved it by having two formulars. One in B1:
=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
 
Hi,

Try using $ to make either the column absolute - $A1, the row absolute -
A$1, or column add row absolute - $A$1
 
I can make the column absolute, but that doesn't solve my problem, as I need
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
 
I use this technique.

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?
 
Thank you very much.

Jan

Dave said:
I use this technique.

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?
Click to expand...
 
Back
Top