Posts by jest3r

    hi guys I an using a few vlookups and can not get the formula to work correctly the vlookups work but if no data is found I want it to =i6 here is the formula

    Code
    =UPPER(IF(ISNA(VLOOKUP(O6,'Wipedrive Report'!A:F,4,FALSE)),IF(ISNA(VLOOKUP(O6,'Asset Capture'!A:F,5,FALSE)),"",VLOOKUP(O6,'Asset Capture'!A:F,5,FALSE)),VLOOKUP(O6,'Wipedrive Report'!A:F,4,FALSE)))


    any help would be greatly appreciated.

    Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"


    thank you PCI
    this is working way better then my try but it is pasting extra data that i don't need, is there anyway i can modify this so that after filtering the data it only pastes the date from column A to column L?
    i have made a few modifications i hope you don't mind.

    Sub Treat()
    Dim LR As Long
    Dim InSh As Worksheet, OutSh As Worksheet
    Dim WkRg As Range
    Const KeyW As String = "MONITOR"
    Const FR As Integer = 34
    Const WkCol1 As String = "A"
    Const WkCol2 As String = "C"
    Const WkColOut As String = "L"


    Set InSh = Sheets("Asset Capture")
    Set OutSh = Sheets("GRN Status Report")
    LR = OutSh.UsedRange.Rows.Count
    LR = Range("A2").End(3).Row
    With InSh
    If (.AutoFilterMode) Then .AutoFilterMode = False ' REMOVE AUTOFILTER IF EXIST
    LR = .Cells(Rows.Count, WkCol1).End(3).Row
    Set WkRg = .Range(.Cells(FR, WkCol1), .Cells(LR, WkCol2))
    WkRg.AutoFilter Field:=3, Criteria1:=KeyW
    LR = OutSh.Cells(Rows.Count, WkColOut).End(3).Row
    WkRg.Offset(1, 0).Copy Destination:=OutSh.Cells(LR + 8, WkColOut)
    WkRg.AutoFilter ' REMOVE AUTOFILTER
    End With
    End Sub

    Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"


    can anyone have a quick look over this and steer me in the right direction?
    I am trying to copy the asset numbers from "Asset Capture" column A to "GRN Status Report" column L, if Column C in "Asset Capture" contains the text "MONITOR"

    Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"


    ok I have moved forward and all I am trying to do now is get column A, row 35 and down data on "asset capture" to move to "GRN status reports" column L, row 9 and down if the data on "asset capture" column 3, row 35 and down =MONITOR while leaving "GRN status reports" column L blank so raw data can be imputed when required.

    Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"


    hello PCI and thanks for responding.
    yes, I only want the data from sheet "asset capture", columns A and B starting at row 35 if column C contains the word MONITOR to populate "GRN status report" last row and columns L and J. the problem I have is that "GRN status report" column L is being populated manually when certain work is done on other assets which then grabs data from "wipedrive report". I'm trying to get the monitor data across because we don't get any monitor data from the wipedrive report.

    hi guys
    the issue I am having is that I am combining a couple of reports and want most of my data to come from the sheet titled Wipedrive reports,
    but what I am trying to do (with little success) is if any monitors are recorded on the asset capture sheet it copies the ATS# and Serial# across to the GRN report. This is becoming difficult because of the sums and lookups I have already put in place, any help or tips would be greatly appreciated.