Insert data into a second sheet based on a condition

vij

Board Regular
Joined
Feb 13, 2011
Messages
215
Hi,

I have two sheets in which I enter data. In both the sheets based on certain condition I want the data to be inserted and sorted date and time wise.
  1. The sheets have reference ids (Auto populated).
  2. "Control" sheet in Column R has the condition "Transfer to Ops Team".
  3. If this condition is met then the whole row is copied to the "Phy Sec" sheet at the appropriate time and place.
  4. The Phy Sec team would also be entering data in this sheet manually.
  5. I am looking for the enteries to be sorted as per the date and time.
  6. Similarly if "Transferred to Control" is enetred in the "Phy Sec" sheet then the row is copied to the Control sheet and sorted as per the date and time.

  7. Combined Task Tracker Macro Enabled Trial.xlsm
    CDEFGHIJKLMNOPQR
    3Reference IdSiteRequestModeDateTimeOperatorRequestAssistanceRequestorEmployeeTypeLDAPBUDepartmentCompanyContactScope
    4N132-0000001N132Mail7-Sep-2010:33 AMUditBadgeChange/modify BadgeRadhakrishnan Nair A VAdobe Paid Tempnair@adobe.comGSSGSSAdobeBadge was activated
    5NOS-0000001NOSMail7-Sep-2012:00 PMVanjeetOnsiteAssistanceexit formaltiesUtsav MazumdarRegularumazumda@adobe.comEmployee ExperienceGlobal Employee Resource CenterAdobeTransferred to GSO Team
    6N132-0000002N132Walky7-Sep-2012:12 PMVanjeetMiscellaneousMaintenance QueryAnmol SharmaVendorcus60415@adobe.comEmployee ExperienceGSOC&WTransferred to Sec Gate 2
    7PTP-0000001PTPBoardLineCall7-Sep-2012:46 PMUditOffsiteAssistanceMaterial EnquiryRobin ChibbarAdobe Paid Tempchibber@adobe.comIT & Cloud OperationsEmployeeAdobe8884085854Return of CISCO Material
    8N132-0000003N132Mail7-Sep-201:02 PMVanjeetOnsiteAssistanceIT Related QueryVipin SachdevaRegularvipin@adobe.comIT & Cloud OperationsHosting, Cloud and Engineering ServicesAdobeRequest was completed by ISOC
    9N132-0000004N132Mail7-Sep-202:09 AMVanjeetOpenOpening access area in absence of the ownerVishesh GuptaRegularvishegup@adobe.comCustomer ExperienceCustomer ExperienceAdobeRequest was completed by ISOC
    10N132-0000005N132Mail7-Sep-202:12 AMAjayGSSQueryDSR Related QueryVinay ShahiVendorwal57161@adobe.comEmployee ExperienceGSSSecuritas35782Request was completed by ISOC
    11N132-0000006N132Mail7-Sep-204:02 PMAmitOnsiteAssistanceexit formaltiesAstha KabraRegularastkabra@adobe.comCreative ProductAdobe DesignAdobeTransferred to ERC Team
    12N132-0000007N132Mail8-Sep-2012:18 AMAjayOnsiteAssistanceIT Related QuerySucharita GhoshRegularsucghosh@adobe.comWWFOAdobe Customer SolutionsAdobeTransferred to Ops Team
    13N132-0000008N132ExtensionCall8-Sep-201:23 AMAjayGSSQueryVendor relatedVinay ShahiVendorwal57161@adobe.comEmployee ExperienceGSSSecuritas35782Query was resolved
    14N132-0000009N132ExtensionCall8-Sep-201.29 AMAjayGSSQueryDSR Related QueryVinay ShahiVendorwal57161@adobe.comEmployee ExperienceGSSSecuritas35782Query was resolved
    15N132-0000010N132Mail8-Sep-208:58 AMSwatiOpenTech CaféSreejeshVendorcus60285@adobe.comEmployee ExperienceGSOC&WTransferred to Ops Team
    16N132-0000011N132Mail8-Sep-2010:10 AMVanjeetOnsiteAssistanceIT Related QueryMrinalini SardarRegularsardar@adobe.comCreative ProductAdobe DesignAdobeTransferred to Ops Team
    17N132-0000012N132Mail8-Sep-2010:57 AMSwatiOpenShipment RoomSreejeshVendorcus60285@adobe.comEmployee ExperienceGSOC&WTransferred to Ops Team
    18N132-0000013N132Walky8-Sep-2011:29 AMVanjeetGSSQueryIT Related QueryRaju PandeyVendorwal85634@adobe.comEmployee ExperienceGSSSecuritasWalkyQuery was resolved
    19N132-0000014N132Walky8-Sep-2012:02 PMVanjeetGSSQueryMaterial EnquiryRaju PandeyVendorwal85634@adobe.comEmployee ExperienceGSSSecuritasWalkyQuery was resolved
    20N132-0000015N132Mail8-Sep-2012:35 PMVanjeetOnsiteAssistanceIT Related QueryAshish MishraRegularashishm@adobe.comCreative ProductCore Technologies & ProductsAdobeTransferred to Ops Team
    Control
    Cell Formulas
    RangeFormula
    C4:C20C4=D4&TEXT(COUNTIF($D$4:D4,D4),"-0000000")
    Cells with Conditional Formatting
    CellConditionCell FormatStop If True
    R18Expression=AND($P25<>"",($P25=VB))textNO
    R18Expression=AND($O25<>"",($O25=VB))textNO
    L19Expression=AND($T24<>"",($T24=VB))textNO
    L18Expression=AND($T23<>"",($T23=VB))textNO
    P10Expression=AND($T17<>"",($T17=VB))textNO
    L10Expression=AND($T15<>"",($T15=VB))textNO
    I10Expression=AND($T17<>"",($T17=VB))textNO
    M10Expression=AND($S17<>"",($S17=VB))textNO
    L14Expression=AND($T19<>"",($T19=VB))textNO
    L13Expression=AND($T18<>"",($T18=VB))textNO
    L7Expression=AND($Q12<>"",($Q12=VB))textNO
    O7Expression=AND($T12<>"",($T12=VB))textNO
    O6Expression=AND($T11<>"",($T11=VB))textNO
    L6Expression=AND($T11<>"",($T11=VB))textNO
    R13:R14,R19Expression=AND($P20<>"",($P20=VB))textNO
    Q13:S14,R19,R77Expression=AND($O20<>"",($O20=VB))textNO
    R20Expression=AND($P24<>"",($P24=VB))textNO
    R20Expression=AND($O24<>"",($O24=VB))textNO
    P20Expression=AND($T24<>"",($T24=VB))textNO
    R21,R16:R17,R22:S22,Q11:S12,Q15:S15Expression=AND($O16<>"",($O16=VB))textNO
    N15:P15,I16,I15:L15,I17:L17,P16:P17,N17:O17,I21,P21,K21:L21,K16:M16,I22:K22,O22:P22,C40,C30:C34,G71:H71,E71,C71,C76:C78,E76:E78,G76:H78,C82,G82:H82,G116:G117,E116:E117,C116:C117,P109,I4:K4,P7,M11,I11:K11,P11:P12,I12,K12:N12Expression=AND($T9<>"",($T9=VB))textNO
    R7,R11:R12,R15:R17,R21Expression=AND($P12<>"",($P12=VB))textNO
    L4Expression=AND($Q9<>"",($Q9=VB))textNO
    Q31,S29:S34,Q20:Q21Expression=AND($O38<>"",($O38=VB))textNO
    M8,G26,G27:H29,G35:H35,I39:J40,G41:H42,G31:H32,M72,I72:K72,I76:I77,O83,M83,J114,I115:I116,K115,I123:J124Expression=AND($S12<>"",($S12=VB))textNO
    G5:G10,E5:E14Expression=AND(#REF!<>"",(#REF!=VB))textNO
    R4Expression=AND($Q9<>"",($Q9=VB))textNO
    G17Expression=AND($T30<>"",($T30=VB))textNO
    E4:E20Expression=#REF!="VB"textNO
    E4:G4,E5:F16Expression=AND(#REF!<>"",(#REF!=VB))textNO
    O4:P4Expression=AND($T9<>"",($T9=VB))textNO
    R4Expression=AND($O9<>"",($O9=VB))textNO
    E17:F19Expression=AND($Q4<>"",($Q4=VB))textNO
    E15:E16,E39:E42,F82:F86Expression=AND($Q4<>"",($Q4=VB))textNO
    R5:R6Expression=AND($P11<>"",($P11=VB))textNO
    I7Expression=AND($T12<>"",($T12=VB))textNO
    Q7:R7Expression=AND($O12<>"",($O12=VB))textNO
    I8Expression=AND($T12<>"",($T12=VB))textNO
    Q8:S8Expression=AND($O12<>"",($O12=VB))textNO
    E17:E19,E26,E38:E41,F76:F78Expression=AND($Q8<>"",($Q8=VB))textNO
    P5:P6,I5:J5,M9,P9,I9:K9,I18,O18:P18,I6,K18,C36:C38,C41,C74:C75,E74:E75,G74:H75,E83:E86,G83:H86,C83:C86Expression=AND($T11<>"",($T11=VB))textNO
    Q9:S10,Q5:S6,R76Expression=AND($O11<>"",($O11=VB))textNO
    M9,G11,G15:G16,G20:H21,G36:H38,K40,M40,G40:H40,I75,I73:K73,J117,M65Expression=AND($S15<>"",($S15=VB))textNO
    M11:M12,M16,G25,G24:H24,M37:M38,I37:K38,G39:H39,I41:J41,M74:M75,O74:O75,I74:J75,O84,M84,I85,I113:I114,I117,J115Expression=AND($S16<>"",($S16=VB))textNO
    Q16:Q19,S16:S19Expression=AND($O36<>"",($O36=VB))textNO
    E20:F20,F112:F122Expression=AND($Q5<>"",($Q5=VB))textNO
    E20Expression=AND($Q9<>"",($Q9=VB))textNO
    G12:G14,G18:G19Expression=AND($S20<>"",($S20=VB))textNO
    P13:P14,O19:P19,I19,I13:I14,K13:K14,M13:M14,K19,P54,E72:E73,E75,G75:H75,C72:C73,C75,G72:H73,P110,K10,M10Expression=AND($T17<>"",($T17=VB))textNO
    G17,M13:M14,M67Expression=AND($S20<>"",($S20=VB))textNO
    G18:G19Expression=AND($T31<>"",($T31=VB))textNO
    C134:H1048576,1:3,M36,T46:XFD46,M43,T42:XFD42,D32:D34,T59:XFD61,D73,D76:D78,D84:D87,T1048574:XFD1048576,D123:D124,M106:M108,D106:D119,D43:D56Expression=AND($R4<>"",($R4=VB))textNO
    C5:D15Expression=AND($N1048573<>"",($N1048573=VB))textNO
    E45,G45:H45,P42,C43:C45,C20:C29,C35,G52:G55,C52:C55,E52:E55,C70,G70:H70,G87:H88,C87:C88,E87:E88,E113:E115,C113:C115,G113:H115,K121:L121,I20,K20:L20,P24,I24:J24,K25,M25,I25,M8,P8,K8Expression=AND($T12<>"",($T12=VB))textNO
    C4:D14,D15:D122Expression=$R4="VB"textNO
    C20:C25Expression=$R11="VB"textNO
    M47,M49:M50,D15:D31,D35:D37,M56:M57,M62,M64,M69,D72,D74,D85:D88,D125:D132,M103:M105,D101:D118,D42:D59,C4:D14Expression=AND($R6<>"",($R6=VB))textNO
    C17:D19,D20:D34,D41:D56Expression=AND($R11<>"",($R11=VB))textNO
    C15:D19,D20:D34Expression=$R5="VB"textNO
    C16:D16,D17:D34,D43:D56Expression=AND($R8<>"",($R8=VB))textNO
    D16:D34Expression=AND($P8<>"",($P8=VB))textNO
    Cells with Data Validation
    CellAllowCriteria
    J4:J5List=INDIRECT(I4)
    L6:L7List=Employee
    L12:L20List=Employee
    P4:P20List=Company
    O15,O17:O19,O6:O7,O4,O22,O26:O27,N44:O44,N38:O38,N46:O47,N33:O33,N52:O53,N60:O60,O63,N64:O64,N71:O71,N75:O75,N80:O80,N92:O92,N94:O94,O114,O121:O122,N116:O116,N124:O124,N129:O129List=Department
    O17:O19List=Department
    O6:O7List=Department
    D4:D132List=Site
    E4:E131List=RequestMode
Combined Task Tracker Macro Enabled Trial.xlsm
ABCDEFGHIJKLMNOPQRST
1Ops IdOps SiteReference IdSiteRequestModeDateTimeOperatorRequestAssistanceRequestorEmployeeTypeLDAPBUDepartmentCompanyContactScopeRemarksRemarks
2N25A-00001N25AN132-0000007N132Mail440820.0125AjayOnsiteAssistanceIT Related QuerySucharita GhoshRegularsucghosh@adobe.comWWFOAdobe Customer SolutionsAdobe0Transferred to Ops TeamPersonal belongings collection
3N132-00001N132N132-0000010N132Mail440820.373611SwatiOpenTech CaféSreejeshVendorcus60285@adobe.comEmployee ExperienceGSOC&W0Transferred to Ops TeamTechcafe open
4-00000N132-0000011N132Mail440820.423611VanjeetOnsiteAssistanceIT Related QueryMrinalini SardarRegularsardar@adobe.comCreative ProductAdobe DesignAdobe0Transferred to Ops TeamPersonal belongings collection
5-00000N132-0000012N132Mail440820.45625SwatiOpenShipment RoomSreejeshVendorcus60285@adobe.comEmployee ExperienceGSOC&W0Transferred to Ops TeamShipment open
6-00000N132-0000015N132Mail440820.524306VanjeetOnsiteAssistanceIT Related QueryAshish MishraRegularashishm@adobe.comCreative ProductCore Technologies & ProductsAdobe0Transferred to Ops TeamPersonal belongings collection
Phy Security
Cell Formulas
RangeFormula
C2:S6C2=FILTER(Control!C:S,Control!R:R="Transferred to Ops team")
A2:A6A2=B2&TEXT(COUNTIF($B$2:B2,B2),"-00000")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:S1Expression=AND($P4<>"",($P4=VB))textNO
F2:F1048576Cell Valuebetween 778000 and 779000textNO


Thanks,
Vij

 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top