copy increment by 1

[ASMelley]

Hi, My problem is that I can not get excel to copy increment by 1. On
one spread sheet I have data in A1 B1 C1. What I want to do is put
this data into another spreadsheet in A1 A2 A3. Then copy the
information in A1 A2 A3 to A4 A5 A6. Instead of having the A4 A5 &A6
refrence A4 B4 C4 i want it to refrence A2 B2 C2 etc. I have to copy
this aprox. 400 times. Is there anyway I can accomplish this.

 

[robert111]

It is not clear what you want to do, please supply some dummy data wit
a description of how you want to manipulate it

 

[ASMelley]

ok

Spread Sheet 1

A B C
1 1.0 2.0 3.0
2 1.1 2.1 3.1
3 1.2 2.2 3.2
4 1.3 2.3 3.3

Spread Sheet 2

A B C
1 1.0
2 2.0
3 3.0
4 1.1
5 2.1
6 3.1

Spread sheet 1 is the data that I am pulling from and spread sheet 2 is
what I want it to look like. The problem is that when I copy the
A1,2,3 of spread sheet 2, to A456 it wants to reference A4 B4 C4 from
spread sheet 1. What I want it to do is reference A2 B2 C2. Hope this
explains it a little better.

 

[Guest]

in the other sheet, in a1 put in this formula

=ADDRESS(TRUNC((ROW()-1)/3)+1,MOD(ROW()+2,3)+1,1,TRUE,"Sheet1")
Change "Sheet1" to be the sheet name where the data is located.
Then drag fill it down the column. If this list the locations where you
want to get the information, then change the formula in A1 to


=Indirect(ADDRESS(TRUNC((ROW()-1)/3)+1,MOD(ROW()+2,3)+1,1,TRUE,"Sheet1"))

and drag fill it down.

 

[Guest]

I provided a solution that does this.

 

[flummi]

Hi,

this may seem a bit crazy but it may help:

Make your reference to sheet 1 like this:

Insted of Sheet1!A1, B1, C1
make it Sheet1!$A1, $B1, $C1

This stops Excel changing the cell references when you copy to the
right.

Enter your first 3 formulae (A1:A3).

Then highlight those 3 cells and copy them to B2. This will change the
references to the rows but not the references to columns. Then
highlicht those cells in B2:B4 and MOVE them to A4. This will NOT
change any cell references.

Highlight all you've got in column A so far and copy to column B into
the row number that you want the A1 reference to become. Move what
you've then got in column B below the last entry in column A.

Repeat this until you're done.

Hope this helps a little bit.

Hans

 

[Guest]

=INDIRECT("'Sheet 1'!R"&1+INT((ROW()-1)/3)&"C"&1+MOD(ROW()-1,3),0)

enter this formula in A1 and copy down. I haven't found a way yet to
increment references the way you want, without using a formula like this.