How to import data from Sheet1 to Sheet2 in the order I want

[zoeteh]

Data in Sheet1:
A1=5.500 B1=0.275
A2=5.625 B2=0.125
A3=5.750 B3=0.000
A4=5.875 B4= -0.125

Data in Sheet2:
A1=Sheet1!A4 B1=Sheet1!B4
A2=Sheet1!A3 B2=Sheet1!B3
A3=Sheet1!A2 B3=Sheet1!B2
..
..
..
..
Instead of linking the cell manually, what formula or shortcut should I
use to import the data in the sequece I want?

 

[stevebriz]

This can be done easliy with a macro...are comfortable using a macro
for this?
A couple questions if macros are ok with you:
Does number of rows change regularly ? How many columns do you have?
Do you want formulas on sheet 2 or you just want the values?

 

[Excelor]

There will be hundreds of rows and column and the data changed
regularly (daily) in sheet1. I would like to be able to validate the
data on sheet2 is correct. So, maybe having the formulas shown is a
better solution?

 

[stevebriz]

try this:

Private Sub CommandButton1_Click()

' ----This sub will insert formulas in sheet2 referencing sheet1 _
but in reverse order in each column.
' Eg:
'Data in Sheet1:
'A1=5.500 B1=0.275
'A2=5.625 B2=0.125
'A3=5.750 B3=0.000
'A4=5.875 B4= -0.125

'Data in Sheet2:
'A1=Sheet1!A4 B1=Sheet1!B4
'A2=Sheet1!A3 B2=Sheet1!B3
'A3=Sheet1!A2 B3=Sheet1!B2



Dim Rw As Integer
Dim i As Integer
Dim j As Integer
Dim flipform As String
Dim LastColumn As Integer
On Error GoTo EndMacro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Sheet2.Cells.ClearContents ' clear sheet2

'-----------Determine last used column------------------------
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Sheet1.Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column


End If
'-----------reference sheet1 cells into sheet2 ------------------------
Sheet1.Select
For j = 1 To LastColumn
Range(Cells(1, j).Address, Range(Cells(65536,
j).Address).End(xlUp)).Select
Rw = Selection.Rows.Count 'Determine last used Row in column
For i = 1 To Rw
flipform = "=sheet1!" & Sheet1.Cells(i, j).Address

If Sheet1.Cells(i, j).Value <> "" Then 'If
sheet1 cell is empty do not copy
Sheet2.Cells((Rw - i + 1), j).Formula =
flipform
Else
Sheet2.Cells((Rw - i + 1), j).Value = ""
End If
Next i
Next j

EndMacro:
Sheet1.Cells(1, 1).Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub