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.

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=524764

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.

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.

--
Regards,
Tom Ogilvy



" wrote:

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.

I provided a solution that does this.

--
Regards,
Tom Ogilvy


" wrote:

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.

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

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

" wrote:

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.

The above formulas will work fine, but only really for the very specific example given.

If you're confident with pasting code into a VBA module, I suggest these two little macros (put them together in the same module) - I've replaced logical operators with words to stop them disappearing on the forum, so where you see EQUALS, type the equals sign:

'###CODE BEGINS HERE
Public xSel As Range

Sub CopyCustom()
Set xSel EQUALS Selection.Cells
End Sub

Sub PasteCustom()
nCell EQUALS 0

For Each xCell In Selection.Cells
nCell EQUALS nCell + 1
xCell.Value EQUALS xSel.Cells(nCell).Value
Next xCell
End Sub
'###CODE ENDS HERE

I suggest assigning these to two keys - perhaps CTRL+D and CTRL+F respectively. What you would then do is:

1. Select the source data array (any shape)
2. Run 'CopyCustom'
3. Select the destination data array (any shape)
4. Run 'PasteCustom'.

So, to implement your example above, you would select A1:C4 in the source sheet, run CopyCustom, then select A1:A12 (or more cells down if you can't be bothered to make sure you select exactly 12) in your OTHER worksheet and run PasteCustom.

This will transform the data from the original range shape to the selected range shape. Try it with different range shapes and see what happens. You may ultimately find this a more flexible solution to an algebraic function.

Regards,
MB