retrive data from oher sheet on a condition

[Guest]

How to retrive data from other sheet to current sheet basing on a condition.
case1:
sheet2: Col D----Col E-----Col F
row15----Capital-- interest--gain (these are lables)
row16---- 250000- 7500---- 6200
In sheet 1 cell A1 to B3 I want these values be transposed if there is a
value in each Col D or Col E or Col F.
The results will be like this:
sheet1:
Col A ---- Col B
1.Capital --- 250000
2.interest --- 7500
3.gain ------- 6200
Suppose sheet 2 Col E ( row 16)value is zero or blank,then the results will
be like:
Col A ---- Col B
1.Capital --250000
2.gain -- 6200

case2:vicevrsa of case 1
sheet2:
------------ Col D----Col E
row16 --- Capital-- 0
row17 ----interest-- 7500
row18 ---- gain ---- 6200
Aagin same output,I want these values be retrieved in sheet 1 cell A1 to B3
,if a value exists in Col E.
The results will be like this:
Sheet 1
Col A ---- Col B
1.interest-- 7500
2.gain ---- 6200
Thanks in advance for your help.

 

[Guest]

If I understand correctly the headings 'Capital', 'Gain' and 'Interest' can
be both columns D to F Row 15 (only?) and/or column D rows 16-18 on Sheet2?

Are there only ever maximum of 3 values?

Why two formats of data?

 

[Guest]

Yes,the headings are row(15) only.It is in the form of a table (D15,E15,F15
are lables and D16,E16 and F16 are their values.There are only maximum 3
values.
In case 2 the lables and values are in 2 columns format.I want function for
both formats.

 

[Guest]

A macro solution (not smart enough to offer a formula solution!):

Sub Transform()

Dim ws1 As Worksheet
Dim irow As Long, row As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")

With ws1
.Cells(1, 1).Resize(3, 2).ClearContents
If IsNumeric(.Cells(16, 1)) Then
irow = 1
For col = 1 To 3
If .Cells(16, col) > 0 Then
.Cells(irow, 1) = .Cells(15, col)
.Cells(irow, 2) = .Cells(16, col)
irow = irow + 1
End If
Next col
Else
irow = 1
For row = 16 To 18
If .Cells(row, 2) > 0 Then
.Cells(irow, 1) = .Cells(row, 1)
.Cells(irow, 2) = .Cells(row, 2)
irow = irow + 1
End If
Next row
End If


End With


End Sub

 

[Guest]

sorry ....try (as I tested on one sheet not two as you require):

Sub Transform()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim irow As Long, row As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Cells(1, 1).Resize(3, 2).ClearContents
With ws2

If IsNumeric(.Cells(16, 1)) Then
irow = 1
For col = 1 To 3
If .Cells(16, col) > 0 Then
ws1.Cells(irow, 1) = .Cells(15, col)
ws1.Cells(irow, 2) = .Cells(16, col)
irow = irow + 1
End If
Next col
Else
irow = 1
For row = 16 To 18
If .Cells(row, 2) > 0 Then
ws1.Cells(irow, 1) = .Cells(row, 1)
ws1.Cells(irow, 2) = .Cells(row, 2)
irow = irow + 1
End If
Next row
End If


End With


End Sub

 

[Guest]

Thank you Toppers,Its working.Can you figure a function to skip blank or zer0
value rows to get this output.

 

[Guest]

Sub Transform()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim irow As Long, row As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Cells(1, 1).Resize(3, 2).ClearContents
With ws2

If IsNumeric(.Cells(16, 1)) Then
irow = 1
For col = 1 To 3
If .Cells(16, col) >= 0 & IsNumeric(.Cells(16, col)) Then
ws1.Cells(irow, 1) = .Cells(15, col)
ws1.Cells(irow, 2) = .Cells(16, col)
irow = irow + 1
End If
Next col
Else
irow = 1
For row = 16 To 18
If .Cells(row, 2) >= 0 & IsNumeric(.Cells(row, 2)) Then
ws1.Cells(irow, 1) = .Cells(row, 1)
ws1.Cells(irow, 2) = .Cells(row, 2)
irow = irow + 1
End If
Next row
End If


End With


End Sub

 

[Guest]

Error! :


IF.Cells(16, col) >= 0 should be IF.Cells(16, col) > 0
IF .Cells(row, 2) >= 0 should be IIF.Cells(row, 2) > 0

As per original