| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
retrive data from oher sheet on a condition
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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 "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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
Thank you Toppers,Its working.Can you figure a function to skip blank or zer0
value rows to get this output. "Toppers" wrote: 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 "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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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 "TUNGANA KURMA RAJU" wrote: Thank you Toppers,Its working.Can you figure a function to skip blank or zer0 value rows to get this output. "Toppers" wrote: 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 "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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
retrive data from oher sheet on a condition
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 "Toppers" wrote: 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 "TUNGANA KURMA RAJU" wrote: Thank you Toppers,Its working.Can you figure a function to skip blank or zer0 value rows to get this output. "Toppers" wrote: 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 "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy data to another sheet on condition | Excel Worksheet Functions | |||
| Copy certain cells from one sheet to another with a condition | Excel Worksheet Functions | |||
| Look for and Retrive Data | Excel Worksheet Functions | |||
| Help to add new sheet in a workbook with a condition | Excel Worksheet Functions | |||
| How do I retrive my time sheet from AmeriCorps Work In Progress? | Setting up and Configuration of Excel | |||
Linear Mode
