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

"TUNGANA KURMA RAJU" wrote:

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.

"Toppers" wrote:

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?

"TUNGANA KURMA RAJU" wrote:

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.