Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
I have the following file:
Sheet1 February to Style # date Revenue ($) ABB $$ ACD $$ DED $$ DEE $$ CAA $$ FEE $$ FEH $$ Sheet2 March to date Revenue ($) ABB $$ DED $$ DEE $$ FEE $$ FEH $$ Each month new styles are added in sheet1. How can I get a formula to look up a style in sheet1 and if it is new to sheet 2 add it. But only add it if it has revenue associated with it. Thanks, Gingit |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
I don't think you're going to find a formula that does this kind of thing.
Maybe you could use a macro that does the work????? If you want to try: Option Explicit Sub testme() Dim InWks As Worksheet Dim OutWks As Worksheet Dim myInRng As Range Dim myCell As Range Dim res As Variant Dim DestCell As Range Set InWks = Worksheets("sheet1") Set OutWks = Worksheets("sheet2") With InWks Set myInRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myInRng.Cells If IsEmpty(myCell.Offset(0, 1).Value) Then 'skip it Else If IsNumeric(myCell.Offset(0, 1).Value) Then 'keep looking res = Application.Match(myCell.Value, OutWks.Range("a:a"), 0) If IsError(res) Then 'not there, so addit With OutWks Set DestCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With DestCell.Value = myCell.Value DestCell.Offset(0, 1).Value = myCell.Offset(0, 1).Value End If End If End If Next myCell End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gingit wrote: I have the following file: Sheet1 February to Style # date Revenue ($) ABB $$ ACD $$ DED $$ DEE $$ CAA $$ FEE $$ FEH $$ Sheet2 March to date Revenue ($) ABB $$ DED $$ DEE $$ FEE $$ FEH $$ Each month new styles are added in sheet1. How can I get a formula to look up a style in sheet1 and if it is new to sheet 2 add it. But only add it if it has revenue associated with it. Thanks, Gingit -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Thanks Dave I'll give it a try.
Gingit "Dave Peterson" wrote: I don't think you're going to find a formula that does this kind of thing. Maybe you could use a macro that does the work????? If you want to try: Option Explicit Sub testme() Dim InWks As Worksheet Dim OutWks As Worksheet Dim myInRng As Range Dim myCell As Range Dim res As Variant Dim DestCell As Range Set InWks = Worksheets("sheet1") Set OutWks = Worksheets("sheet2") With InWks Set myInRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myInRng.Cells If IsEmpty(myCell.Offset(0, 1).Value) Then 'skip it Else If IsNumeric(myCell.Offset(0, 1).Value) Then 'keep looking res = Application.Match(myCell.Value, OutWks.Range("a:a"), 0) If IsError(res) Then 'not there, so addit With OutWks Set DestCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With DestCell.Value = myCell.Value DestCell.Offset(0, 1).Value = myCell.Offset(0, 1).Value End If End If End If Next myCell End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gingit wrote: I have the following file: Sheet1 February to Style # date Revenue ($) ABB $$ ACD $$ DED $$ DEE $$ CAA $$ FEE $$ FEH $$ Sheet2 March to date Revenue ($) ABB $$ DED $$ DEE $$ FEE $$ FEH $$ Each month new styles are added in sheet1. How can I get a formula to look up a style in sheet1 and if it is new to sheet 2 add it. But only add it if it has revenue associated with it. Thanks, Gingit -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |